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

Question

I am using PDO to access my data base and am looping using two while loops with fetch at the same time, seen below:

$DBH = new PDO('mysql:host=localhost;dbname=database;charset=utf8',$dblogin,$dbpass);
$sql = 'SELECT * FROM table';
$STH = $DBH->prepare($sql);
$STH->execute();

while ($bm_table = $STH->fetch(PDO::FETCH_ASSOC))
{
    // SQL Query
    $sql1 = 'QUERY HERE';
    $STH1 = $DBH->prepare($sql1);
    $STH1->execute();

    // Loops through using different handle, but what if I used STH again?
    while ($row = $STH1->fetch(PDO::FETCH_ASSOC))
    {
        SomeFunction($bm_table,$row);
    }
}

As you can see above I am using a different statement handle ($STH, $STH1 etc.) Is this necessary? Or can I use just one statement handle for everything. The reason I have used multiple handles is as the $bm_table value that uses $STH, will still be in use while I am fetching $row wouldn't that change the value of $bm_table or stop the fetch from working? How does the handles with PDO work? Especially when in this case I have two simultaneous fetch loops running at the same time using the same PDO connection.

So the main thing I am looking for here is if I have two statements that are running simultaneously is it important that I use different handles when I continue to use the same connection?

That's the point of prepared statements. Prepare the query once, and execute it as many time as you want, feeding in different parameters. — Sammitch, Oct 03 '15 at 00:07
This (this link: https://stackoverflow.com/questions/6346674/pdo-support-for-multiple-queries-pdo-mysql-pdo-mysqlnd/55169977#55169977) solution helps you to run multiple statement together, If an incorrect a statement occurs, it does not execute any other statement — Sajad Mirzaei, Mar 16 '19 at 07:15

score 1 · Accepted Answer · answered Oct 03 '15 at 00:11

1

$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
}

answered Oct 03 '15 at 00:11

Barmar

741,623
53
500
612

Hey @Barmar so just to clarify so just to clarify the second I overwrite the STH variable from within the loop it will break the fetch on the outer loop? So my use of different variables was correct and I should keep doing it the way I am? As in this case I always want the outer loop to execute the SQL "SELECT * FROM table" but if I change STH from within the loop for another quick SQL command it will break the one on the outer loop. – Oct 03 '15 at 00:23
Exactly. Just think of it logically. Every time through the loop, it executes `$STH->fetch()` and tests the result of it. If you change what `$STH` contains, then that affects what `$STH->fetch()` returns. – Barmar Oct 03 '15 at 00:27
Thanks @Barmar. So just to clarify what does it store in the $STH variable when I run the fetch? How does it keep track of every row its on? Isn't STH and object in this case as I'm calling the method fetch? – Oct 03 '15 at 00:29
`$STH` contains a `PDOStatement` object. That object holds the state of a particular query. – Barmar Oct 03 '15 at 00:30
You can think of it as being like an object that contains an array and a current index in the array. Every time you fetch, it returns the element at the index and increments it. – Barmar Oct 03 '15 at 00:32
So in the future when I want to run multiple queries at the same time I can use the same connection but just need to use different handles to prepare the statements and execute them both with different handles. – Oct 03 '15 at 00:33
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/91219/discussion-between-code-and-barmar). – Oct 03 '15 at 00:34

score 0 · Answer 2 · answered Oct 03 '15 at 00:12

If I understand you correctly what you want is dynamic query?... just put a parameter on your method then...

something like this. call it as much as you want with difference parameters though.

Class SampleClass{

 public function GetAll($tablename)
 {
     $sth = $this->prepare("SELECT * FROM $tablename");
     $sth->execute();
     return $sth->fetchAll();
 }

}

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

2 Answers2