3

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!

animal
  • 57
  • 5

2 Answers2

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

DiverseAndRemote.com
  • 19,314
  • 10
  • 61
  • 70
  • 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 Nov 10 '12 at 08:02
  • oh. thanks for pointing out mysqli_real_escape by the way. Appreciated. – animal Nov 10 '12 at 08:03
  • 1
    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]. – animal Nov 10 '12 at 08:12
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;
}
Jean
  • 762
  • 5
  • 12
  • [Please don't use mysql_* functions. They will be deprecated.](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-function-in-php) – Yes Barry Nov 10 '12 at 07:21
  • Thanks. But it's preferred to use [prepared statements](http://php.net/manual/en/mysqli.prepare.php). – Yes Barry Nov 10 '12 at 07:25
  • 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. – animal Nov 10 '12 at 07:42