Dynamic where statement efectively

Question

So I was wondering what the most efficient way to search a database using WHERE.

If I have var such as

$conditions = "1,3,4,9"

With each of these ints referencing a primary id of a table. So what is the best way to select these rows from mysql.

for($i = 0; $i < strlen($conditions); $i++)
{
  if($conditions[$i] == ',')
   continue;
  $conn->prepare('select * FROM table WHERE id= $i');
}

I was thinking about doing something above however, I am sure there would be more efficient ways then this.

Ps. I know the code above won't work and i wrong it was just an example I wrote up then.

Thanks for the advice.

Right, if you have array than use IN else use find_in_set for comma separated string — Devsi Odedra, May 09 '18 at 12:11
Explode the `$conditions` then build an `in` group. Proper way with prepared statement: https://3v4l.org/06uiU — chris85, May 09 '18 at 12:13

score 0 · Answer 1 · answered May 09 '18 at 12:12

0

The function you're looking for is find_in_set:

select * from ... where find_in_set($conditions, id)

answered May 09 '18 at 12:12

Robert

score 0 · Answer 2 · answered May 09 '18 at 12:13

0

Convert the string into array:

$conditions = explode(",","1,3,4,9");
foreach($conditions as $id){
  $conn->prepare('select * FROM table WHERE id= $id');
}

answered May 09 '18 at 12:13

Omar Abu Omar

2 Answers2