confused about reading from database using an array

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Say you have an array of @names and you have these names as columns inside your database.

Without looping over the array and connecting to the database infinitely many times, how can I do it all in one run?

@names = qq(aaron richard robert penny);

my $data = qq(SELECT id, order_num, state FROM orders WHERE names=@nam
+es);
...
[download]

This is for a MySQL connection.

Comment on confused about reading from database using an array Download Code

Replies are listed 'Best First'.
Re: confused about reading from database using an array by Joost (Canon) on Dec 12, 2006 at 20:46 UTC
you can use the SQL "IN" statement: `SELECT id, order_num, state FROM orders WHERE names IN ("name1","name2 +","name3")` [download] just make sure you use the `$dbh->quote` method to construct the quoted values. "What should it profit a man, if he should win a flame war, yet lose his cool?"	[reply] [d/l] [select]
Re^2: confused about reading from database using an array by Anonymous Monk on Dec 12, 2006 at 20:49 UTC
Are you saying I can replace the list with the array? `SELECT id, order_num, state FROM orders WHERE names IN (@names)` [download]	[reply] [d/l]
Re^3: confused about reading from database using an array by Joost (Canon) on Dec 12, 2006 at 20:54 UTC
No, because you need to put commas and quotes there: `my $sql = "SELECT id, order_num, state FROM orders WHERE names IN (".j +oin(",",map($dbh->quote($_), @names)).")";` [download] Where $dbh is your DBI database handle. See also map , join and DBI's quote() method. "What should it profit a man, if he should win a flame war, yet lose his cool?"	[reply] [d/l]
Re: confused about reading from database using an array by sgifford (Prior) on Dec 12, 2006 at 22:30 UTC
For an extremely large list of names (hundreds), you could also create a temporary table to `JOIN` against. There's often a length limit for SQL queries, and creating a giant `IN` list would bump up against that. If you've just got a few (around 100 or less), using `IN` or `OR` should work fine. -- sgifford's Web page	[reply] [d/l] [select]