How to correctly parse an array for an SQL query

Question

When you need to do something like this:

SELECT * FROM userinfo WHERE id in (18,2,6,4,5)

And the id array comes from another query like:

$ids = $conn->fetchAll('SELECT origin from action WHERE url = "'.$url.'" AND SUBSTRING(origin,1,3)<>"pct" GROUP BY origin');

If I need to parse the array in order to give the right format to the query id do:

    $norm_ids = '(';
    foreach ($ids as $ids) {
        $norm_ids .= $ids['origin'] .',';
    }
    $norm_ids = substr_replace($norm_ids ,"",-1) .')';

That outputs the ids like: (id1,id2,id3,id.......), so the I'll just: FROM userinfo WHERE id in ". $norm_ids;

But seems to ugly to me, is there a way to do this better?

nice ass · Accepted Answer · 2013-01-26 11:07:04Z

3

You could do:

$idStr = rtrim(str_repeat('?,', count($ids), ',');
$query = 'SELECT * FROM userinfo WHERE id in (' . $idStr . ')';

and then use prepare():

$conn = $db->prepare($query);
$conn->execute($ids);
$res = $conn->fetchAll(...);

answered Jan 26, 2013 at 11:07

nice ass

16.8k8 gold badges54 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Alexander Nenkov · Accepted Answer · 2013-01-26 11:07:23Z

1

SELECT * FROM user_info WHERE id IN (SELECT origin from action ......) ....

answered Jan 26, 2013 at 11:07

Alexander Nenkov

2,91022 silver badges29 bronze badges

Comments

Doon · Accepted Answer · 2013-01-26 11:09:16Z

1

Do you need the id's separate or can you combine them into 1 query?

perhaps something like:

SELECT * FROM userinfo WHERE id in (SELECT origin from action WHERE url = "'.$url.'" AND SUBSTRING(origin,1,3)<>"pct" GROUP BY origin');

this way you let the sql server do the work.

answered Jan 26, 2013 at 11:09

Doon

20.3k3 gold badges42 silver badges44 bronze badges

Comments

Jaspal Singh · Accepted Answer · 2013-01-26 11:48:54Z

1

When i am faced with such situations, i use trim

$norm_ids_str = '';
foreach ($ids as $ids) {
    $norm_ids_str .= $ids['origin'] .',';
}
$norm_ids = '(' . trim($norm_ids_str, ',') . ')';

answered Jan 26, 2013 at 11:48

Jaspal Singh

1,2401 gold badge12 silver badges17 bronze badges

Collectives™ on Stack Overflow

How to correctly parse an array for an SQL query

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related