Re: Using DBI, better option than importing into @array

On Tue, 31 Jul 2007 01:44:42 -0000 Jason <jwcarlton@xxxxxxxxx> wrote:

J> On Jul 30, 8:00 pm, Ted Zlatanov <t...@xxxxxxxxxxxx> wrote:
On Mon, 30 Jul 2007 21:30:36 -0000 Jason <jwcarl...@xxxxxxxxx> wrote:

J> # Push subjects into Perl array
J> my $filelist = $dbh->selectall_arrayref("SELECT `id`, `lastmodified`,
J> `subject` FROM $forum_subjects ORDER BY lastmodified DESC");
...
J> # In the "view subject" section, loop through last 20 indexes of
J> @filenames
J> # 0 and 20 are dynamic in the real script
J> for ($count=0; $count < 20; $count++) {
J> ($id, $lastmodified, $subject) = split(/\|:\|/, $filenames[$count]);

J> my $topiclist = $dbh->selectall_arrayref("SELECT `id`, `subject`,
J> `postdate`, `username`, `email`, `comment` FROM $forum_posts WHERE
J> id=" . $dbh->quote($id) . " ORDER BY postdate ASC");

J> print ...
J> }

J> I know that this has got to be the most inefficient method possible,
J> but I haven't found a better way. Is there a faster method to get the
J> information I want than this?

First of all, you may want to use sprintf() to make your SQL strings
clearer. Also, don't use $dbh->quote() only sometimes, use it all the
time.

Second, learn SQL better and you'll be able to write the exact query
that will return the last 20 items with the detail you need. It's not a
Perl problem that your approach is slow. I won't write the query for
you, because you should look it up, and it may make sense for you to use
the MySQL-specific version as opposed to the standard SQL version.

Last but most important, use Rose::DB::Object or Class::DBI (both on
CPAN) to automate your database work. You'll spend 2 hours learning how
to set up a Rose::DB connection, then you'll save weeks of your time
using all the functionality that RDBO provides for you. I like
Class::DBI too, but RDBO is IMHO much better supported and designed, so
I reccomend it.

J> Thanks for the tip on Rose::DB::Object. The whole point of this
J> exercise is to learn MySQL, anyway (the system functions in flat text,
J> just not well), so that helps a lot.

If you do use RDBO, turn on the "Debug" flag for the objects and the
managers. You'll see the full SQL queries RDBO makes, which is a nice
learning tool. Once you've used RDBO, you'll wonder why you ever
suffered through the DBI interface.

Ted
.