Check if any items in an array (php) is in a table (mysql).

[rjames.clarke@gmail.com]

I want to check if any of the items in a PHP array exist in a MySQL
table.
What is the best way to do this with making repeated calls to the
database for each item?

Actual application is:
I have an array (in PHP) of serial numbers of units about to be
shipped.
I want to check the ship record (a table in a MySQL database) to check
if that serial number has been used before.
According to our quality policy we never reuse serial numbers, serial
numbers are unique. So prior to printing the packing slip I want to
warn the shipping guy/gal a serial number may be incorrect.

I could brute force it and increment through the "to be shipped array"
and check each item against the table. But that is, as I said brute
force, and I am having to be concerned with system speed lately.

Thanks
Bob

[David Haynes]

Re: Check if any items in an array (php) is in a table (mysql).

rjames.clarke@g mail.com wrote:[color=blue]
> I want to check if any of the items in a PHP array exist in a MySQL
> table.
> What is the best way to do this with making repeated calls to the
> database for each item?
>
> Actual application is:
> I have an array (in PHP) of serial numbers of units about to be
> shipped.
> I want to check the ship record (a table in a MySQL database) to check
> if that serial number has been used before.
> According to our quality policy we never reuse serial numbers, serial
> numbers are unique. So prior to printing the packing slip I want to
> warn the shipping guy/gal a serial number may be incorrect.
>
> I could brute force it and increment through the "to be shipped array"
> and check each item against the table. But that is, as I said brute
> force, and I am having to be concerned with system speed lately.
>
> Thanks
> Bob
>[/color]

My suggestion:

Load the shipped table as an associative array and then do an isset() on
the $array[$serial_number] in question. Only load those rows that have
the shipped status set the way you need.

-david-

[Sjoerd]

Re: Check if any items in an array (php) is in a table (mysql).


rjames.clarke@g mail.com wrote:[color=blue]
> I want to check if any of the items in a PHP array exist in a MySQL
> table.[/color]

$query = "SELECT COUNT(*) FROM my_table WHERE serialno IN (".implode(' ,
', $serials).")";