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!