select rows where id is a part of a variable

3166 views php
-1

I want to select rows where id is a part of a variable:

$rx = '1-2-3-4';

$st = $db->query("select id, img from abc where id in ('" . $rx . "') order by date desc");

echo $st->rowCount();  // 1

I'm expecting 4 because there are rows where id is 2 or 3 or 4.

answered question

The IN clause uses a comma separated list like IN (1, 2, 3, 4) . That said, you should make use of prepared statements. Take a look at mysqli or pdo.

Where is the source of the data? User input? Generally untrustworthy source?

@dnFer, do I need quotes ('1', '2'...) using prepared statement?

3 Answers

10

Standard syntax for searching in a string is comma separated, so you can replace dash with comma:

$rx = '1-2-3-4';
$rxc= str_replace("-",",",$rx);

$st = $db->query("select id, img from abc where id in ('" . $rxc . "') order by date desc");

echo $st->rowCount();

posted this
6

You can do it by many different way's but I've two ways in my mind. Hope this helps :)

1. Try like this way by explode by dash - and implode by comma ,

$rx = '1-2-3-4';
$st = $db->query("select id, img from abc where id in ('" . implode(',',explode('-',$rx)) . "') order by date desc");
echo $st->rowCount();  // 1

OR

2. Replace dash - with comma , using str_replace('-',',',$rx);

$rx = '1-2-3-4';
$st = $db->query("select id, img from abc where id in ('" . str_replace('-',',',$rx) . "') order by date desc");
echo $st->rowCount();  // 1

posted this
10

You can easily do it by replacing - with ',', note the extra quotes are needed...

$st = $db->query("select id, img 
         from abc 
         where id in ('" . str_replace("-", "','", $rx) . "') 
         order by date desc");

This generates...

select id, img from abc where id in ('1','2','3','4') order by date desc

posted this

Have an answer?

JD

Please login first before posting an answer.