Jada Pinks Jada Pinks - 8 months ago 36
MySQL Question

CGI using MySQL: How to display all data from table?

This is for a homework. When I run the script, it will only show one row of data from the database.

my @record = $sql->fetchAll();
is the problem, I think. How do I make it so it outputs ALL rows from the table?

use CGI qw(:standard);
use CGI::Carp qw(fatalsToBrowser);
use DBI;
use strict;
use warnings;

my $idnum = param('idnum');
my $year = param('year');
my $make = param('make');
my $model = param('model');
my $color = param('color');
my $price = param('price');

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

my $db = "DBI:mysql:cars:localhost";
my $dbh = DBI->connect($db,"root","",{RaiseError=>1});

my $sql = $dbh->prepare(qq(select * from cars));

print <<here;

<div align="center">
<table border ="2" bordercolor="green">
<td>Id</td><td>Year</td><td>Make</td><td>Model</td><td>Color</td> <td>Price</td>

my @record = $sql->fetchrow_array; ### there is only one row in resultset

for(my $i = 0; $i < @record; $i++ ) {
print "<td>$record[$i]</td>";
print "</tr>";

print "</table></div></body></html>";


Answer Source

The fetchrow_array method only returns one line at a time, until it's exhausted. It's an iterator. So your code correctly prints one row at a time.

You need to iterate the results until there are no more left.

while (my @record = $sql->fetchrow_array) {
   print "<tr>";
   print "<td>$_</td>" foreach @record;
   print "</tr>";

I've used the considerably shorter foreach @record, which gives you each element in the array, one by one. That way you don't need to deal with indexes. Putting it after the line you want to repeat in the loop is called postfix notation.