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

I'm sure this has come up before, however I think I'll ask again just in case. I'm trying to get my web page running like so many others do where I can just type my update into a form, it throws that information into a mysql database, then the front page pulls the last say... 3 entries from the database and displays them on the front page. I'm having trouble trying to figure out where to even start, so far I have this:

#!/usr/bin/perl -w

use CGI qw(:standard);
use DBI;
use strict;

my @list = '';

print header;
print start_html;

my $dbh = DBI->connect('DBI:mysql:test') or die "Trouble opening datab
+ase: $DBI::errstr; stopped";

my $sth = $dbh->prepare("SELECT name, data, ID FROM frog WHERE ID like
+ '%'") or die "Trouble preparing statement: $DBI::errstr; stopped";

$sth->execute() or die "Trouble executing statement: $DBI::errstr; sto
+pped";

while ( my ($blankfield, $data) = $sth->fetchrow_array() ) {
push @list, $data;
}

my $i = $#list;
while ($i >= ($#list - 2)) {
   print $list[$i--], "<BR>\n";
}

$dbh->disconnect();


print end_html;
[download]

It runs a lot slower than it should, and I'm not sure why. Also, if there are multiple columns in my database, I don't know how to associate the columns with each other. Right now I can only pull up data from one column (the script is getting data from the first 2 columns, ignoring the first, and only saving what's in the second. That's intentional because I had no other idea how to do this). Any ideas on this?

Comment on Download Code

Replies are listed 'Best First'.
Re: Database, DBI Slowness by btrott (Parson) on Nov 11, 2000 at 05:03 UTC
One reason it's so slow is that you're loading the entire database table into your program, when you only need 3 lines! You don't need to do that; since you're using MySQL, you can use the LIMIT directive: `select foo from bar limit 3` [download] What do you want that where clause to do? That matches any ID: so you don' t need it. Just eliminate the where clause entirely. And why are you selecting three columns if you only want one? Just say `select data from frog limit 3` [download] Also you should try using bind_columns and fetch, cause it's faster: `my($data); $sth->bind_columns(\$data); while ($sth->fetch) { print $data; }` [download] If you want to select multiple columns, try this: `my $sth = $dbh->prepare(<<SQL); select id, data from frog limit 3 SQL $sth->execute; my($id, $data); $sth->bind_columns(\$id, \$data); while ($sth->fetch) { print $id, "\t", $data, "\n"; }` [download] (By the way, I took out the error-checking partly for brevity, and partly cause I always use RaiseError). Check out Tricks with DBI.	[reply] [d/l] [select]

Replies are listed 'Best First'.

Re: Database, DBI Slowness
by btrott (Parson) on Nov 11, 2000 at 05:03 UTC

    select foo from bar limit 3
[download]

And why are you selecting three columns if you only want one? Just say

    select data from frog limit 3
[download]

    my($data);
    $sth->bind_columns(\$data);
    while ($sth->fetch) {
        print $data;
    }
[download]

    my $sth = $dbh->prepare(<<SQL);
select id, data from frog limit 3
SQL
    $sth->execute;

    my($id, $data);
    $sth->bind_columns(\$id, \$data);
    while ($sth->fetch) {
        print $id, "\t", $data, "\n";
    }
[download]

Check out Tricks with DBI.

[reply]
[d/l]
[select]