Multiple mysql queries from within for loop

Question

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!

Please don't use mysql_* functions.

Yes Barry
– Yes Barry

2012-11-10 07:18:54 +00:00
Commented Nov 10, 2012 at 7:18 — Yes Barry
– Yes Barry, Commented Nov 10, 2012 at 7:18
take a look at my answer. I think it does what you need

DiverseAndRemote.com
– DiverseAndRemote.com

2012-11-10 07:52:13 +00:00
Commented Nov 10, 2012 at 7:52 — DiverseAndRemote.com
– DiverseAndRemote.com, Commented Nov 10, 2012 at 7:52

DiverseAndRemote.com · Accepted Answer · 2012-11-10 07:51:00Z

2

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

edited Nov 10, 2012 at 7:51

answered Nov 10, 2012 at 7:34

DiverseAndRemote.com

20k10 gold badges65 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

animal Over a year ago

Brilliant. I think that's got it. Thanks. Gonna do a bit more testing to confirm before I put the green tick. But I think you've cracked it.

animal Over a year ago

oh. thanks for pointing out mysqli_real_escape by the way. Appreciated.

animal Over a year ago

Yup, it works alright. Thanks so much. Feels so good to get rid of those ugly looped queries. [As soon as I have 15 reputation I'll +1 this].

Jean · Accepted Answer · 2012-11-10 07:20:36Z

1

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;
}

answered Nov 10, 2012 at 7:20

Jean

7725 silver badges13 bronze badges

3 Comments

Yes Barry Over a year ago

Please don't use mysql_* functions. They will be deprecated.

Yes Barry Over a year ago

Thanks. But it's preferred to use prepared statements.

animal Over a year ago

Thanks for the suggestion. The problem is i don't think the $books array will come out with the same order as $Booklist - and i don't think it will mirror any duplicate elements present in $Booklist. This is important for what I need to do.

Collectives™ on Stack Overflow

Multiple mysql queries from within for loop

2 Answers 2

3 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related