using a string of values in WHERE clause - mysql

Question

I have a string of values like :

$cat = "1,5,6,8,9";

now I would like to use that variable in WHERE clause. how do I do it ?

"select category from test where category...?"

I am using below code but I get null from number_of_result variable :

$array_of_cat = explode(",",$cat);

if($number_of_result == 0){
    $mresult = mysql_query("select * from books where
     GoodName like '%$title%' and GroupCode in ($array_of_cat) ");
    $number_of_result = mysql_num_rows($mresult);
}

Why are you checking `$number_of_result` before the query? `$number_of_result` isn't set yet. — Telford Computer-DoctorLtd, Aug 30 '14 at 07:24
Here is [the fundamental problem](http://ideone.com/KCrVWZ) reproduced. — user2864740, Aug 30 '14 at 07:38

score 1 · Answer 1 · edited May 23 '17 at 12:05

1

You do not need to explode the variable - doing so results in an array that will result in the wrong value (the string "Array") when interpolated into the SQL string.

Despite the fact that this is unsafe (SQL injection prone) you could do:

if($number_of_result == 0){
    $mresult = mysql_query("select * from books where
     GoodName like '%$title%' and GroupCode in ($cat)");
    $number_of_result = mysql_num_rows($mresult);
}

I strongly suggest you use the mysql_real_escape_string function to prepare your variables.

edited May 23 '17 at 12:05

Community

1
1

answered Aug 30 '14 at 07:32

sfitsos

5
2

It is time to update suggestions. `mysql_` is *deprecated* and should *not* be recommend at all. – user2864740 Aug 30 '14 at 07:35
I totally agree with you. Half the apps that use `mysql` and `php` use them though... – sfitsos Aug 30 '14 at 08:24

score 1 · Accepted Answer · answered Aug 30 '14 at 07:36

1

Change your SQL statement to this one. Use implode() to pass array elements in IN clause

$mresult = mysql_query("
  SELECT * FROM books 
  WHERE GoodName LIKE '%$title%' AND GroupCode IN (" . implode(",", $array_of_cat) . ")");

answered Aug 30 '14 at 07:36

hex494D49

9,109
3
38
47

Telford Computer-DoctorLtd · Answer 3 · 2014-08-30T07:34:12.683

0

$cat = array(1,5,6,8,9);

$array_of_cat = implode(",",$cat);

if($number_of_result > 0){
  $mresult = mysql_query("select * from books where
  GoodName like '%$title%' and GroupCode in ($array_of_cat) ");

$number_of_result = mysql_num_rows($mresult);
}

Also, I'd strongly suggest reading up on PDO/MySQLi

edited Aug 30 '14 at 07:34

answered Aug 30 '14 at 07:28

Telford Computer-DoctorLtd

164
9

using a string of values in WHERE clause - mysql

3 Answers3