Discussion :

[Galdon]

Bonjour, quand un visiteur clique sur un lien externe dans les pages de mon site, ça appelle une page PHP via AJAX qui enregistre diverses informations dans la table track_liens :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
-- 
-- Structure de la table `track_liens`
-- 
 
CREATE TABLE `track_liens` (
  `id_lien` int(11) NOT NULL auto_increment,
  `urlFrom` varchar(255) NOT NULL default '',
  `urlTo` varchar(255) NOT NULL default '',
  `date` int(11) default '0',
  PRIMARY KEY  (`id_lien`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

Voici un exemple d'enregistrement :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
1	|	http://www.finalclap.com/page1.html	|	http://www.google.fr		|	1214687186
2	|	http://www.finalclap.com/page2.html	|	http://www.yahoo.com		|	1220657186
3	|	http://www.finalclap.com/page3.html	|

Cahier des charges

Ce qui m'intéresse c'est de pouvoir générer à partir de cette table des tableaux par date ou par domaine/période :

Rapport liens sortants pour le 10/10/2008

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Domaine			| Nb de clics
www.google.fr		| 10
forum.machun.net	| 25

Rapport de pour la période 10/09/2008 - 12/09/2008

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
Jour		| Nb de clics
10/09/2008	| 10
11/09/2008	| 8
12/09/2008	| 5

Donc j'ai deux questions :

1. Comment faire un GROUP BY date alors que le champ date contient un entier (timestamp unix) ?

2. Comment faire un GROUP BY domaine alors que je n'ai pas de champs domaine (en gros : comment isoler le domaine sortant de chaque entrée depuis le champ urlTo, avec des %, LIKE et autres joyeuseries du genre) ?

Merci

[Chtulus]

Bonsoir,

1/ Je comprends pas trop le soucis ? Est-ce que cela peut vous aider ? :
http://pcaboche.developpez.com/artic...-heure/?page=3

2/ Il me semble que SUBSTRING fonctionne en MySQL !


Auriez vous une ébauche de votre requête ?

[SQLpro]

1. Comment faire un GROUP BY date alors que le champ date contient un entier (timestamp unix) ?

Pourquoi diable avez vous mis un INT au lieu d'une date dans votre table ?
Faites un modèle de données correct. C'est le seul moyen de faire des requêtes simples, propres et efficaces !

2. Comment faire un GROUP BY domaine alors que je n'ai pas de champs domaine (en gros : comment isoler le domaine sortant de chaque entrée depuis le champ urlTo, avec des %, LIKE et autres joyeuseries du genre) ?

Vous pouvez isoler cette partie de l'URL en faisant des SUBSTRING combinés avec des recherches de motifs.
Le mieux serait d'ajouter à votre table une colonne dns et de l'alimenter à l'aide d'un trigger.

A +

[Galdon]

Merci pour vos réponses, j'ai résolu le problème du GROUP BY date avec la fonction FROM_UNIXTIME :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
SELECT * , DATE( FROM_UNIXTIME( `date` ) ) AS Date_seule
FROM `track_liens`
GROUP BY Date_seule

J'ai aussi traité les rapports par domaine sur une période donnée :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SELECT *,DATE(FROM_UNIXTIME(`date`)) AS Date_seule
FROM `track_liens`
WHERE `urlTo` LIKE '%google.fr%'
AND DATE(FROM_UNIXTIME(`date`)) BETWEEN '2008-06-01' AND '2008-09-01'

Mais il me reste le GROUP BY domaine où il faut utiliser SUBSTR, celui-là je ne vois pas trop comment faire...


Sinon, pour répondre à vos remarques :

• Type du champ date : J'utilise un INT parce que je préfère travailler avec des timestamp sous PHP. Le type de champ TIMESTAMP est bizarre, ça ressemble plus à un DATETIME qu'à un entier !
  Mais avec le recul j'aurais pu utiliser un DATETIME + la fonction UNIX_TIMESTAMP, ça reviendrait au même dans le traitement PHP.
  
  
• Champ domaine : Les accès à ma table seront bien plus fréquent en écriture qu'en lecture, puisque la lecture ne se fera que depuis la partie administration de mon site.
  La table va vite grossir au fil des clics des visiteurs, donc je préfère stocker le moins de données possible même si ça augmente le temps des requêtes SELECT.

[Galdon]

J'ai réussi à isoler le nom de domaine, mais ça ne fonctionne pas pour les URL qui n'ont pas de slash à la fin (ex: http://www.google.fr) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
SELECT `id_lien`, `urlTo`, SUBSTR(urlTo,8,LOCATE('/',urlTo,8)-8)
FROM `track_liens`

Donc je commence le SUBSTR à 8 pour enlever les 7 caractères de http://, et je récupère tous les caractères présents entre http:// et le premier slash à partir du 8ème caractère.

Du coup, quand il n'y à pas de slash après le 8ème caractère, LOCATE renvoie 0 et SUBSTR retourne une chaine vide.

Comment résoudre ce problème ?

[CinePhil]

En gérant les deux cas :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
SELECT `id_lien`, `urlTo`, 
  CASE
    WHEN LOCATE('/', urlTo, 8) = 0 THEN SUBSTR(urlTo, 8, LENGTH(urlTo) - 8)
    ELSE SUBSTR(urlTo,8,LOCATE('/',urlTo,8)-8)
  END AS Domaine
FROM `track_liens`

[Galdon]

Merci ça marche (sauf que c'est LENGTH(urlTo) - 7)).