Small Php and MySql problem

Question

I have an array of ID:s, and the ID:s are in this format:

  Bmw_330ci_89492822

So it's a string!

Now, I have this code to find whatever is in that array, in MySQL: ($solr_id_arr is the array I mentioned above, it contains string ID:s) ex: $solr_id_arr[0] outputs Bmw_330ci_89492822

$solr_id_arr_imploded = implode(", ", $solr_id_arr);
$query = "SELECT * FROM my_table WHERE ad_id IN ('$solr_id_arr_imploded')";
$qry_result = mysql_query($query) or die(mysql_error());

Problem is this wont work because (I think) that there should be quotes around each of the imploded elements in order for MySQL to find the match. The field in MySQL I am matching is of type Varchar.

Here is the $query echoed:

 SELECT * FROM my_table WHERE ad_id IN ('Bmw_m3_cool_565440282, Bmw_m5_839493889')

Do you have any other solutions for this, all I need is to find matches in MySQL which are inside this array!

Thanks

Answer 1

Don't surround the entire thing in quotes. It is looking for where ad_id is 'Bmw_m3_cool_565440282, test'

Use

SELECT * FROM my_table WHERE ad_id IN ('Bmw_m3_cool_565440282', 'test')

A quick fix would be to change:

//this
$solr_id_arr_imploded = implode(", ", $solr_id_arr);

//to this
$solr_id_arr_imploded = implode("', '", $solr_id_arr);

Answer 2

Simple switch to ', ' in implode():

implode("', '", $solr_id_arr);

This, together with the hardcoded quotes in the SQL string will format them as separate items.

Answer 3

Previous answers will work fine.

Just make sure the strings themselves do not contain quotes. If they do, escape each string before you do the implode().

Answer 4

If it were my code I'd write it like this:

$solr_id_arr_imploded = "'" . implode("', '", $solr_id_arr) . "'";
$query = "SELECT * FROM my_table WHERE ad_id IN ($solr_id_arr_imploded)";
$qry_result = mysql_query($query) or die(mysql_error());

...just because it keeps all the quoting work in one place. You might also want to make sure that the array isn't empty before entering this block of code. Otherwise the SELECT will match all empty ad_id's, which probably isn't what you wanted. We're also assuming that the elements of the array don't include any quote characters (or user-provided strings that haven't been sanity-checked).

Answer 5

This one seems complicated but it's more safer and fastest one

function escaped($str)
{
   return mysql_escape_string($str);
}
$arrayOfIds = array_map("escaped", $solr_id_arr);

$solr_id_arr_imploded = implode(", ", $arrayOfIds);

$query = "SELECT * FROM my_table WHERE ad_id IN ('$solr_id_arr_imploded')";
$qry_result = mysql_query($query) or die(mysql_error());