Pdo Multiple Fetching of Same Query

PDO multiple fetching of same query

You can't fetch from the DB multiple times like this. Do this instead:

$orders = $ordersQuery->fetchAll(PDO::FETCH_ASSOC);

...

foreach ($orders as $val) {
    // stuff 1
}

...

foreach ($orders as $val) {
    // stuff 2
}

PHP - PDO - How to fetch multiple rows with only one query?

You need modify query to

SELECT * FROM users WHERE ID IN(1,4,17);

and use fetchAll() method which returns all records instead of one.

If you don't want to use fetchAll(); then you need use fetch() in loop and you need still modify query.

while ($user = $result->fetch(PDO::FETCH_ASSOC)) {
  print_r($user);
}

Notice: you use prepared statements without parameters.

PDO multiple queries

It turns out that you need to use PDOStatement::nextRowset.

$stmt   = $db->query("SELECT 1; SELECT 2;");
$stmt->nextRowset();
var_dump( $stmt->fetchAll(PDO::FETCH_ASSOC) );

This will return result for the second query.

It is a bit odd implementation. It would certainly be easier if multi-query statement would just return both results sets under one array. However, the advantage is that this implementation allows to fetch every query using different FETCH styles.

fetch multiple select in pdo

You can use ->nextRowset() to access the next data (which in case the count). First get (fetch) the desired rows. Then get the count:

<?php
$stmt = $dbh->prepare("
    SELECT SQL_CALC_FOUND_ROWS * FROM table WHERE mark=0 ORDER BY id ASC LIMIT 0, 15; 
    SELECT FOUND_ROWS() as total;
");

$stmt->execute();

$values = $stmt->fetchAll(PDO::FETCH_OBJ);

$stmt->nextRowset(); // shift to the total

$count = $stmt->fetchColumn(); // get the total

?>

<span>Number of rows: <? echo $count; ?></span>

<?php
if($count > 0) {
    foreach($values as $v) {
        // iterate fetched rows
        echo $v->id;
    }
}
?>

PHP PDO how to run a multiple query request?

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

PDO fetch multiple records from database

Here's what's going on; you're mixing MySQL APIs/functions and those do not intermix.

Replace the :userid (PDO) bind in b.aankoop_username_id = :userid with a ? placeholder

b.aankoop_username_id = ?

Then this line:

$query->bind_param(':userid', $id, PDO::PARAM_INT);

Replace :userid by $id and remove , PDO::PARAM_INT but adding i

$query->bind_param("i", $id);

Sidenote: Make sure that column is int type. If not, use s instead of i.

Replace the line for fetchAll with the loop as outlined in AbraCadaver's answer.

You can't mix MySQL APIs/function, read the following on Stack:
Can I mix MySQL APIs in PHP?

Read up on mysqli with prepared statements and how it works:

http://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php

Checking for errors would have outlined the errors.

http://php.net/manual/en/mysqli.error.php
http://php.net/manual/en/function.error-reporting.php

PHP PDO Multiple Result sets in one query or multiple separate queries

Maybe this will help, I sudo coded out a bunch of stuff but hopefully it gets the idea across.

class Article extends Entity
{
    public $id;
    public $title;
    public $body;
    public $comments = [];
}

class Comment extends Entity
{
    public $id;
    public $title;
    public $comment;
    public $user_name;
    public $created_at;
    public $updated_at;
    public $edited = false;
}

class ArticleDao
{
    public function get(int $id): Article {
       // query for article that has primary key $id
       // $object = fetch::class
       $object->comments = (new ArticleCommentDao())->findByArticle($id);
       return $object;
    }
}

class ArticleCommentDao
{
    public function findByArticle(int $articleId): array {
        // query for comments that have foreign key $articleId
        $comments = [];
        // foreach result in resultset
        // $object = fetch::class
        $comments[] = $object;
        return $comments;
    }
}

Executing Multiple Queries Using PDO

Run your first query which is the insert then after success on that one get the last insertid then use the id on your next query.. Eg.

<?php

try {

        $db = new Database(); //Create a new object of type Database establishing a connection to the MySQL database

        $query = $db->prepare("INSERT INTO orders (order_type`, `item`, `amount`, `price`, `price_btc`, `status`, `timestamp`, `placed_by`, `secret`, `first_name`, `last_name`, `address_1`, `address_2`, `city`, `zip_code`, `country`, `state`, `phone_number`) VALUES(:order_type, :item, :amount, :price, :price_btc, :status, :timestamp, :placed_by, :secret, :first_name, :last_name, :address_1, :address_2, :city, :zip_code, :country, :state, :phone_number)");

        $query->execute(array( /* your values*/ ));

        $lastId = $db->lastInsertId(); // fetch last insert id, after success.

        $order = $db->prepare("SELECT * FROM `orders` WHERE `ID`=?");
        $order->bindValue(1, $lastId);
        $order->execute();
        //Fetch your records and display.

}
catch (PDOException $e) {
        echo "Error : " . $e->getMessage();

}

?>

I left some part of the codes like you did, but the important thing is to run the insert first then collect the last

PDO can I reuse the same Statement Handle for multiple queries?

$STH and STH1 are not statement handles, they're just PHP variables. You can reassign a variable if you no longer need its old value. But in the case of this code, you still need the old value.

If you assign $STH inside the outer loop to the handle returned by the second prepare() call, then when it gets back to the top of the loop and re-executes the $STH->fetch() test, it will try to fetch from the second query, not the first one. This will immediately end the outer loop because all those rows have been read.

You can reuse a statement handle for repetitions of the same query. This is very useful when the query has parameters:

$stmt = $DBH->prepare("SELECT * FROM tablename WHERE id = :id");
$stmt->bindParam(':id', $id);
foreach ($id_array as $id) {
    $stmt->execute();
    $row = $stmt->fetch();
    // do stuff with $row
}

Pdo Multiple Fetching of Same Query