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

Hi there,

I have a MySQL database and am using DBI to query it and a CGI
script to show the results in the browser.

My cgi takes input from the form (good) and performs as expected if the
input is a record in the database (good). However if nonsense is typed in - ie. the record
is not in the database, all I get is an internal sever error.

I tried putting counting the number of retrieved rows, figuring if there was nothing retrieved from the
database, $count would be zero. I then tried an if statement (if $count==0 then print stuff about record not found etc.)
This didn't help though.
#!/biol/programs/perl/bin/perl -w

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

#print "Content-type:text/html\n\n";

my($dbh,$sth,$count,$sql,$cgi);

$cgi = new CGI;
use CGI::Carp qw(fatalsToBrowser);


    
$dbh = DBI->connect("DBI:mysql:host=myhost;database=mydb",
    "username","password",
    {PrintError =>0, RaiseError => 1})|| die "Database connection not 
+made: $DBI::errstr";

    $sql = qq{SELECT * FROM name};
    $sth = $dbh->prepare($sql);
    
    $sth->execute();
    print header(),
    start_html(-title=>"My Database",-BGCOLOR=>"white",-FONT=>"Times")
+;
    
    print STDOUT "<H1>My Database</H1><BR><BR>";
    
    print STDOUT "<TABLE><TR><TH>Short name</TH><TH>Long name</TH></TR
+>";
    
    my ($id,$short_name,$long_name);
    $sth->bind_columns(undef,\$id,\$short_name,\$long_name);
    
    while($sth->fetch()){
        print STDOUT "<TR><TD>$short_name</TD><TD>$long_name<TD></TR>"
+;
    }
    
    print STDOUT "</TABLE>";
    print p (end_html);
    
    
    $sth->finish();
    $dbh->disconnect();
    exit(0);

[download]
Any ideas ?

thanks,
basm101

Replies are listed 'Best First'.
Re: MySQL/DBI: Error if entry is not in the database
by dws (Chancellor) on Feb 05, 2003 at 18:31 UTC
    However if nonsense is typed in - ie. the record is not in the database, all I get is an internal sever error.

    Check your server's error logs.

    I see a couple of problems. The zero'th problem is that you aren't showing us any code that takes data from a form and injects it into a WHERE clause. How can you expect to us to diagnose a problem when you're not showing us the right code?

    Another problem is that you're waiting far too long to print a response header. Do that right up top, before you start opening up database connections and firing off queries.

    And I don't think 

      print p (end_html);

    [download]
    is doing what you expect it to do.

[reply]
[d/l]
      I would even dare to add: not only check your server's error logs but show them to us as well! (of course only the relevant parts, or before you know it someone dumps a few meg of server error logs on this site ;-) )

      CountZero

      "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law

[reply]
Re: MySQL/DBI: Error if entry is not in the database
by powerhouse (Friar) on Feb 05, 2003 at 20:57 UTC
    #print "Content-type:text/html\n\n";

    Why did you comment out the content type?

    since your using CGI.pm's :standard the easier way to print the headers is like this:
    print header();

    [download]
    to print the body tags and any CCS, Javascripts and stuff, use the start_html clause:
    print start_html(-bgcolor=>"#FFFFFF",
-text=>"#000000",
-style=>{code=>$somecode},
-script=>{code=>$Somejscript});

    [download]


    And how I test for rows is like this:

        my ($sth, $row);
    $dbh = <useyourconnection>;
    $sth = $dbh->prepare (qq{ SELECT * FROM mytable });
    $sth->execute();
    while ($row = $sth->fetchrow_hashref()) {
        # these are the rows that do exists do
        # what you want with those rows.
    }

    [download]

    If you only want to check for ONE record you can do it like this:

        $sth = $dbh->prepare (qq{ SELECT * FROM mytable WHERE some_column 
+= ? }); # The ? is a placeholder
    $sth->execute($str); # $str is what your searching for
    $row = $sth->fetchrow_hashref(); # This executes the above
    $sth->finish();# This closes the connection.

    [download]

    That is untested, so please take it with a grain of salt.

    I actually store everything in a LOCAL module I create. I have the script push the location to @inc then just use the module. Then my connections look more like this (EVER TIME):
    $dbh = SOMEDIR::SOMENAME::Connect();

    [download]

    That way I don't have to re-write the whole dang code every single time. I just use that statement and it's much faster.

    Just because I showed you that, does not mean you don't need to show more of your code to get a better answer. I'm just showing you how I do it, PLUS if you leave the print Content-type commented out, you'll get a error in your browser every time. The error logs for that should say premature end of script headers.

    Hope that helps some.
    Richard.

    UPDATE:
    Sorry, I did not NOTICE the part that you commented out the print Content-type because you ALREADY used CGI.pms functions which I recommended. My bad. Please disregard that part.

    Thanks!

    Added update per author request - dvergin 2003-01-23

[reply]
[d/l]
[select]
Re: MySQL/DBI: Error if entry is not in the database
by jasonk (Parson) on Feb 05, 2003 at 18:26 UTC

    You can use the 'rows' method to determine if your select returned any rows or not:

    unless($sth->rows) {
    print header,"<H1>No rows selected</H1>";
}

    [download]
[reply]
[d/l]
      Thanx guys - the code jasonk suggested works fine.
      Ta for everyone's comments. I have to admit I'm not sure how to check my server error logs <blush>
      I use CGI::Carp qw(fatalsToBrowser); and hope that tells me what I need to know

      I only started doing CGI scripts last week so I'm still confused about some basic stuff -
      I commented out the content-type line because I didn't like it literally
      printing Content-type:text/html\n\n in my browser window !

      What do you mean I would get an error if I left the Content-type line out ? I don't...
      Thanks
      basm101

      As dws thought it might help to see my code that gets data from the form
      here it is
      #!/biol/programs/perl/bin/perl -w
use strict;
use DBI;
use CGI qw(:standard);

#print "Content-type:text/html\n\n";

my($cgi);

$cgi = new CGI;
use CGI::Carp qw(fatalsToBrowser);

print "Content-type:text/html\n\n";
print <<EOF;
<HTML>
<HEAD><TITLE>My Database</TITLE></HEAD>
<BODY BGCOLOR="#CCCCFF" TEXT="indigo">

<h2>My Database</h2><HR>
<TABLE>
<TR VALIGN="baseline">
    <TD><h3>Enter name</h3></TD>
    <FORM METHOD="post" ACTION="my_server_here">
    <TD><INPUT TYPE="TEXT" NAME="querybox" SIZE="50"></TD>
    <TD><INPUT TYPE="SUBMIT">
    </FORM>

</TR>
</TABLE>

</BODY>
</HTML>
EOF

      [download]
[reply]
[d/l]

Back to Seekers of Perl Wisdom