Comparing contents of an array against sql contents

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
pinehead18
Forum Contributor
Posts: 329
Joined: Thu Jul 31, 2003 9:20 pm

Comparing contents of an array against sql contents

Post 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?
User avatar
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

Post 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;
 
pinehead18
Forum Contributor
Posts: 329
Joined: Thu Jul 31, 2003 9:20 pm

Re: Comparing contents of an array against sql contents

Post 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,
pinehead18
Forum Contributor
Posts: 329
Joined: Thu Jul 31, 2003 9:20 pm

Re: Comparing contents of an array against sql contents

Post by pinehead18 »

if (($key + 1) != count($arr_ids)) {

I changed != to <= and it works perfectly,

Thanks.
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: Comparing contents of an array against sql contents

Post 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.
Post Reply