Insert in Loop not working 1

[benderulz]

Can anyone tell me why my insert statement in the loop is only inserting the first record? The code completes 5 trips through the loop and it is selecting different sets each time through (I can see the sets with the echo at the end of the loop), but it only inserts the first set.

$count = 0;

While ($count < 10){
 
$sql= "SELECT id 
FROM quest
WHERE used = '0'
ORDER BY RAND()
LIMIT 1";
 
 $results = mysqli_query($con,$sql);
 
 while ($row=mysqli_fetch_array($results)){
 $id = $row['id'];
  }
  
$mark = "UPDATE quest
SET used = '1'
WHERE id = '$id'";

mysqli_query($con,$mark);

$sql2= "SELECT id 
FROM quest
WHERE used = '0'
ORDER BY RAND()
LIMIT 1";
 
$results2 = mysqli_query($con,$sql2);
 
 while ($row=mysqli_fetch_array($results2)){
 $id2 = $row['id'];
  }
  
$mark2 = "UPDATE quest
SET used = '1'
WHERE id = '$id2'";

mysqli_query($con,$mark2);

$build = "INSERT INTO holding
(q1,q2) VALUES ('$id','$id2')";

mysqli_query($con,$build);

echo $id;
echo $id2;
echo "<br>";

$count = $count + 2;
}

mysqli_close($con);

 

[jpadie]

nothing looks wrong in the code at first glance. So i suspect that the loop is failing for a query error or simply because there are no more table entries that are 'unused'.

i have done a minor amount of optimisation to your code as shown. you were redeclaring variables each iteration, which (for example) is unnecessary. Also I could not work out why you were doing the exercise twice each loop iteration rather than selecting two randoms and doing it once? Taken to a wider perspective, why not select all ten randoms at once and then enter them in couplets to the holding table? You could get a comma delimited list of ten randoms from the table in one query, update the used status in a second query and then put them in a holding table in a third. No loops required.

anyway, for what it's worth here is some code:

<?php
$count = 0;
$getSql= "SELECT id FROM quest WHERE used = '0' ORDER BY RAND() LIMIT 2";
$markSql = "UPDATE quest SET used = '1' WHERE id IN ('%s', '%s')";
$buildSql = "INSERT INTO holding (q1,q2) VALUES ('%s','%s')";
while($count < 5):
    $ids = array(); //reset array
    $results = mysqli_query($con,$getSql) or die(mysqli_error($con));
    while($row = mysqli_fetch_array($results)) $ids[] = $row['id'];
    mysqli_free_result($results);
    mysqli_query($con,vsprintf($markSql, $ids)) or die(mysqli_error($con));
    mysqli_query($con,vsprintf($buildSql, $ids)) or die(mysqli_error($con));
    echo '<pre>' . print_r($ids, true) . '</pre>';
    $count++;
endwhile;
mysqli_close($con);
?>

 

[benderulz]

It was an issue with my primary key settings. I see what you are talking about reassigning the variables. I was running the two select queries because I did not know how to insert into 2 different columns from the same query. Thank you for help. I learned a lot from your optimization.