It's Jon the newbie here. I'm trying to teach myself all of the
stuff I need to set up a basic weblog with Perl and MySQL, and in my
self education, I'm starting to question my concept of how DBI::MySQL
Basically, I'm not sure how to use the DBI methods to retrieve
records out of a selected query dataset. I'm sitting here with Perl
in a Nutshell's database chapter, section 20.5 of the MySQL manual
("MySQL Perl API"), and Jochen's summary at the Perl/DBI site in
front of me. And, I've got fetch, fetchall_arrayref, and the other
fetch functions taunting me with chants of "newbie, newbie, newbie".
I understand how to prepare an SQL query and I understand that once
you've prepared the statement, you can go ahead and execute it.
(Jochen, your list of supported functions answers a lot of my
questions on that end. Very well written, thank you.)
But, I think I'm projecting Perl like hash functionality onto the
fetch functions (because I read the chapters about DBM hashes before
I read the DBI sections). I just want to make sure I'm understanding
Okay, the way I read it with DBI, you're supposed to do all of your
selection, sorting, and "kitchen sink" criteria in your prepared SQL
statement. Then, "fetch" merely gives you a row by row method of
returning that data?
But, what if you want to move and maneuver in a returned record set,
is there any way to do this without a new query or dumping it all
into a hash?
For instance, say I have a basic two table relational structure where
StoryID is the key...
Story (StoryID, StoryBlob, StoryAuthorID)
Comment (CommentID, StoryID, CommentBlob, CommentAuthorID)
And, say I want to pull up all of the Comments matching a certain
StoryID. Simple enough SQL statement. Okay, now, if I want to
display the first 10 results (or 21 to 30), I see that MySQL supports
a "limit" statement.
But, say ,theoretically, I want to pull 3 random rows from that
query? Is there any way for me to use the rows in any order I want?
I mean, I can grok that I could put all 10 rows into a hash and then
use perl to manipulate it, but is there any way to tell perl to pick
row 5, 2 and 8 (in that order) from an executed query of 10 rows? Or
does fetch simply offer me a row by row method of going through the
dataset where each time I get a new row, fetch is primed for row+1?
In other words, are there ways to execute new queries criteria on top
of your current query or to use Perl like hash manipulations? Or do
you simply need to prepare a new statement and execute the new
statement that includes the current one? If I want to pull a
specific Comment out by CommentID, would I just go ahead and do a new
query, or is there some way to isolate just that one. Or, say I
wanted to select only certain Comment Author ID's from my current
query... again, can I use Perl hash functions, or is the proper
method to just do a new query? I assume I can dump all of a query
into a hash, but that this would have serious memory/performance
implications if I get into the hundreds of records.
Also, if I am just using a fetch method and I get to the end of the
query, does the next fetch command cause an error or does it restart
back to the first row returned? Do you just execute a new query at
Sorry if some of these questions are non-sensical or obvious. I am a
newbie, so please be gentle. I have a sneaking suspicion that they
are, but like I said, the DBM docs show hash's, but the DBI docs just
show "fetchs", and I want to make sure I'm not missing something.