Re: Perl Mysql Null Recordset
by dws (Chancellor) on Nov 16, 2004 at 21:51 UTC
|
I am using DBI with MySQL and am looking for a way to test if a query returned an empty recordset.
You might have better luck by rephrasing the question as "how many users with this name exist?"
Try something like this (untested):
my $sth = $db1->prepare(
"SELECT count(*) FROM users WHERE username = ?"
);
$sth->execute($Username_POST);
my $rs = $sth->fetchall_arrayref();
my $count = $rs->[0]->[0];
| [reply] [Watch: Dir/Any] [d/l] |
|
I have never seen that syntax before. It is very sisinct; I like it. Thanks for providing your code. Joe
| [reply] [Watch: Dir/Any] |
|
| [reply] [Watch: Dir/Any] |
Re: Perl Mysql Null Recordset
by ikegami (Patriarch) on Nov 16, 2004 at 21:58 UTC
|
dws's solution works great if you only need to check if a user exists or not (although I'd use fetch() and $rs->[0] instead of fetchall_arrayref() and $rs->[0]->[0]). If you want to check if the user exists or not, and do something with the record if he does, try the following:
$rs1 = $db1->prepare("SELECT ...");
$rs1->execute(...);
unless ($row = $rs1->fetch())) {
die("User does not exist\n");
}
... check supplied password against the one in $row ...
| [reply] [Watch: Dir/Any] [d/l] [select] |
|
Very nice way of putting it. That seems like it would give you more control over the variables inside the $rs1 if a user did exist. Thanks for the post. Joe
| [reply] [Watch: Dir/Any] |
Re: Perl Mysql Null Recordset
by jZed (Prior) on Nov 16, 2004 at 22:10 UTC
|
If you search for a single column and your query is guaranteed to return at most one row, you can use selectrow_array to do everything at once:
my $sql = "SELECT username FROM users WHERE username = ?";
my $user = $dbh->selectrow_array( $sql,{},$Username_POST );
if( $user ) { die "User '$user' exists; }
else { ... # user doesn't exist }
Note: I dropped the semicolon at the end of your SQL - you don't want that there. I also used placeholders, which is a better way to go in general.
Update
Or, if you want the whole row:
my $sql = "SELECT username FROM users WHERE username = ?";
my @row = $dbh->selectrow_array( $sql,{},$Username_POST );
if( @row ) { die "User exists"; }
else { ... # user doesn't exist }
| [reply] [Watch: Dir/Any] [d/l] [select] |
|
Wow, that's an interesting use of the selectrow_array function. I didn't know that you could have it just return one variable. Very neat. Thanks, Joe
| [reply] [Watch: Dir/Any] |
Re: Perl Mysql Null Recordset
by TrekNoid (Pilgrim) on Nov 16, 2004 at 21:49 UTC
|
I use Oracle, but I gather this should still work for MySQL...
$rows = $rs1->rows;
if (!$rows) {
report_error("username does not exist");
} else {
report_error("username_exists");
}
Trek | [reply] [Watch: Dir/Any] [d/l] |
|
That seems simple enough. I'll give that a try. Thanks for the input.
Joe
| [reply] [Watch: Dir/Any] |
Re: Perl Mysql Null Recordset
by ChrisR (Hermit) on Nov 17, 2004 at 02:33 UTC
|
The execute statement returns 0E0 if no records were affected/returned. Instead of :
if($rs1 != 'NULL')
Try:
if($rs1 ne "0E0")
Also, I'm pretty sure the != test is for numeric values only.
From the MySQL Reference Manual:
If no rows are affected, execute returns "0E0" , which Perl treats as zero but regards as true. If an error occurs, execute returns undef. | [reply] [Watch: Dir/Any] [d/l] [select] |
Re: Perl Mysql Null Recordset
by radiantmatrix (Parson) on Nov 17, 2004 at 21:45 UTC
|
The DBI execute returns the number of rows affected. In the case of a select, it returns the number of rows found. Argh! My bad... jZed caught my mistake. So, something like:
$rs1->execute;
if (@{$rs1->fetchrow_array}) {
report_error("username_exists");
} else {
report_error("username does not exist");
}
You could also have fun with the ternary operator:
$rs1->execute;
report_error( (@{$rs1->fetchrow_array) ? "username_exists" : "username
+ does not exist" );
The ternary operator works by checking the truth of the first part (before the ?) and returning the second part if it is true, else returning the third part.
| [reply] [Watch: Dir/Any] [d/l] [select] |
|
No, execute only returns the rows affected with *NON-SELECT* statement handles. From the DBI docs:
"For SELECT statements, execute simply "starts" the query within the database engine. Use one of the fetch methods to retrieve the data after calling execute. The execute method does not return the number of rows that will be returned by the query (because most databases can't tell in advance), it simply returns a true value."
| [reply] [Watch: Dir/Any] |