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

How can I tell if I pulled ONE, JUST ONE result from MySQL, once the statment done. 
 $sth = $dbh->prepare("SELECT * FROM poll WHERE status='waiting' ORDER
+ BY `id` ASC LIMIT 0, 5");
 $sth->execute or die $dbh->errstr;

[download]

Replies are listed 'Best First'.
Re: I need to tell what I pulled from SQL
by ichimunki (Priest) on Jul 22, 2002 at 16:49 UTC
    You could use the DBI method fetchall_arrayref() and then count elements. There is also a rows() method, but I hear this is not reliable. perldoc DBI is a good place for more info about these methods.

    But depending on *why* you need to know how many rows are returned, the best solution may not be in Perl or DBI but in your database. You should either consider using SQL statements like "select count(*) from poll where ..." and checking that the count is one, or consider building your table keys (using uniqueness) so that the criteria of this sort of SQL statement can return only one row.

[reply]
[d/l]

      I was just going to jump in and suggest $sth->rows, but now your comment has given me pause. Can you say more about this method's "unreliability"?

      Thanks,

      BCE
      --Your punctuation skills are insufficient!

[reply]
[d/l]
        The problems with this method are documented in 'perldoc DBI'. It is probably more reliable than I would be, but to sum it up, $sth->rows is mostly for do() commands like INSERT, DELETE, etc, so that you know how many rows were affected (as those queries don't really have any other results). I also suspect that DBI functionality, although it is supposed to provide a uniform interface, can vary somewhat based on which type of server is used. There might be a MySQL-specific answer to this question, but it probably wouldn't be very portable-- besides, doing COUNT(*) or simply fetching all the data (since, in this case, only a single row seems to be expected), is just going to be quicker than any other solution.
[reply]
Re: I need to tell what I pulled from SQL
by ehdonhon (Curate) on Jul 22, 2002 at 17:11 UTC
[reply]
Re: I need to tell what I pulled from SQL
by dug (Chaplain) on Jul 22, 2002 at 17:30 UTC
    The execute() method called from your statement handle returns the number of affected rows, as does $sth->{'mysql_affected_rows'}.

    *NOTE* I'm think this is mysql specific for selects

    You probably want something like: 
    my $sth = $dbh->prepare(
  "SELECT * FROM poll 
   WHERE status='waiting' 
   ORDER BY `id` ASC 
   LIMIT 0, 5"
  );
my $query_count = $sth->execute or die $dbh->errstr;
if ( $query_count == 1 ) {
  # you *can* pull one but just one row using one of the 
  # fetch* methods
} else {
  # your condition isn't met
}

    [download]
[reply]
[d/l]
[select]
Re: I need to tell what I pulled from SQL
by Abigail-II (Bishop) on Jul 22, 2002 at 16:56 UTC
    In the code you give, you didn't pull anything from the database yet, so the question doesn't make much sense.

    It's only after fetches that you get something - one result at the time. (Unless you use some of the wrappers from DBI).

    Abigail

[reply]

Back to Seekers of Perl Wisdom