Perl script malfunctioning on query [Archive]

Liz_W

05-19-2005, 09:24 PM

I am trying to write a Perl script that runs a Select query against my database of cars.

The query is based on choices made via a form, the user selects the make and model of a car and the query is then meant to bring up the registration and colour of applicable cars, my code only brings up one registration number, I know there is more than one of the model selected in the database and the colours are not outout at all.

Can someone help me out here please, I know it must be something simple as the script works until the output.

#!/www/perl/bin/perl.exe
use CGI;

use DBI;

use CGI::Carp qw(fatalsToBrowser);

$CGI::POST_MAX = 128;
$CGI::DISABLE_UPLOADS = 1;

$model=CGI::param("model");
$make=CGI::param("make");
#connect to database giving pass and username
if (!($dbh = DBI->connect("DBI:mysql:dbname:localhost", 'uname', 'pass')))
{
&outputErr("couldn't connect to database".DBI->errstr);
}

if (!($query = $dbh->prepare("SELECT registration,colour FROM car
WHERE make = '$make'
AND model = '$model' ")))
{
&outputErr("couldn't prepare statement".$dbh->errstr);
}

#execute the query
if (!($query->execute()))
{
&outputErr("couldn't execute statement".$query->errstr);
}
while (@car = $query->fetchrow_array())
{
$registration=$car[0];
$colour=$car[3];

}

$query->finish;

$dbh->disconnect;

print <<PAGE;
Content-type: text/html \n\n
<html>
<head>
<title>Details</title>
</head>

<body bgcolor="#E7E7E7">
$registration
$colour
</body>
</html>
PAGE
exit 0;

Sorry for the double post, as I am unsure where my problem lies, in the Perl or the SQL.

rwedge

05-20-2005, 03:00 AM

When you loop through the data you are not appending the scalers, you
overwrite any previous value with any new data that satisifies your search.

while (@car = $query->fetchrow_array())
{
$registration=$car[0];
$colour=$car[3];

}

Try :

while (@car = $query->fetchrow_array())
{
$registration .= $car[0];
$colour .= $car[3];

}

/Bob