Asked  7 Months ago    Answers:  5   Viewed   29 times

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>";
}

 Answers

86

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>";
Wednesday, March 31, 2021
 
edsk
answered 7 Months ago
64

It seems that PDO::MYSQL_ATTR_FOUND_ROWS is a mysql connection option. Thus, it works only as PDO connection option as well. So, set it up this way

$opt  = array(
    PDO::MYSQL_ATTR_FOUND_ROWS   => TRUE,
    // you may wish to set other options as well
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
);
$this->_db = new PDO($dsn,DB_USER,DB_PASS,$opt);
Wednesday, March 31, 2021
 
shivam
answered 7 Months ago
30

There are several ways to achieve this, but if you prefer the query builder way

$results = Yii::app()->db->createCommand()->
          select('id, filename, uploadDate')->
          from('files')->
          order('uploadDate DESC')->
          limit(5)->
          queryAll();

var_dump($results);

Read this documentation for more detail : http://www.yiiframework.com/doc/guide/1.1/en/database.query-builder

Saturday, May 29, 2021
 
linjuming
answered 5 Months ago
10

Here's the code as if PHPUnit 3.7.13

$ phpunit --configuration config.xml --testsuite Library
$ phpunit --configuration config.xml --testsuite XXX_Form

If you want to run a group of the test suites then you can do this

<testsuites>
  <testsuite name="Library">
    <directory>library</directory>
  </testsuite>
  <testsuite name="XXX_Form">
    <file>library/XXX/FormTest.php</file>
    <directory>library/XXX/Form</directory>
  </testsuite>
  <testsuite name="Both">
    <directory>library</directory>
    <file>library/XXX/FormTest.php</file>
    <directory>library/XXX/Form</directory>
  </testsuite>
</testsuites>

Then

$ phpunit --configuration config.xml --testsuite Both

Unfortunately PHPUnit currently does not support nested testsuites like this

<testsuites>
    <testsuite name="Both">
      <testsuite name="Library">
        <directory>library</directory>
      </testsuite>
      <testsuite name="XXX_Form">
        <file>library/XXX/FormTest.php</file>
        <directory>library/XXX/Form</directory>
      </testsuite>
  </testsuite>
</testsuites>

So if you wanted to run groups of test suites this way you have to have xml configuration duplication!

Monday, June 21, 2021
 
chugadie
answered 5 Months ago
29

Your PDO is configured to emulate prepared queries, whereas mysqli is using true prepared queries.

The prepared query binds the string ''1'' as an integer parameter value. PHP coerces it to an integer using something like intval(). Any string with non-numeric leading characters is interpreted as 0 by PHP, so the parameter value sent after prepare is the value 0.

The fake prepared query uses string interpolation (instead of binding) to add the string ''1'' into the SQL query before MySQL parses it. But the result is similar, because SQL also treats a string with non-numeric leading characters in an integer context as the value 0.

The only difference is what ends up in the general query log when the parameter is bound before prepare versus after prepare.

You can also make PDO use real prepared queries, so it should act just like mysqli in this case:

$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

PS: This may demonstrate a good reason why it's customary to start id values at 1 instead of 0.

Wednesday, August 18, 2021
 
derobert
answered 3 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share