Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?

Single mysql query

by Anonymous Monk
on May 31, 2008 at 05:25 UTC ( #689388=perlquestion: print w/replies, xml ) Need Help??
Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

I'm not completely familiar with mysql myself but a SOPW that was just posted about this database sparked a question.

SELECT * FROM images WHERE id > ? LIMIT 1
The above is a piece of code that was given. Now the OP of that post used while($sth->fetch) on a binded column. Most of the samples I've seen/used have been this way.

How would you pull back just one chunk of data? Instead of looping like they do it?

Replies are listed 'Best First'.
Re: Single mysql query
by ikegami (Pope) on May 31, 2008 at 06:02 UTC

    You don't have to fetch all the rows.

    $sth->execute($id); my $row = $sth->fetch(); $sth->finish(); if ($row) { ... } else { # No more images ... }

    The selectrow_* methods are convenient shortcuts when you expect at most one row. Any extra rows returned by the query are ignored.

    my $row = selectrow_arrayref($stmt_or_sth, undef, $id); if ($row) { ... } else { # No more images ... }

    Note: I assumed RaiseError was true.

Re: Single mysql query
by pc88mxer (Vicar) on May 31, 2008 at 06:09 UTC
    The previous question seems to be this one: Calculating previous/next from database

    Calling fetch only once is fine if you only need the first row. Whenever you are done with the statement handle, just call finish:

    my ($id, $title); my $sth = $dbh->prepare("SELECT id, title FROM images WHERE id > ? LIM +IT 1"); $sth->bind_columns(\$id, \$title); $sth->execute($some_value); $sth->fetch; $sth->finish;
    However, I would suggest not binding columns and, instead, use fetchrow_hashref:
    my $sth = $dbh->prepare(...); $sth->execute(...); my $row = $sth->fetchrow_hashref; unless ($row) { # no rows returned } else { # process $row } $sth->finish;
    This approach also makes it easier to determine if the query returned no rows - a case that your code should be written to handle.
Re: Single mysql query
by jaldhar (Vicar) on May 31, 2008 at 06:08 UTC

    DBI has a number of functions that let you bypass having to code a loop. (Behind the scenes they are looping so these are just shortcuts really.)

    if you know you're only going to get one row back, selectrow_array (or _arrayref or _hashref) does the prepare, execute, and fetch all in one go for you.

    If you want an arrayref of arrayrefs or hashrefs (each representing one row,) the selectall_arrayref or selectall_hashref functions combine prepare, execute, and fetch into one function for you.

    If you only want certain columns from each row returned as an array of arrayrefs (each representing the selected columns in one row, selectcol_arrayref does the prepare, execute, and fetch in one go just like the other functions.


Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://689388]
Approved by pc88mxer
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others about the Monastery: (4)
As of 2018-05-23 19:20 GMT
Find Nodes?
    Voting Booth?