Perl/MySQL

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

Hi, I am hoping some one can help me here...

I basically want to be able to output the contents of a mysql table:

---------------------------------------------------------
|Name   | Address       | Tel         | Email            |
---------------------------------------------------------
| Bob   | 1 London Road | 00000000000 | bob@london.co.uk |
---------------------------------------------------------
| Tony  | 10 Downing St | 99999999999 | tony@no10.co.uk  |
---------------------------------------------------------
[download]

like this:

Name: Bob
Address: 1 London Road
Tel: 00000000000
Email: bob@london.co.uk

Name: Tony
Address : 10 Downing St
Tel: 99999999999
Email: tony@no10.co.uk
[download]

Here's my current code:

use DBI;

#    my $sql = "SELECT * FROM win32_processor";
    my $sql = "SHOW FIELDS FROM win32_processor";


    # Connect to the database.
    if (! saracen_open_database_connection 
            (    \$dbh,
                $program_config{saracen_database},
                $program_config{saracen_admin_login},
                $program_config{saracen_admin_pass}
            )
        )
    {
        # Indicate that a problem occured.
        return 0;
    }

    if (! saracen_execute_query(\$dbh, \$sth, $sql))
    {
        return 0;
    }

    #output database results
    while (@row = $sth->fetchrow_array)
    {     
        print "@row\n" 
    }
[download]

Im afraid my perl or mysql knowledge isn't extensive enough to solve this problem. I can get a table of fields, and I can get a table of values, I just cant seem to put them together!

Cheers, Marcel

Comment on Perl/MySQL Select or Download Code

Replies are listed 'Best First'.
Re: Perl/MySQL by FitTrend (Pilgrim) on Feb 12, 2005 at 20:31 UTC
Update: `while (@row = $sth->fetchrow_array) { print "@row\n" }` [download] to: `while ($ref = $sth->fetchrow_hashref()) { print "Name: $ref->{'name'}\n"; print "Address: $ref->{'address'}\n"; print "Tel: $ref->{'tel'}\n"; print "Email: $ref->{'email'}\n\n"; }` [download] I've always used fetchrow_hashref instead. Its easier since you can visually see what fields your reference in the $ref->{'TABLE_FIELD_NAME'} instead of @row array. You may need to change what's in the $ref-> since I don't know what your field names are. Hope this helps Marc	[reply] [d/l] [select]
Re: Perl/MySQL by matija (Priest) on Feb 12, 2005 at 20:34 UTC
You'll find it easier to user `fetchrow_hashref` for this purpose, like this: `while ($row=$sth->fetchrow_hashref) { foreach $col (%{$row}) { print "$col: ".$$row{$col}."\n"; } }` [download] Edit: I made a stupid mistake: it should be `foreach $col (keys %{$row}) {` [download]	[reply] [d/l] [select]
Re^2: Perl/MySQL by Anonymous Monk on Feb 15, 2005 at 14:12 UTC
Thanks for all your help. I have found a small problem in the code suggested by majita. It appears to print the field name and the value, then on the next line, it prints the value again? `Name: tony tony: Address: 10 downing st 10 downing st: Tel: 00000000000 000000000: Email: tony@no10.co.uk tony@no10.co.uk:` [download] wheres the problem? Cheers, Marcel	[reply] [d/l]
Re: Perl/MySQL by hubb0r (Pilgrim) on Feb 12, 2005 at 20:41 UTC
using $sth->fetchrow_hashref() will get you what you're looking for... a nicely formatted hashref (one row) with column names as keys. Please note that this is not the optimal way for retrieving data from the DBI speedwise... there have been discussions about this, most notably: DBI Tutorial Read up on it and you will learn alot. I know I did.	[reply]
Re: Perl/MySQL by mr_jpeg (Beadle) on Feb 13, 2005 at 00:36 UTC
I won't duplicate what others have said regarding fetchrow->hashref, but I wonder if you're looking for array slice notation? For instance: `print("Name: $row[0]\nAddress: $row[1]\n$Tel: $row[2]\nEmail: $row[3]\ +n");` [download]	[reply] [d/l]