MySQL - guidance on multiple WHERE clause

Do you want a single query where the values in your array are in the where ? Like this:

$values = implode(",", $your_array);
  $query = "SELECT * FROM table WHERE column IN ($values)"

Hi

Thanks for that. From what I understand this may well be the solution. The only problem is deploying it.

So far I'm trying ...

<?php


$Prescriber_Name = $_POST[thing];
$values = implode(", ", $Prescriber_Name); 

// open the connection
$conn = mysql_connect("location", "username", "password");

// pick the database to use
mysql_select_db("database",$conn);

//create the sql statement
$query = "SELECT * FROM  prescribing_test WHERE prescribing_test.Prescriber_Name IN ($values)"


//execute the SQL statement
$result = mysql_query($query, $conn) or die(mysql_error());



mysql_close($conn);


?>

<table>
<tr>
<th><B>Prescriber Name</B></th>
<th><B>BNF Name</B></th>
<th><B>Total Items</B></th>
</tr>

<?

//go through each row in the result set and display data
while($newArray = mysql_fetch_array($result)) 

{

?>

<tr>
<td><?=$newArray["Prescriber_Name"]?></td>

<td><?=$newArray["BNF_Name"]?></td>

<td>><?=number_format($newArray["Total_Items"],0,'.',',')?></td>

</tr>
<?
	
}


?>

</table>

... but its not working. Can you tell me if I am on the right track here please?

Can you tell me what's not working ?

If nothing is displayed, perhaps because you need to

<?php echo $newArray["BNF_Name"]; ?>

I don't know if your site is setup to use <?= as a short tag for echo.

Looks like you need to put single quotes around $value in setting the $query variable, such as follows:

$query = "SELECT * FROM prescribing_test WHERE prescribing_test.Prescriber_Name IN ('$values')";

SQL Tutorial

Thanks again for taking the time to help me with this.

As it stands, the code I posted doesn't display anything ( all I get is a blank page, no graphics and no text).

When I add single quotes and a semi colon to the query as suggested

$sql = "SELECT * FROM  prescribing_test WHERE Prescriber_Name IN ('$value')";

I get my graphics, text and table headings but unfortunately no output from the query. I've tried

<?php echo $newArray["BNF_Name"]; ?>  <?php echo $newArray["BNF_Name"]; ?>

but it didn't change anything.

I have added a row count to the code

$num_rows = mysql_num_rows($result);
echo "$num_rows Rows";

and this shows zero rows. Does this suggest that I'm not getting the data into the query in the first place?

Following on from my last posting and the tweaks made, I have discovered that selecting a single entry from the multi line list box works. Full results are displayed as required. However, it is the multiple selection that is failing. With more than one line selected I get zero rows returned.

I've found the problem!

Adding single quotes to the comma in the implode statement now gives me the expected results.

$values = implode("','", $_POST[thing]);

Not sure yet why this is needed but hey it works.

Thanks guys!

Your values are string, and therefor all need to be in separate quotes whithin the IN (...). I was expecting id's (integers) and then they are not needed. My apologies.

Hi pritaeas

No need to apologise. I didn't exactly give a lot of info at the start.

If it wasn't for you suggesting the implode option I would still be stumbling around in the dark. I really appreciate your help.