CODEWITHSUNDEEP
phpmysqlforeach
Tural Ali
Tural Ali

Reputation: 23290

MySQL query within foreach loop

I want to show all text messages from db where id=$e ($err is an array).
Inserted the query into the foreach loop, it works well but it does extra work (does query for every value of array).
Is there any other way to do it (i mean extract query from foreach loop)?
My code looks like this. 

foreach ($err as $e) 
{
$result = $db -> query("SELECT * from err_msgs WHERE id='$e'");
$row = $result -> fetch_array(MYSQLI_BOTH);
echo "<li><span>".$row[1]."</span></li>";
}

Upvotes: 0

Views: 8453

Answers (4)

Johan
Johan

Reputation: 76753

You can do this much simpler by doing 

$err_csv = implode("','",$err);
$sql = "SELECT FROM err_msgs WHERE id IN ('$err_csv')";
$result = $db -> query($sql);
while ($row = $result -> fetch_array(MYSQLI_BOTH)) 
{
   echo "<li><span>".$row[1]."</span></li>";
}

That way you don't have to keep sending queries to the database.

Links:
http://php.net/manual/en/function.implode.php

Upvotes: 0

DaveRandom
DaveRandom

Reputation: 88697

It is much more efficient to do this with implode() because it will only result in one database query.

if (!$result = $db->query("SELECT * FROM `err_msgs` WHERE `id`='".implode("' OR `id`='",$err)."'")) {
  echo "Error during database query<br />\n";
  // echo $db->error(); // Only uncomment this line in development environments. Don't show the error message to your users!
}
while ($row = $result->fetch_array(MYSQLI_BOTH)) {
  echo "<li><span>".$row[1]."</span></li>\n";
}

Upvotes: 4

PhilDin
PhilDin

Reputation: 2842

Firstly, a bit of a lecture: embedding strings directly into your queries is going to cause you trouble at some point (SQL injection related trouble to be precise), try to avoid this if possible. Personally, I use the PDO PHP library which allows you to bind parameters instead of building up a string.

With regard to your question, I'm not sure I have understood. You say that it does extra work, do you mean that it returns the correct results but in an inefficient way? If so then this too can be addressed with PDO. Here's the idea.

Step 1: Prepare your statement, putting a placeholder where you currently have '$e' Step 2: Loop through $err, in the body of the loop you will set the place holder to be the current value of $e

By doing this you not only address the SQL injection issue, you can potentially avoid the overhead of having to parse and optimise the query each time it is executed (although bear in mind that this may not be a significant overhead in your specific case).

Some actual code would look as follows:

// Assume that $dbdriver, $dbhost and $dbname have been initialised
// somewhere. For a mysql database, the value for $dbdriver should be
// "mysql".

$dsn = "$dbdriver:host=$dbhost;dbname=$dbname";
$dbh = new PDO($dsn, $dbuser, $dbpassword); 

$qry = "SELECT * from err_msgs WHERE id = :e"
$sth = $dbh->prepare($qry);

foreach ($err as $e) {
    $sth->bindParam(":e", $e);
    $sth->execute();
    $row = $sth->fetch();

    // Prints out the *second* field of the record
    // Note that $row is also an associative array so if we
    // have a field called id, we could use $row["id"] to 
    // get its value
    echo "<li><span>".$row[1]."</span></li>";
}

One final point, if you want to simply execute the query once, instead of executing it inside the loop, this is possible but again, may not yield any performance improvement. This could be achieved using the IN syntax. For example, if I'm interested in records with id in the set {5, 7, 21, 4, 76, 9}, I would do:

SELECT * from err_msgs WHERE id IN (5, 7, 21, 4, 76, 9)

I don't think there's a clean way to bind a list using PDO so you would use the loop to build up the string and then execute the query after the loop. Note that a query formulated in this way is unlikely to give you any noticable performance improvment but it really does depend on your specific circumstances and you'll just have to try it out.

Upvotes: 1

xdazz
xdazz

Reputation: 160973

Check the SQL IN clause.

Upvotes: 1

Related Questions