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?
Comparing contents of an array against sql contents
Moderator: General Moderators
-
pinehead18
- Forum Contributor
- Posts: 329
- Joined: Thu Jul 31, 2003 9:20 pm
- markusn00b
- Forum Contributor
- Posts: 298
- Joined: Sat Oct 20, 2007 2:16 pm
- Location: York, England
Re: Comparing contents of an array against sql contents
An SQL statement with multiple WHERE (OR) clauses looks like: SELECT * FROM `tbl_name` WHERE `a` = 1 OR `a` = 2 OR `a` = 3;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?
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;
-
pinehead18
- Forum Contributor
- Posts: 329
- Joined: Thu Jul 31, 2003 9:20 pm
Re: Comparing contents of an array against sql contents
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,
Thanks,
-
pinehead18
- Forum Contributor
- Posts: 329
- Joined: Thu Jul 31, 2003 9:20 pm
Re: Comparing contents of an array against sql contents
if (($key + 1) != count($arr_ids)) {
I changed != to <= and it works perfectly,
Thanks.
I changed != to <= and it works perfectly,
Thanks.
Re: Comparing contents of an array against sql contents
Here are some simpler alternatives that use implode().
Edit: This post was recovered from search engine cache.
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)