[This is an email copy of a Usenet post to "mailing.database.mysql"]
How many authors are likely to be passed to this subroutine? As long as
it is some ``reasonable'' number, I suggest you construct a query using
IN(...).
If you had a *huge* number of authors to check, you might resort to a
JOIN on a temporary table, but I can't see that being faster. At some
point it might become the only thing that works though, if you had
several MB worth of ("author1", "author2", ... "author234234"). :-)
Anyway, here is how I would do this.
sub existAuthor {
return() unless ref($_[0]) eq 'ARRAY'; # may as well check
my $authorNames = $_[0];
return() unless @{$authorNames}; # make sure there aren't 0 elements
my $q = "SELECT COUNT(*) AS _count FROM authors WHERE name IN (" .
join(",", map { $DBH->quote($_) } @{$authorNames}) . ")";
my $sth = $DBH->prepare($q) or die "DBH->prepare($q): ".$DBH->errstr();
$sth->execute() or die "sth->execute(): ".$sth->errstr();
$q = $sth->fetchrow_hashref()->{_count}; # get the result
$sth->finish();
return ($q == @{$authorNames}); # return result of comparison
}
Note my use of map { $DBH->quote($_) } to quote the arguments to IN(...).
This, I believe, is the "proper way" to quote text using DBI. If the
driver provides that method, and mysql does, you can blame it if you have
quoting troubles. :-)
--
Jeff S Wheeler [EMAIL PROTECTED]
Software Development Five Elements, Inc
http://www.five-elements.com/~jsw/
database sql query table
In article <[EMAIL PROTECTED]>, "Per M
Knutsen" <[EMAIL PROTECTED]> wrote:
> Can anyone suggest how to convert the following Perl code into a single
> SELECT statement?
>
> sub existAuthor {
> my ($authorNames) = @_; # $authorNames is a reference to an
> array my ($author, $sqlQuery); # containing names of authors
> foreach $author (@$authorNames) {
> $sqlQuery = "SELECT * FROM authors WHERE name = '$author'";
> doQuery($sqlQuery) ? next : return 0;
> }
> return 1;
> }
>
> In words, I want to make sure that every element in the array
> @authorNames exists in the table authors. If not, the routine should
> return false.
>
> If my array is large, many SQL calls are made with the existing code. Is
> there a way to reduce this to a single SQL statement?
>
> Thanks!
>
> Per K
--
Jeff S Wheeler [EMAIL PROTECTED]
Software Development Five Elements, Inc
http://www.five-elements.com/~jsw/
database sql query table
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
