Page 1 of 1
Comparing contents of an array against sql contents
Posted: Sat Oct 31, 2009 3:17 pm
by pinehead18
I'm rather new when it comes to using an array. However, with my array I have 20 user id's. I want to pull a table all of the rows that have any one of the 20 user id's in the array.
How should i go about creating the statement for it to work. Do i need a for each to run through each user id in the array?
Re: Comparing contents of an array against sql contents
Posted: Sat Oct 31, 2009 3:25 pm
by markusn00b
pinehead18 wrote:I'm rather new when it comes to using an array. However, with my array I have 20 user id's. I want to pull a table all of the rows that have any one of the 20 user id's in the array.
How should i go about creating the statement for it to work. Do i need a for each to run through each user id in the array?
An SQL statement with multiple WHERE (OR) clauses looks like: SELECT * FROM `tbl_name` WHERE `a` = 1 OR `a` = 2 OR `a` = 3;
To build that statement from PHP, you could do something like:
Code: Select all
<?php
$arr_ids = array(1, 2, 3, 4, 5);
$stmt = "SELECT * FROM `tbl_name` WHERE";
foreach ($arr_ids as $key => $id) {
$stmt .= " `a` = {$id}";
/** Only stick an OR on if we are not at the end of the array */
if (($key + 1) != count($arr_ids)) {
$stmt .= " OR";
}
}
print $stmt;
Re: Comparing contents of an array against sql contents
Posted: Sun Nov 01, 2009 6:21 pm
by pinehead18
The first part of this works great. However, I can't seem to make the if statement work. I still receive the last or even though their is nothing after it. Causing an error in the sql. Any ideas how to make that not be their? I've tried for about an hour to play with it but have not been successful.
Thanks,
Re: Comparing contents of an array against sql contents
Posted: Mon Nov 02, 2009 11:46 am
by pinehead18
if (($key + 1) != count($arr_ids)) {
I changed != to <= and it works perfectly,
Thanks.
Re: Comparing contents of an array against sql contents
Posted: Mon Nov 02, 2009 4:10 pm
by McInfo
Here are some simpler alternatives that use implode().
Code: Select all
$ids = array(2, 4, 8);
$query = 'SELECT * FROM `user` WHERE `id` = '.implode($ids, ' OR `id` = ');
// SELECT * FROM `user` WHERE `id` = 2 OR `id` = 4 OR `id` = 8
$query = 'SELECT * FROM `user` WHERE `id` IN ('.implode($ids, ', ').')';
// SELECT * FROM `user` WHERE `id` IN (2, 4, 8)
Edit: This post was recovered from search engine cache.