Querying WHERE column is IN ($array) returns only one row

Question

I'm trying to query all results where surveyid contains my array $subscibearray. The problem is the SQL query is only returning the first result (44 in this case) instead of all of them. I tried using mysql_fetch_array as well without any success. My results are being outputted via Table. Any help would be appreciated. I omitted as much code as possible to make this easy to read.

Array format:

$subscribearray = "" . join(', ',$subscribearray) . "";

var_dump of $subscribearray:

string(17) "'44, 35, 194, 36'"

query:

$result = mysql_query("SELECT * 
                       FROM surveys 
                       WHERE surveyid IN ($subscribearray) 
                       ORDER BY peercompletetime DESC 
                       LIMIT 100") 
or die(mysql_error());

you should also switch over to MySQLi, MySQLi is MySQL Improved. The reason behind this is because MySQL has been deprecated since around PHP 5. MySQLI is more secure and such. It isn't that hard, for example mysql_query becomes mysqli_query (note the i after mysql). — Tyler, Aug 24 '16 at 18:56
Every time you use [the `mysql_` database extension](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php), **a Kitten is strangled somewhere in the world** it is deprecated (gone for ever in PHP7) Specially if you are just learning PHP, spend your energies learning the `PDO` database extensions. [Start here](http://php.net/manual/en/book.pdo.php) its really pretty easy — RiggsFolly, Aug 24 '16 at 18:56
what do you get when you var_dump the $result? I'm mostly interesting in the num_rows and field_count values — Tyler, Aug 24 '16 at 18:58
If you think about how you would code an `IN()` query it would look like this `WHERE surveyid IN (1,2,3,4,5)` **and not** `WHERE surveyid IN ('1,2,3,4,5')` so that is what you have to mimic in your php statement that preps the data for this variable — RiggsFolly, Aug 24 '16 at 18:59

score 1 · Accepted Answer · answered Aug 24 '16 at 19:01

You should:

$subscribearray = join(',', $subscribearray); // 1,2,3

Or:

$subscribearray = "'" . join("','", $subscribearray) . "'"; // '1','2','3'

If you wrap all ids with a string, it will look for the string, and not separated values. ('1,2,3')

Of course, if it's a numeric column you should use the first one.

Querying WHERE column is IN ($array) returns only one row

1 Answers1