How do I filter a php array with a MySQL table?

Question

Say I have an array of strings in a php array called $foo with a few hundred entries, and I have a MySQL table 'people' that has a field named 'name' with a few thousand entries. What is an efficient way to find out which strings in $foo aren't a 'name' in an entry in 'people' without submitting a query for every string in $foo?

So I want to find out what strings in $foo have not already been entered in 'people.'

Note that it is clear that all of the data will have to be on one box at one point. The goal would be doing this at the same time minimizing the number of queries and the amount of php processing.

Answer 1

I'd put your $foo data in another table and do a LEFT OUTER JOIN with your names table. Otherwise, there aren't a lot of great ways to do this that don't involve iteration at some point.

Answer 2

I'm not sure there is a more efficient way to do this other than to submit all the strings to the database.

Basically there are two options: get a list of all the strings in MySQL and pull them into PHP and do the comparisons, or send the list of all the strings to the MySQL server and let it do the comparisons. MySQL is going to do the comparisons much faster than PHP, unless the list in the database is a great deal smaller than the list in PHP.

You can either create a temporary table, but either way your pushing all the data to the database.

Answer 3

For a few hundred entries, just use array_diff() or array_diff_assoc()

Answer 4

The best I can come up with without using a temporary table is:

 $list = join(",", $foo);

// fetch all rows of the result of 
// "SELECT name FROM people WHERE name IN($list)" 
// into an array $result

$missing_names = array_diff($foo, $result);

Note that if $foo contains user input it would have to be escaped first.

Answer 5

$query = 'SELECT name FROM table WHERE name != '.implode(' OR name != '. $foo);

Yeash, that doesn't look like it would scale well at all.

Answer 6

CREATE TEMPORARY TABLE PhpArray (name varchar(50));

-- you can probably do this more efficiently
INSERT INTO PhpArray VALUES ($foo[0]), ($foo[1]), ...;

SELECT People.*
FROM People
 LEFT OUTER JOIN PhpArray USING (name)
WHERE PhpArray.name IS NULL;

Answer 7

What about the following:

1. Get the list of names that are already in the db, using something like: SELECT name FROM people WHERE name IN (imploded list of names)
2. Insert each item from the return of array_diff()

If you want to do it completely in SQL:

1. Create a temp table with every name in the PHP array.
2. Perform a query to populate a second temp table that will only include the new names.
3. Do an INSERT ... SELECT from the second temp table into the people table.

Neither will be terribly fast, although the second option might be slightly faster.