results from a mysql query using dbi 1

[gshirey]

Another question for the end of the day. A pattern has emerged . . . I spend all day struggling, post at end of day, have answer at beginning of new day. Like the seasons, its great. Thanks in advance.

I am now succesfully getting results from a mysql query. However, I am having a hard time parsing the results. I would like to be able to print a row at a time. But I just can't figure out what to use (fetch, fetchall_arrayref, etc.) I'm sure something has been written about formattin responses from dbi queries. Any pointers would be greatly appreciated.

I am currently using the following:

while (@ary = $sth->fetchrow_array ())
	{
	print join ("\n", @ary), "\n";
	}

And while that does give the proper results, it gives them in an unparsed blob of info.

Any tips greatly appreciated.

Thanks

 

[tsdragon]

I'm assuming you are outputting html, so you should use <br> instead of, or in addition to, \n. Also, why not put them into individual variables when you fetch them instead of an array, like this, for example:

while ( ($fname,$lname,$email,$phone) = $sth->fetchrow_array() ) {
   print &quot;First Name: $fname<br>\n&quot;;
   print &quot;Last Name: $lname<br>\n&quot;;
   print &quot;Email: $email<br>\n&quot;;
   print &quot;Phone: $phone<br>\n&quot;;
}

Of course, you could combine the print statements into one, print out table row/cell html tags, etc.
Tracy Dryden
tracy@bydisn.com

http://www.bydisn.com

Meddle not in the affairs of dragons,
For you are crunchy, and good with mustard.

 

[mms1]

Use this one this works fine
Mamun
mms@sdnbd.org

my ($dbh4, $sth4,@row4);
$dbh4 = DBI->connect(&quot;dbi:mysql:database_name&quot;,username,password) || &CgiDie (&quot;Cannot Open Show Database&quot;
$sth4 = $dbh4->prepare(&quot;select id,po from po where id='$id'&quot;
$sth4->execute;

while(@row4 = $sth4->fetchrow_array) {
print qq|
<tr>
<td width=&quot;10%&quot; bgcolor=&quot;#CCCCCC&quot; align=&quot;right&quot;>$row4[13]</td>
<td width=&quot;10%&quot; bgcolor=&quot;#CCCCCC&quot; align=&quot;right&quot;>$row4[0]</td>
<td width=&quot;10%&quot; bgcolor=&quot;#CCCCCC&quot; align=&quot;right&quot;>$row4[1]</td>
<td width=&quot;10%&quot; bgcolor=&quot;#CCCCCC&quot; align=&quot;right&quot;>$row4[2]</td>
<td width=&quot;10%&quot; bgcolor=&quot;#CCCCCC&quot; align=&quot;right&quot;>$row4[3]</td>
<td width=&quot;10%&quot; bgcolor=&quot;#CCCCCC&quot; align=&quot;right&quot;>$row4[4]</td>
<td width=&quot;10%&quot; bgcolor=&quot;#CCCCCC&quot; align=&quot;right&quot;>$row4[5]</td>
<td width=&quot;10%&quot; bgcolor=&quot;#CCCCCC&quot; align=&quot;right&quot;>$row4[6]</td>
<td width=&quot;10%&quot; bgcolor=&quot;#CCCCCC&quot; align=&quot;right&quot;>$row4[7]</td>
<td width=&quot;10%&quot; bgcolor=&quot;#CCCCCC&quot; align=&quot;right&quot;>$row4[8]</td>
<td width=&quot;10%&quot; bgcolor=&quot;#CCCCCC&quot; align=&quot;right&quot;>$row4[9]</td>
</tr>
|;
}
$sth4->finish;
$dbh4->disconnect;

 

[gshirey]

Again,

Thanks for the help. Though I'm not sure what the fetch is doing, I now have the pieces to format how I would like. This forum has been such a treasure since changing jobs and losing a perl guru to ask questions.

 

[hmerrill]

Not sure what platform you are on, but the &quot;perldoc's&quot; documentation that comes with perl are an *excellent* resource for locating information on Perl and any of the perl modules that you use. For example, you say you're not sure what &quot;fetch&quot; is doing - have a look at the perldocs for the perl DBI module by doing

perldoc DBI

if you're on *nix. If you're on NT or Windows, I believe you can bring up a DOS window, and at the prompt type in &quot;perldoc DBI&quot;. You can search for the word &quot;fetch&quot; using the forward slash operator, and then typing in &quot;fetch&quot; and hitting return.

There's a FAQ on using the perldocs in this forum - have a look at that if you have any questions. You can start at the top by doing &quot;perldoc perl&quot; to see all the different perldocs that come with Perl. Then, each additional perl module you install normally comes with it's own perldoc as well - such as DBI, and the DBD module for the database you use(DBD::mysql).

If you plan on programming or maintaining perl DBI code to interact with a database, I would highly recommend the &quot;Programming the Perl DBI&quot; book by Descartes and Bunce.

HTH.
Hardy Merrill
Mission Critical Linux, Inc.

http://www.missioncriticallinux.com

 

[tsdragon]

What the fetchrow_array is doing is getting the next &quot;row&quot; in the table that matches your query and returning an array (or list) of the fields you asked for from that row. It will return an empty array/list when there are no more matching rows. The empty array/list is interpreted as &quot;false&quot;, which is what makes the while end (a non-empty array/list is &quot;true&quot. Tracy Dryden
tracy@bydisn.com

http://www.bydisn.com

Meddle not in the affairs of dragons,
For you are crunchy, and good with mustard.