[SOLVED] Trouble with Mysql IN

[gamesmstr]

The problem I have is using a variable array in a MYSQL IN condition.

 

$station_equipment=array(1,2,3,4,5);
$weapons=mysql_query("SELECT * FROM equipment WHERE category='weapon' and id IN ('$station_equipment') ORDER BY id ASC");

 

This returns an empty set where as:

 

$weapons=mysql_query("SELECT * FROM equipment WHERE category='weapon' and id IN (1,2,3,4,5) ORDER BY id ASC");

 

Works fine.  Any suggestions?

 

[itaym02]

Use the explode function http://php.net/explode to transform the array to a string which will look like that:

"1,2,3,4,5,6"

[gamesmstr]

Explode, transforms Strings into an array of strings, not the other way around, however I'll look at implode and see what that does.

[kickstart]

Hi

 

He means implode (and I made exactly the same mistake a couple of days ago).

 

All the best

 

Keith

[gamesmstr]

Nope not working.  But now instead of an empty set, it returns only the result from the first element of the array.

[PFMaBiSmAd]

You would need to post your current code and an example of what the data in the table looks like.

[kickstart]

Hi

 

Is this what you have:-

 

$weapons=mysql_query("SELECT * FROM equipment WHERE category='weapon' and id IN (".explode(',',$station_equipment).") ORDER BY id ASC");

 

All the best

 

keith

[gamesmstr]

You would need to post your current code and an example of what the data in the table looks like.

 

You Really don't want me to do that It is huge, complicated, and filled with includes and ajax calls and all of it works.

 

The only thing that doesn't work is the query using IN.  It works fine without it, (just doesn't screen the results).  It also works fine if I manually add the data in a string.  The problem arises when a variable is used with IN.

 

I'll create a simplified section to describe my problem:

 

Pertinent table data:

 

ID = INT(11);

 

Table: equipment

ID  category

1  weapon

2  weapon

3  weapon

4  weapon

5  weapon

 

<?php
include "common/head.php";
$station_equipment=array(1,2,3,4,5);
$station_equipment2=implode(",",$station_equipment);
$weapons=mysql_query("SELECT * FROM equipment WHERE category='weapon' AND id IN (1,2,3,4,5) ORDER BY id ASC");
$weapons2=mysql_query("SELECT * FROM equipment WHERE category='weapon' AND id IN ('$station_equipment') ORDER BY id ASC");
$weapons3=mysql_query("SELECT * FROM equipment WHERE category='weapon' AND id IN ('$station_equipment2') ORDER BY id ASC");
$result=mysql_num_rows($weapons);
$result2=mysql_num_rows($weapons2);
$result3=mysql_num_rows($weapons3);
echo"result = $result, result2 = $result2, result3 = $result3";
?>

 

The result is this:

 

result = 5, result2 = 0, result3 = 1

[kickstart]

Hi

 

Remove the single quotes from around $station_equipment2. It is currently trying to find an id that is equal to '1,2,3,4,5'

 

All the best

 

Keith

[gamesmstr]

That did it!  Thanks!