mysql - PHP PDO how to run a multiple query request?

Question

Hello I'm trying to execute this SQL request in PHP with PDOStatement:

CREATE TEMPORARY TABLE r1
SELECT CONCAT(MONTH(Heure_deb),'/',DAY(Heure_deb)) as 'Date',
                      Heure_deb,
                      Operateur.Id_op ,
                      Nom_op ,
                      Prenom_op,
                      Nom_act ,
                      TIME(Heure_deb) as heure,
                      Commentaire,
                      Type
                      FROM Operateur, Pointage, Activite
                      WHERE Operateur.Id_op = Pointage.Id_op
                      AND Activite.Id_act = Pointage.Id_act
                      ORDER BY date, Id_op, heure
;
Create temporary table r2
SELECT a.Id_op, a.Heure_deb, MIN(b.heure_deb) as fin, TIMEDIFF(b.Heure_deb, a.Heure_deb) as Time_Difference, ROUND(HOUR(TIMEDIFF(b.Heure_deb, a.Heure_deb)) + MINUTE(TIMEDIFF(b.Heure_deb, a.Heure_deb))/60,2) as Decimal_duree
FROM Pointage a
LEFT JOIN Pointage b ON a.Id_op = b.Id_op
WHERE a.heure_deb < b.heure_deb
Group by a.Id_op, a.Heure_deb
;
select CONCAT(MONTH(r1.Heure_deb),'/',DAY(r1.Heure_deb)) as 'Date',
                      TIME(r1.Heure_deb) as heure,
                      r1.Id_op ,
                      Nom_op ,
                      Prenom_op,
                      Nom_act ,
                      Commentaire,
                      Type,
                      Time_Difference,
                      Decimal_duree
from r1
LEFT JOIN r2 ON r1.Id_op = r2.Id_op and r1.heure_deb = r2.heure_deb
Order by Id_op, Date , heure

This request return this in phpMyAdmin Request

But in PHP the fetch doesn't works, return false but any error code. PHP Code:

  $result=$objPdo->prepare($requete);
  $result->execute();
  $err = $result->errorInfo();
  if(!empty($err[2])){
  echo"<script type='text/javascript'>alert('Erreur SQL :".$err[2]."');</script>";
  }else{
  while($row=$result->fetch()){
    echo"<tr>
              <td>".$row['Date']."</td>
              <td>".$row['heure']."</td>
              <td>".$row['Nom_op']."</td>
              <td>".$row['Prenom_op']."</td>
              <td>".$row['Type']."</td>
              <td>".$row['Time_Difference']."</td>
              <td>".$row['Decimal_duree']."</td>
              <td>".$row['Commentaire']."</td>
          </tr>";
  }
  echo"</table></div>";
}

Answer 1

There are three queries in this request and therefore you have to run them in three calls, not one:

$objPdo->query("CREATE TEMPORARY TABLE r1
SELECT CONCAT(MONTH(Heure_deb),'/',DAY(Heure_deb)) as 'Date',
                      Heure_deb,
                      Operateur.Id_op ,
                      Nom_op ,
                      Prenom_op,
                      Nom_act ,
                      TIME(Heure_deb) as heure,
                      Commentaire,
                      Type
                      FROM Operateur, Pointage, Activite
                      WHERE Operateur.Id_op = Pointage.Id_op
                      AND Activite.Id_act = Pointage.Id_act
                      ORDER BY date, Id_op, heure
;";

$objPdo->query("Create temporary table r2
SELECT a.Id_op, a.Heure_deb, MIN(b.heure_deb) as fin, TIMEDIFF(b.Heure_deb, a.Heure_deb) as Time_Difference, ROUND(HOUR(TIMEDIFF(b.Heure_deb, a.Heure_deb)) + MINUTE(TIMEDIFF(b.Heure_deb, a.Heure_deb))/60,2) as Decimal_duree
FROM Pointage a
LEFT JOIN Pointage b ON a.Id_op = b.Id_op
WHERE a.heure_deb < b.heure_deb
Group by a.Id_op, a.Heure_deb
;";

$result = $objPdo->query("select CONCAT(MONTH(r1.Heure_deb),'/',DAY(r1.Heure_deb)) as 'Date',
                      TIME(r1.Heure_deb) as heure,
                      r1.Id_op ,
                      Nom_op ,
                      Prenom_op,
                      Nom_act ,
                      Commentaire,
                      Type,
                      Time_Difference,
                      Decimal_duree
from r1
LEFT JOIN r2 ON r1.Id_op = r2.Id_op and r1.heure_deb = r2.heure_deb
Order by Id_op, Date , heure";

while($row=$result->fetch()){
    echo"<tr>
          <td>".$row['Date']."</td>
          <td>".$row['heure']."</td>
          <td>".$row['Nom_op']."</td>
          <td>".$row['Prenom_op']."</td>
          <td>".$row['Type']."</td>
          <td>".$row['Time_Difference']."</td>
          <td>".$row['Decimal_duree']."</td>
          <td>".$row['Commentaire']."</td>
      </tr>";
}
echo"</table></div>";