Requête SQL et JSON

Version imprimable

Requête SQL et JSON

Bonjour à tous, j'essaie de créer un fichier json à partir d'une requête sur ma base mysql. J'arrive à le générer mais les infos ne sont pas présentées comme elles le devraient pour renseigner un graph Morris de Bootstrap.
Je m'explique: je cherche à afficher le nombre de réservations par agent sur une durée pour un graphique, il me faudrait donc un JSON du type :
[{"date_res":"2014-12-01","N°agent_1":"nombre de résa","N°agent_2":"nombre de résa","N°agent_3":"nombre de résa",;.}.

Mais ma requête :

Code:

$myquery = "SELECT date_res,agent_res,COUNT(*) FROM reservations WHERE date_res BETWEEN '2014-12-01' AND '2015-01-10' GROUP BY date_res"

me renvoie un JSON du type:
{"date_res":"2014-12-01","agent_res":"000001","COUNT(*)":"7"}.

Quelqu'un saurait-il m'aider pour modifier ma requête OU pensez-vous que le problème doive se résoudre dans un traitement PHP ?? Je bloque complètement là.
Code:

1 2 3 4 5 6 7 8 $query = mysql_query($myquery); $data = array(); while($r=mysql_fetch_assoc($query)){ $data[]=$r; } $json = fopen('data/resa_date_json.js', 'a+'); fwrite($json, json_encode($data));
Merci d'avance

Nanouna

Une petite modification au niveau de la requête. Il faut donner un nom à la colonne calculée.
Code:

1 2 $myquery = "SELECT date_res,agent_res,COUNT(*) AS nombre_de_resa FROM reservations WHERE date_res BETWEEN '2014-12-01' AND '2015-01-10' GROUP BY date_res"

21/01/2015, 19h37
Nanouna

Requête SQL et JSON
Merci pour ce tuyau, c'est bien plus propre comme ça. Mais je n'arrive toujours pas à avoir un rendu (nombre_de_resa) par numéro d'agent dans mon JSON ! Il faudrait que le n° d'agent devienne une variable à laquelle j'affecte la valeur 'nombre_de_resa'.
Est-ce possible via SQL?
Citation:
Envoyé par esired

Une petite modification au niveau de la requête. Il faut donner un nom à la colonne calculée.

Code:

1 2 $myquery = "SELECT date_res,agent_res,COUNT(*) AS nombre_de_resa FROM reservations WHERE date_res BETWEEN '2014-12-01' AND '2015-01-10' GROUP BY date_res"
21/01/2015, 19h46
Nanouna

Reqête SQL et JSON

Petite correction sur ma requête (ajout de agent_res dans le GROUP BY) sans impact sur mon problème

Code:

SELECT date_res,agent_res,COUNT(*) AS nombre_de_resa FROM reservations WHERE date_res BETWEEN '2014-12-01' AND '2015-01-10' GROUP BY date_res, agent_res
21/01/2015, 20h28
esired

Je pense que cette discussion répond parfaitement à votre problème, mais il faudra jouer avec les procédures stockées. :D
Mais si vous préférez le faire en PHP, jettez un oeil ici.
21/01/2015, 21h04
Nanouna

Merci beaucoup, je vais regarder ça.

Après quelques jours de recherche et d'essais infructueux, je reviens vers vous. Quelqu'un aurait-il une idée svp?
Merci d'avance et bonne soirée à tous.

Construis ton tableau comme tu le dois :

Code:

1
2
3
4
5
6
$myquery = "SELECT date_res,agent_res,COUNT(*) AS nombre_de_resa FROM reservations WHERE date_res BETWEEN  '2014-12-01' AND  '2015-01-10'  GROUP BY date_res, agent_res";
$query = mysql_query($myquery);
$data = array();    
while($r=mysql_fetch_assoc($query)){ 
       $data[$r['date_res']][$r['agent_res']] = $r['nombre_de_resa'];
    }

Requête SQL et JSON

Merci sabotage,
Ta query fonctionne parfaitement et m'a fait réaliser, qu'avec ce système,les jours où un agent n'a pas fait de réservations, il n'apparaitra pas dans le tableau.
Hors, pour chaque date, il me faut le nombre de réservations de tous les agents!!

Du coup je m'oriente vers un trigger qui complèterai une table parallèle à celle 'réservations' dans ma base du type :
[id_date][sumAgent1][sumAgent2][sumAgent3][sumAgent4][sumAgent]

Pour le moment cette table est renseignée, un peu vilainement, par une requête PHP, qui quand elle envoie les données à la table 'réservations', les envoie aussi à cette nouvelle table 'agent_total_reservations'(sauf que celle-ci à un champ ID classique et donc plusieurs ligne avec la même date) avec des IF qui inserent des 0 ou des 1 selon l'agent qui a fait la résa.
Ca marche, mais je devra modifier mon PHP et chaque ligne IF à chaque ajout d'un agent !!!!

D'où l'idée d'un trigger qui remplirai une table avec un grand nombre d'agent dans lequel je selectionnerai ensuite les agents voulus dans ma requête de calcul du nombre de réservations para agent.
Mais ma requête de trigger ne passe pas et m'affiche :
Code:

1 2 3 #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT COUNT(*) AS ExiDate FROM resa_total_agent WHERE id_date=2015-12-01 ' at line 6
Mon trigger est le suivant et je ne comprend pas ce qu'il n'accepte pas dans mon SELECT COUNT(*).
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 DELIMITER $$ CREATE TRIGGER update_resa_total_agent AFTER INSERT ON reservations FOR EACH ROW BEGIN DECLARE ExiDate INT SELECT COUNT(*) AS ExiDate FROM resa_total_agent WHERE id_date=NEW.date_res IF ExiDate=0 THEN INSERT INTO resa_total_agent(id_date) VALUES(NEW.date_res) ELSE UPDATE resa_total_agent set sumA1=sumA1+1 WHERE id_date=NEW.date_res end if; END; $$ DELIMITER

02/02/2015, 10h07
sabotage

Il faut des guillemets sur les dates

Code:

WHERE id_date='2015-12-01'

Requête SQL et JSON

En fait j'ai essayé :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
DELIMITER $$
  CREATE TRIGGER update_resa_total_agent
  AFTER INSERT ON reservations
  DECLARE ExiDate INT
            SELECT COUNT(*) AS ExiDate FROM resa_total_agent
            WHERE id_date='2015-12-01'
            FOR EACH ROW
                BEGIN
 
 
               IF ExiDate=0
               THEN
               INSERT INTO resa_total_agent(id_date) VALUES('2015-01-29')
               ELSE UPDATE resa_total_agent
                        set sumA1=sumA1+1
                         WHERE id_date='2015-01-29'
end if;
END;
$$
DELIMITER

et ça me donne:

MySQL a répondu: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE ExiDate INT
SELECT COUNT(*) AS ExiDate FROM resa_total_agent' at line 3

Même chose si je remplace

Code:

IF ExiDate=0

par

Code:

IF NOT EXISTS(ExiDate)

Code:

SELECT COUNT(*)

par

Code:

SELECT COUNT(id_date)

J'ai pourtant bien déclaré ExiDate avant.

Et l'essai avec les variables au lieu des dates en brut n'est pas plus concluant et me donne le même message.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
DELIMITER $$
  CREATE TRIGGER update_resa_total_agent
    AFTER INSERT ON reservations
   DECLARE ExiDate INT
            SELECT COUNT(id_date) AS ExiDate FROM resa_total_agent
WHERE id_date=NEW.date_res
        FOR EACH ROW
            BEGIN
 
 
              IF NOT EXISTS(ExiDate)
               THEN
               INSERT INTO resa_total_agent(id_date) VALUES(NEW.date_res)
               ELSE UPDATE resa_total_agent
                        set sumA1=sumA1+1
                         WHERE id_date=NEW.date_res
end if;
END;
$$
DELIMITER

Serait-ce lié à ma construction des tables?

resa_total_agent:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 Structure de la table `resa_total_agent`
--
 
CREATE TABLE IF NOT EXISTS `resa_total_agent` (
  `id_date` date NOT NULL,
  `sumA1` int(11) NOT NULL DEFAULT '0',
  `sumA2` int(11) NOT NULL DEFAULT '0',
  `sumA3` int(11) NOT NULL DEFAULT '0',
  `sumA4` int(11) NOT NULL DEFAULT '0',
  `sumA5` int(11) NOT NULL DEFAULT '0',
  `sumA6` int(11) NOT NULL DEFAULT '0',
  `sumA7` int(11) NOT NULL DEFAULT '0',
  `sumA8` int(11) NOT NULL DEFAULT '0',
  `sumA9` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

et reservations:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE IF NOT EXISTS `reservations` (
  `id_res` int(11) NOT NULL AUTO_INCREMENT,
  `site_res` int(15) NOT NULL,
  `ligne_res` enum('TLD','Cluster') NOT NULL,
  `num_res` varchar(7) NOT NULL,
  `agent_res` char(6) NOT NULL,
  `date_res` date NOT NULL,
  `nom_promo_res` enum('Aucune','10 jours Merveilleux','Promo Eté','Promo Hiver') NOT NULL,
  PRIMARY KEY (`id_res`),
  KEY `site_res` (`site_res`,`ligne_res`,`date_res`,`nom_promo_res`),
  KEY `agent_res` (`agent_res`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=132 ;

Requête SQL et JSON

Voici j'ai réussi à faire mon premier trigger

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
DELIMITER $$
 CREATE TRIGGER `insert_resa_rec` AFTER INSERT ON `reservations`
 FOR EACH ROW BEGIN
 
IF (SELECT COUNT(*) FROM `resa_rec` WHERE  id_date=NEW.date_res) = 0 THEN
INSERT INTO `resa_rec` (id_date)
VALUES(NEW.`date_res`);
 
 END IF;
IF (New.agent_res='000001') THEN
 UPDATE `resa_rec` SET `A1` =`A1`+1 WHERE id_date=NEW.`date_res`;
ELSE IF (New.agent_res='000002') THEN
 UPDATE `resa_rec` SET `A2` =`A2`+1 WHERE id_date=NEW.`date_res`;
ELSE IF (New.agent_res='000003') THEN
 UPDATE `resa_rec` SET `A3` =`A3`+1 WHERE id_date=NEW.`date_res`;
ELSE IF (New.agent_res='000004') THEN
 UPDATE `resa_rec` SET `A4` =`A4`+1 WHERE id_date=NEW.`date_res`;
ELSE IF (New.agent_res='000005') THEN
 UPDATE `resa_rec` SET `A5` =`A5`+1 WHERE id_date=NEW.`date_res`;
 
 END IF;
 END IF;
 END IF;
 END IF;
 END IF;
 
IF (New.site_res='1') THEN
 UPDATE `resa_rec` SET `S1` =`S1`+1 WHERE id_date=NEW.`date_res`;
ELSE IF (New.site_res='2') THEN
 UPDATE `resa_rec` SET `S2` =`S2`+1 WHERE id_date=NEW.`date_res`;
ELSE IF (New.site_res='3') THEN
 UPDATE `resa_rec` SET `S3` =`S3`+1 WHERE id_date=NEW.`date_res`;
ELSE IF (New.site_res='4') THEN
 UPDATE `resa_rec` SET `S4` =`S4`+1 WHERE id_date=NEW.`date_res`;
ELSE IF (New.site_res='5') THEN
 UPDATE `resa_rec` SET `S5` =`S5`+1 WHERE id_date=NEW.`date_res`;
 
 END IF;
 END IF;
 END IF;
 END IF;
 END IF;
 
IF (New.nom_promo_res='Aucune') THEN
 UPDATE `resa_rec` SET `P_Aucune` =`P_Aucune`+1 WHERE id_date=NEW.`date_res`;
ELSE IF (New.nom_promo_res='10 jours Merveilleux') THEN
 UPDATE `resa_rec` SET `P_Merv` =`P_Merv`+1 WHERE id_date=NEW.`date_res`;
ELSE IF (New.nom_promo_res='10 jours Merveilleux') THEN
 UPDATE `resa_rec` SET `P_Ete` =`P_Ete`+1 WHERE id_date=NEW.`date_res`;
ELSE IF (New.nom_promo_res='10 jours Merveilleux') THEN
 UPDATE `resa_rec` SET `P_Hiver` =`P_Hiver`+1 WHERE id_date=NEW.`date_res`;
ELSE IF (New.nom_promo_res='10 jours Merveilleux') THEN
 UPDATE `resa_rec` SET `P5` =`P5`+1 WHERE id_date=NEW.`date_res`;
 
END IF;
 END IF;
 END IF;
 END IF;
 END IF;
 
    END$$
DELIMITER ;

Ca fonctionne parfaitement :)

Questions aux pros : est-ce que ça se fait de créer une table et de la renseigner grace à un trigger avec des données qui sont déjà dans une table existante, comme je le fais?