准备好的语句在while循环中由预准备语句生成

Question

so i was just wondering if this is a good practice or not, or for some reason does this type of code affect the speed and functionality of a system ?

$foo = "bar";
$stmt = $db->prepare('SELECT * FROM table WHERE bar=?');
$stmt->bind_param('s',$foo);
$stmt->execute();
$result = $stmt->get_result();
   while($row = $result->fetch_assoc()){
       $val1 = $row['val1'];
       $val2 = $row['val2'];
       echo "<section>";
          $stmt2 = $db->prepare('SELECT * FROM table2 WHERE bar=?');
          $stmt2->bind_param('s',$foo);
          $stmt2->execute();
          $result2 = $stmt2->get_result();
             while($row = $result2->fetch_assoc()){
                 $val1 = $row['val1'];
                 $val2 = $row['val2'];
             }
          $stmt->close();
      echo "</section>";
    }
$stmt->close();

the first statement could generate 50 or more data, that means that another 50 or more stmt will be produced, is this bad ? thanks for the answers.

Answer 1

Every time you prepare a statement, it requires a call to MySQL, which is expensive. If it's the same statement, this is unnecessary and a waste of time.

You also only need to bind the params once. bind_param associates the parameters with a reference to the variable. So the loop only needs to update the variable's value and call execute.

In your code, it seems like repeating the inner query every time through the loop is unnecessary. It's not dependent on anything retrieved from the outer query, so it will return the same set of results each time. You should do it once, save the results in an array, and then just loop through the array every time, to avoid hitting the database unnecessarily.

If that was just an artificial example, and you really do have a dependency between the data returned from the outer query and the parameters to the inner query, you probably should do them as a single query containing a JOIN between the two tables.