CODEWITHSUNDEEP
phpmysqlsql
Mr Coder
Mr Coder

Reputation: 8196

How to manage empty IN sql query?

$ids = array(1,2,3);
$in = implode(',',$ids);

$query = "SELECT * FROM user where user_id IN ($in) ";

Query works no problem. But when $ids is empty array $ids = array();

I got sql query error rightly so because SELECT * FROM user where user_id IN () is not a valid query .

How can I avoid such situation without checking for empty array i.e making query run no matter what ?

Upvotes: 10

Views: 24139

Answers (8)

biberesser
biberesser

Reputation: 39

I usually always add an "invalid" value, so the list is never empty:

$in = implode(',', array_merge($ids, [-1])); // never empty!

No additional conditional statements are required, keeping the code relatively clean. The "invalid" value must be outside the range of allowed values, of course. (The example above assumes that valid $ids are always positive.)

Upvotes: 3

Michael Blood
Michael Blood

Reputation: 1267

I do several different types of checking in different situations to make sure that the sql statement is valid. In the most basic type of checking, I make sure that the variable is not empty or false

$in = implode(',',$ids);
if(!$ids) $in="0";
$query = "SELECT * FROM user WHERE user_id IN ($ids)";

Typically, I use a whole series of db_* functions which validate the input I pass into queries so that I can handle more advanced uses check where the $ids array comes from

Here is some test code that works well for me in dozens of situations.

function db_number_list($lst)
{
    if(!$lst) 
        return "0"; // if there are no passed in values , return a 0 
    if (!is_array($lst)) 
        $lst = explode (",",$lst);  //if a lst was passed in,  create an array
    foreach ($lst as $k=>$val)
    {
        if(!is_numeric(trim($val)))
            unset($lst[$k]);//remove non-numeric values;
    }
    if(!count($lst)) 
        return "0"; //if nothing is in the array after removing non-numeric values,  return 0
    return implode (",",$lst);
} 

$ids=array();
$query = "SELECT * FROM user WHERE user_id IN (".db_number_list($ids).")";
echo "ids:'".print_r($ids,true)."'<br>$query<hr>";

$ids="1,2,45,6,";
$query = "SELECT * FROM user WHERE user_id IN (".db_number_list($ids).")";
echo "ids:'".print_r($ids,true)."'<br>$query<hr>";

$ids=array(3,6,1,"drop table x", 4);
$query = "SELECT * FROM user WHERE user_id IN (".db_number_list($ids).")";
echo "ids:'".print_r($ids,true)."'<br>$query<hr>";

Output:

ids:'Array ( ) '
SELECT * FROM user WHERE user_id IN (0)

ids:'1,2,45,6,'
SELECT * FROM user WHERE user_id IN (1,2,45,6)

ids:'Array ( [0] => 3 [1] => 6 [2] => 1 [3] => drop table x [4] => 4 ) '
SELECT * FROM user WHERE user_id IN (3,6,1,4)

Upvotes: -1

Faizan Ali
Faizan Ali

Reputation: 509

you should not run the query when the $in is empty. I think what yoou need is something like this 

$in = implode(',',$ids);
if($in) {
     $query = "SELECT * FROM user where user_id IN ($in) ";
 }
 else {
      //alternate scenario here
 }

Edit

$in = implode("','",$ids); // generate like 1','2
$query = "SELECT * FROM user where user_id IN ('$in') "; //  if has  1','2 surrond it with quote make it IN('1','2') and if empty than IN('')

Upvotes: 8

Falcon
Falcon

Reputation: 259

Best way to manage this is:

$in = implode("','",$ids); // generate like 1','2
$query = "SELECT * FROM user where user_id IN ('$in') "; //  if has  1','2 surrond it with quote make it IN('1','2') and if empty than IN('')

This saves you from if/else structure and everything else

Upvotes: 7

Ovais Khatri
Ovais Khatri

Reputation: 3211

if((count($in)>0)
{
  //execute sql query code
}

Upvotes: 0

Your Common Sense
Your Common Sense

Reputation: 157989

for the given query you should not run it at all. 

if ($ids) {
  // run your code
} else {
  return false; //or whatever.
}

for the other cases it could be conditional concatenation as shown in the other answers

Upvotes: -1

Nadir Sampaoli
Nadir Sampaoli

Reputation: 5555

You should set a conditional concatenation:

if (count($ids) > 0)
{
    $in = implode(',',$ids);
    $query = "SELECT * FROM user WHERE user_id IN ($in)";
    // ....
}

EDIT: fixed my misunderstanding.

Upvotes: -2

Rukmi Patel
Rukmi Patel

Reputation: 2561

you can add 0 ahead of $ids string. After all No ids will start from 0 in any database.

Or you can do like this..

$sql = "SELECT * FROM user where ".($ids != ''? "user_id IN ($ids)" : " 1=1");

Upvotes: 5

Related Questions