PHP While Loops from Arrays

Question

I have a table that contains members names and a field with multiple ID numbers. I want to create a query that returns results where any values in the ID fields overlap with any values in the array.

For example: lastname: Smith firstname: John id: 101, 103

I have Array #1 with the values 101, 102, 103

I want the query to output all members who have the values 101 or 102 or 103 listed in their id field with multiple ids listed.

Array (  [0] => 101
         [1] => 102 
         [2] => 103 )

$sql="SELECT firstname, lastname, id
      FROM members WHERE id LIKE '%".$array_num_1."%'";
$result=mysql_query($sql);

while ($rows=mysql_fetch_array($result)) {
       echo $rows['lastname'].', '.$rows['firstname'].'-'.$rows['id'];
      }

I tried to simplify it. The IDs are actually stored in another table, and I have generated the array from this table.

$array_num_1 = array();
$sql_id="SELECT id FROM id_table WHERE id < 200";
$result_id=mysql_query($sql_id);
while($rows_id=mysql_fetch_array($result_id)){;
      $array_num_1[] = $rows_id['id'];
}

Answer 1

If you must do it this way:

$sql = "SELECT firstname, lastname, id FROM members WHER id IN (" . implode(', ', $array) . ")"

As @cletus says in his comment, though, the database is made for this sort of thing. Hard coding it in an array seems like the suboptimal way of doing things.

Answer 2

You're probably looking for IN:

SELECT firstname, lastname, id FROM members WHERE id IN (1, 2, 3);

Of course, you could create (1, 2, 3) dynamically, e.g. using implode.

---

Whoops, I just realized that I got your db schema wrong. As others have advised, you should normalize it.

Answer 3

Normalize your database. Make these ids into separate table.

Edit: And then use answers below

Answer 4

SELECT firstname, lastname, m.id 
FROM members m, id_table i ON m.id=id_name 
WHERE i.id < 200