Hey there..

I'm trying to populate a pull-down list with 2 fields (firstname lastname) from a Mysql table:

mysql> describe main;
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| type | varchar(20) | YES | | NULL | |
| firstname | varchar(40) | YES | | NULL | |
| lastname | varchar(40) | YES | | NULL | |

This isn't working at all:

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

## connect to the database
my $dbh = DBI->connect( "dbi:mysql:maindata", "", "" );
my $lastname = param("lastname");

## prepare a SQL statement for execution
$sth = $dbh->prepare( "SELECT firstname, lastname FROM main;");

## execute the statement in the database
$sth->execute();

my $table = $sth->fetchall_arrayref;
my($i, $j);

print qq {

<select name="nameselect" size="1">
<option selected></option>
for $i ( 0 .. $#{$table} ) {
for $j ( 0 .. $#{$table->[$i]} ) {
print "<option>$table->[$i][$j]\t</option>";
}
}
</select>
}

Can anyone help me on the syntax here?

There's no need to make life difficult for yourself! There's a very good module on CPAN that reads all your table structure into memory so you can use it as many times as you like in your script without making further, costly, database connections.

The module is called MySQL::Tableinfo and is available at:

http://search.cpan.org/...MySQL%3A%3ATableInfo

Here's an example of how I've used it to populate a drop-down menu. Hopefully from this example you will be able to work out what you need to do:


Note: I have developed my own module based on this module which shares a few common function names. That's why I have called it WS::MySQL above.

- wil

Do you need the select list to build a new option for each first/last name or should it be like:

<option>Fisrt Last</option>

?

This will do the second option:

Strange.. the following is returning an empty set:

## connect to the database
my $dbh = DBI->connecT( "dbi:mysql:maindata", "", "" );

## prepare a SQL statement for execution
$sth = $dbh->prepare("SELECT firstname,lastname FROM main");
$sth->execute;

print qq {

<select name="nameselect">
while ($row = $sth->fetchrow_hashref) {
<option>$row->{firstname}</option>
<option>$row->{lastname}</option>
}
</select>

}

Yep, because you have a typo :

connecT should be connect

Also you don't want to put perl code inside print qq{ };

Are you sure you can login with no username/password to your database?

The db does require a u/p, but I didn't feel the need to post it here. ;) Fortunately that typo doesnt exist in the script.

What I have below (http://snoopy.edtech.neu.edu/...uations2.pl) returns a list with the correct number of values.. unfortunately they're all empty:

#!/usr/bin/perl -w

## modules
use DBI;
use CGI qw(param);

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

## connect to the database
my $dbh = DBI->connect( "dbi:mysql:maindata", "", "" );

## prepare a SQL statement for execution
$sth = $dbh->prepare("SELECT firstname,lastname FROM main");
$sth->execute;

my $output = qq|<select name="nameselect">|;
$output .= qq|<option>$row->{firstname} $row->{lastname}\n| while (my $row = $sth->fetchrow_hashref);

$output .= qq|</select>|;
print $output;
$sth->finish();

Add in some debugging like:

something() or die DBI->errstr;

....that may trap something. Do it for the prepare/execute.

Doesn't return an error on anything *shrug*.

Seems like this should be a lot simpler to do.

It has to be something with the $row->{firstname}/{lastname} hash references because the script is iterating through the proper number of rows but not returning anything (i.e. The table has three people. The pulldown list the script it creating has three [blank] entries.)

Does the hashref method use the column names as keys to the hash reference? If it does I don't know what the hell the problem could be.

>>
If it does I don't know what the hell the problem could be.
<<

Yes it does.

Inside the while loop try putting:

$output .= map { "$_ => $row->{$_}<br>" } keys %$row;

....and see what it prints.

## connect to the database
my $dbh = DBI->connect( "dbi:mysql:maindata", "", "" ) or die
DBI->errstr;

## prepare a SQL statement for execution
$sth = $dbh->prepare("SELECT firstname,lastname FROM main") or die DBI->errstr;
$sth->execute or die DBI->errstr;

while (my $row = $sth->fetchrow_hashref) {
$crap = map { "$_ => $row->{$_}<br>" } keys %$row;
}

print $crap;

it returns '2'

Yes there is a much simpler way to do this!! Scroll up and re-read my post about doing this by using an already tried and testing module!

- wil

It doesn't look like the MySQL::TableInfo module is as handy as the one that you wrote. It seems to provide a means for returning all column attributes, but not specific data within the table. No part of the module seems to address my needs.

$tbl_info->column("lastname"); returns the column attributes. There doesn't seem to be any part of the module that makes querying and formatting column data easier. Am I missing something???

Okay I finally got it working with the hashref function. The problem is that I have all my HTML form data in a huge qq{} block. I can't dynamically populate the pulldown menu within that block.

Here's a snippet:

--

<form name="facultyeval" method="post" action="">

<table width="80%">
<tr>
<td width="24%">
<div align="right"><b>Student's Name:</b></div>
</td>
<td width="76%">
<select name="nameselect" size="1">

while (my $row = $sth->fetchrow_hashref) {
$fld = $row->{firstname};
print $fld;
}


</td>
</tr>
<tr>
<td colspan="2"><b>Question 1</b></td>
</tr>
<tr>
<td colspan="2">How many Quarters have you had this student?
<select name="totalQuar" size="1">
<option value="1" selected>1</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>

--

As you can see I've embedded the code within the qq{} block which does not work. What's the best way to approach this considering I'm printing an enormous form within that black (300+ lines)?

Thanks again yall!

Paul

Just close and re-open the print...eg...