Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl Monk, Perl Meditation
 
PerlMonks  

Perl Mysql Null Recordset

by JoeJaz (Monk)
on Nov 16, 2004 at 21:34 UTC ( [id://408256]=perlquestion: print w/replies, xml ) Need Help??

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

Hi, Forgive me if this is a stupid question, but google doesn't seem to be much help for it. I am using DBI with MySQL and am looking for a way to test if a query returned an empty recordset. For example, I issue the query:
$rs1 = $db1->prepare("SELECT * FROM users WHERE username = '" . $Usern +ame_POST . "';");
Assume that I have the database connection set up correctly. I have tried a statement such as:
$rs1->execute; if($rs1 != 'NULL') { report_error("username_exists"); } else { report_error("username does not exist"); }
I have also tried placing the $rs1->execute; statement after the if statement, but that changed nothing. The if statement always evaluates to true. Will anyone take pitty on my with this question? I would really appreciate any advice. Thanks for reading my problem. Joe

Replies are listed 'Best First'.
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];

      I have never seen that syntax before. It is very sisinct; I like it. Thanks for providing your code. Joe
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 ...
      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
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 }
      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
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
      That seems simple enough. I'll give that a try. Thanks for the input. Joe
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.
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.


    radiantmatrix
    require General::Disclaimer;
    Perl is

      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."

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://408256]
Approved by rozallin
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others learning in the Monastery: (4)
As of 2024-03-28 15:30 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found