CODEWITHSUNDEEP
phpmysqli
D_Guy13
D_Guy13

Reputation: 561

Prepared Statement, Fails when re-used

I have this code, works fine when looped through once.

<!-- language: php -->
<?php
$query = "SELECT username FROM users WHERE user_id = ? LIMIT 1";
$stmt = $con->prepare($query) or die(mysqli_error($con));
$stmt->bind_param("s", $user_id);
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($username);
?>

When I call it for the first time on the page, it loads fine and the data shows as it should.

<!-- language: php -->
<?php
    // This one works fine.
        while ($stmt->fetch()){
        echo 'Welcome '.$username;
        }
    ?>

If I want to re-use the same query somewhere else, it fails wihtout errors. Am I doing it wrong? What is the correct way to re-use the same query multiple time on the same page, Since the data is the same, I don't want to re-query the DB each time.

<!-- language: php -->
<?php
// When re-used somewhere else on the page, it fails. Nothing shows.
    while ($stmt->fetch()){
    echo 'Welcome '.$username;
    }
?>

Upvotes: 0

Views: 79

Answers (5)

Zbynek Vyskovsky - kvr000
Zbynek Vyskovsky - kvr000

Reputation: 18845

fetch returns one row after another. Once you fetch all rows, any further call to fetch() method will return false.

You need to re-execute() the query in order to get the same result again. This will go to call database again though. If you want to cache the result, you need to put it into some in-memory cache / global variable / whatever you prefer.

If you really want to get to the beginning of result set again, you can use mysqli_data_seek() for this:

mysqli_data_seek($stmt, 0);
while ($stmt->fetch()){
    echo 'Welcome '.$username;
}

Upvotes: 1

Lucas Martins
Lucas Martins

Reputation: 528

You can store the results:

<?
$query = "SELECT username FROM users WHERE user_id = ? LIMIT 1";
$stmt = $con->prepare($query) or die(mysqli_error($con));
$stmt->bind_param("s", $user_id);
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($col['username']);

$result = array();
for ($i = 0; $i < $stmt->num_rows; $i++){
    $stmt->data_seek($i);
    $stmt->fetch();
    foreach ($col as $key => $value){
        $result[$i][$key] = $value;
    }
}
?>

Upvotes: 0

D_Guy13
D_Guy13

Reputation: 561

I needed to split up the code to achieve what I needed with MySQL(i). Thanks for your inputs.

<?php
// Preparing the statement
$query = "SELECT username FROM users WHERE user_id = ? LIMIT 1";
$stmt = $con->prepare($query) or die(mysqli_error($con));
$stmt->bind_param("s", $user_id);
$stmt->store_result();
?>


<?php
// Executing it when needed and creating some variables for later re-use
    $stmt->execute();
    $stmt->bind_result($username);
    while ($stmt->fetch()) {
    $new_username = $username;
    echo 'Welcome '.$new_username;
    }
?>

<?php
// It is possible now to re-use the same result set
    echo 'Welcome '.$new_username;
?>

Upvotes: 0

Alok Patel
Alok Patel

Reputation: 8022

What you can do is get the result-set as an ASSOC Array and save the result in a variable. Now you can use this array as many times as you want.

Update your code like this,

$stmt->setFetchMode(PDO::FETCH_ASSOC);
$results = $stmt->fetchAll();

foreach ($results as $r) {
    //first time use
}

foreach ($results as $r) {
    //second time use
}

Upvotes: 0

rap-2-h
rap-2-h

Reputation: 32028

You could pull the query result into an array with fetchAll

$users = $stmt->fetchAll();

// this works
foreach($users as $user) {
    echo 'Welcome ' . $username;
}

// this works again
foreach($users as $user) {
    echo 'Welcome ' . $username;
}

More information here: http://php.net/manual/fr/pdostatement.fetchall.php

Upvotes: 0

Related Questions