Can I display the output of MySQL using CGI?

Question

HI, I have written a program in Perl using the DBI module. Can I display the output of MySQL using CGI? If so please help me.

program:

#!/usr/bin/perl -w
use DBI;

print "Content-type:text/html\n\n";
$db_handle = DBI->connect("dbi:mysql:database=CYP1B1;host=localhost:192.168.3.93;")
    or die "Couldn't connect to database: $DBI::errstr\n";

$sql = "SELECT * FROM BPCG";
$statement = $db_handle->prepare($sql)
    or die "Couldn't prepare query '$sql': $DBI::errstr\n";

$statement->execute()
    or die "Couldn't execute query '$sql': $DBI::errstr\n";
print "Location\tNucleotidechange\tAminoacidchange\n";
while(($Location,$Nucleotidechange,$Aminoacidchange)=$statement->fetchrow_array())
{
    print "$Location  $Nucleotidechange               $Aminoacidchange \n";

}
$db_handle->disconnect();

Answer 1

Start at CPAN.

Answer 2

What you have is a CGI script, near enough. You're not outputting anything HTML, though, that output is space-separated. Change your content type to 'text/plain'.

What you need is a webserver to run your CGI script. Go look up something about lighttpd or apache and how they need to be set up to run CGI scripts (for instance, the example for Apache). Sorry, nothing on that is going to be very helpful, the topic of setting up webservers is quite complicated, but hopefully a few search terms will set you off in the right direction.

Alternatively, be aware that this is not a modern way of writing anything for the web. If you're exploring what's possible (as opposed to solving a problem really quickly), I suggest you learn a framework such as Catalyst (which is on CPAN). There's more upfront work for you there, but whatever you're trying to do will be easier to do in the long run.

Answer 3

There's nothing particularly special about CGI programs. You have to output a CGI header, but after that you can output what you like just like any other program.

What are you trying to do, exactly, and what problem are you having? What doesn't work as you expect when you run your program?

Answer 4

Try reading Ovid's CGI Course. It's got the best explanation of basic CGI programming I've seen.

Answer 5

It looks like you're already outputting the required header (Content type + 2 newlines) so all that remains for you to view this via a web browser is to put the program in a directory on a web server that can run cgi scripts (typically called cgi-bin), make sure the file is executable (chmod 755 yourscript.cgi) and then point your web browser at the required URL, e.g. http://yourserver/cgi-bin/yourscript.cgi

Answer 6

I too wondered if it were difficult to display Mysql database records using cgi on web pages. Here's a link to how I did it (although it's fairly basic, it really wasn't too difficult): Using Perl and Mysql to create a webpage

I made use of the CGI, HTML::Template and DBI modules. Here's a link to a basic tutorial that uses modules to create a guestbook webpage: Using modules to create a webpage