php - Store and iterate over result of query in mysqli

Question

This is my simple query in php, using mysqli object oriented style:

$query = "SELECT name FROM usertable WHERE id = ?";
$stmt = $mysqli->prepare($query);
$stmt->bind_param('i', $id);
$id= $_GET['id'];
$stmt->execute();
$stmt->bind_result($name);

while($stmt->fetch()){
   echo $name." ";
}

$stmt->free_result();
$stmt->close();

This works fine. I obtain the list of name retrieved from the select statement.

Now, inside the while I want use the $name variable as parameter for another query, but mysqli do not allow this, since I have to close the first query and then call the second query.

So I think I have to store the result of the first query and then iterate over the result calling a new query.

I have tried the following:

$query = "SELECT name FROM usertable WHERE id = ?";
$stmt = $mysqli->prepare($query);
$stmt->bind_param('i', $id);
$id= $_GET['id'];
$stmt->execute();
//$stmt->bind_result($name);
$result = $stmt->store_result();
$stmt->free_result();
$stmt->close();

while ($row = $result->fetch_row()) 
{
    echo $row[0]." ";
}

But this does not work. The code inside while is never reached.

N.B.: I want avoid the use of multi_query().

Answer 1

mysqli_stmt::store_result return a boolean. According to the doc it should be something like:

$stmt->execute();
$stmt->store_result();

$stmt->bind_result($name);

while($stmt->fetch()){
    //echo $name." ";
    // try another statement
    $query = "INSERT INTO usertable ...";
    $stmt2 = $mysqli->prepare($query);
    ...
}

$stmt->free_result();
$stmt->close();

If this doesn't work you can fetch all rows first into an array and then looping that array again:

$stmt->execute();
$stmt->bind_result($name);
$names = array();
while($stmt->fetch()){
    $names[] = $name;
}
$stmt->free_result();
$stmt->close();

foreach($names as $name) {
    $query = "INSERT INTO usertable ...";
    $stmt = $mysqli->prepare($query);
    ...
}