CODEWITHSUNDEEP
phpmysqlarraysloops
animal
animal

Reputation: 57

Multiple mysql queries from within for loop

hope someone can help with this problem that's been driving me insane for ages. I'm quite new to mysql/php.

I have an array [$Booklist] that consists of a series of numbers that refer to BookID in the Books table. I then query the database to get the details associated with each ID:

for($s=0;$s<$smax+1;$s++){
  $sql="SELECT * FROM Books WHERE BookID = '$Booklist[$s]' ";
  $result=mysql_query($sql) or exit();

  while($row = mysql_fetch_array($result))
   {
    $Title[$s]=$row['Title'];
    $Author[$s]=$row['Author'];
    $Publisher[$s]=$row['Publisher'];
    $Year[$s]=$row['Year'];
  }
}

This works fine. But I've never liked running multiple queries from a php for loop.

Importantly, duplicate BookIDs in the $Booklist array are allowed. And the order of IDs in the $Booklist array must be retained (with the duplicates) so that the contents of the $Title and $Author arrays have the correct order, including duplicates. Hope that makes sense!

I tried doing this:

$sql="SELECT * FROM Books WHERE BookID IN ('".join("','", $Booklist)."') 
ORDER BY FIELD(BookID, '".join("','", $Booklist)."')";
$s=0;
  while($row = mysql_fetch_array($result))
   {
    $Title[$s]=$row['Title'];
    $Author[$s]=$row['Author'];
    $Publisher[$s]=$row['Publisher'];
    $Year[$s]=$row['Year'];
    $s++;
  }

But it meant any duplicates in $Booklist weren't mirrored in $Title, $Author etc. so I scrapped it.

My question is - is there a better solution to the mysql query inside a for loop? Or, is there a problem with running multiple mysql queries from a for loop??? - is it simpler to stick with that?

Thanks!

Upvotes: 3

Views: 2320

Answers (2)

DiverseAndRemote.com
DiverseAndRemote.com

Reputation: 19888

I think what you need is the following. It does a single query and then creates your 4 arrays based on the data returned by your query while using the $booklist variable for the order:

$sql = "SELECT * FROM Books WHERE BookID IN ('".join("','", array_map('mysqli_real_escape_string',$Booklist))."')";
$result = mysqli_query($sql) or exit();
$rows   = array();
while ($row = mysqli_fetch_array($result)) {
    $rows[$row['BookId']] = $row;
}

$i = 0;
foreach ($Booklist as $bookId) {
    if (isset($rows[$bookId])) {
        $Title[$i]     = $rows[$bookId]['Title'];
        $Author[$i]    = $rows[$bookId]['Author'];
        $Publisher[$i] = $rows[$bookId]['Publisher'];
        $Year[$i]      = $rows[$bookId]['Year'];
        $i++;
    }
}

Also note that I added mysqli_real_escape_string to prevent sql injection

Upvotes: 2

Jean
Jean

Reputation: 772

I am not sure that I get wath the problem is with your code, but whould this works for you ? I am simply putting the retrieved books in an array. Duplicates books should all be there.

$books = array();
$sql="SELECT * FROM Books WHERE BookID IN ('".join("','", $Booklist)."')";
while($row = mysqli_fetch_array($result)){
    $books[] = $row;
}

Upvotes: 1

Related Questions