MySQL - guidance on multiple WHERE clause

[nibbler]

I'm looking for some guidance on how to develop a msql query that loops through elemenets in a php array. The array is constructed from a multiple line list box.

[pritaeas]

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)"

[nibbler]

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?

[pritaeas]

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.

[oldcat]

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

[nibbler]

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?

[nibbler]

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.

[nibbler]

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!

[pritaeas]

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.

[nibbler]

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.