building array from MySql Db SELECT function

[bazz]

starting to make scripts that communicate with a MySQL Db.

Haven't been able to confirm the best way to put them in an array. Is it simply a matter of changing my $fieldsInAccountsTable to my @fieldsInAccountsTable? (shown in red)

I want an array so I can run a loop to output a form with a textbox for each field.



my $db = 'whatever';
my $server = 'server name';
my $port = '1234';
my $user = 'whatever';
my $pass = 'whatever';
my $table = "Account";

my $dbh = DBI->connect("DBI:mysql:$db:$server:$port", $user, $pass,
{RaiseError => 1, PrintError => 0, AutoCommit => 1})
or die "Can't connect: DBI->errstr()";

my $fieldsInAccountsTable = $dbh->prepare("LISTFIELDS $table");
perhaps this query is better?

my $query = $dbh->prepare("Select * From `Account");



Bazz

[FishMonger]

my $query = $dbh->prepare("Select * From `Account");
No, simply changing $query to an @query array won't work. That line is only creating the statement handle object, you still need 2 more steps. You need to execute the statement handle and loop over the results.

This is probably to most common apprroach.
my $sth = $dbh->prepare("Select * From Account");
$sth->execute;

while (my @fields = $sth->fetchrow_array) {
# each loop is 1 record (row) from the db
# you can either process each row 1 by 1
# or push it onto another array and process it after the loop
}

[FishMonger]

I thought you might be interested in seeing an example from one of my scripts.

my %departments = get_departments($frysdb);
my @departments = sort keys %departments;

print $cgi->popup_menu(-name=>'department',
-values=>\@departments,
-labels=>\%departments,
-default=>\$form{'department'}
);

sub get_departments {
my $dbh = shift;
my %dept;
my $sth = $dbh->prepare("SELECT number, name FROM departments")
or die "prepare statemnet failed: $DBI::errstr\n";
$sth->execute;

while(my ($dept, $name) = $sth->fetchrow_array) {
$dept{$dept}=$name;
}
return %dept;
}

Resulting html:
<select name="department" tabindex="5">
<option value="01">01 Components</option>
<option value="02">02 Computers</option>
<option value="03">03 Software</option>
<option value="04">04 Service</option>
<option value="05">05 Audio Visual</option>
<option value="06">06 Grocery</option>
<option value="07">07 Appliance Telecom</option>
<option value="10">10 Audit</option>
<option value="11">11 Receiving</option>
<option value="12">12 Customer Service</option>
<option value="13">13 Loss Prevention</option>
<option value="84">84 ISP</option>
<option value="85">85 Legal</option>
<option value="86">86 Payroll</option>
<option value="87">87 Archive</option>
<option value="88">88 District Personnel</option>
<option value="89">89 Construction/Maintenance</option>
<option value="90">90 Store Development</option>
<option selected="selected" value="91">91 ISO</option>
<option value="92">92 Accounts Payable</option>
<option value="93">93 General Ledger</option>
<option value="94">94 Benefit Services</option>
<option value="95">95 Corporate Administration</option>
<option value="96">96 Advertising</option>
<option value="97">97 ISD</option>
<option value="98">98 Accounts Receivable</option>
<option value="99">99 Store Management</option>
</select>

[bazz]

FishMonger, that is very helpful: thank you.:thumbsup: :thumbsup: :thumbsup:

I think I can see now how my perl code will be much shorter with a MySQL Db. :)

bazz

[bazz]

OK, I have managed to connect but am getting an error

I think it's in the my $sth = $dbh->prepare("SELECT LISTFIELDS $table") part. Perhaps I shouldn't use SELECT and LISTFIELDS together?

bazz


sub submitToDb {

print "This sub is for sending your form data to the Db.\n";

my $db = 'abc';
my $server = 'localhost';
my $port = '1234';
my $user = 'username';
my $pass = 'password';
my $table = 'Accounts';

my $dbh = DBI->connect("DBI:mysql:$db:$server:$port", $user, $pass,
{RaiseError => 1, PrintError => 0, AutoCommit => 1})
or die "Can't connect: DBI->errstr()";

# all is well!
print "Success: connected!\n";
my $frysdb;
my %departments = get_departments($frysdb);
my @departments = sort keys %departments;
my $cgi;
my %form;
print $cgi->popup_menu(-name=>'department',
-values=>\@departments,
-labels=>\%departments,
-default=>\$form{'department'}
);

sub get_departments {
my $dbh = shift;
my %dept;
my $sth = $dbh->prepare("SELECT LISTFIELDS $table")
or die "prepare statement failed: $DBI::errstr\n";
$sth->execute;

while(my ($fieldNames) = $sth->fetchrow_array) {
$fieldNames{$fieldNames}=$name;
}
return %dept;
}

# disconnect from the MySQL server
$dbh->disconnect();

}


and here's the error



Software error:

Can't call method "prepare" on an undefined value at registration.pl line 361.

[nkrgupta]

I guess since you are initialising $dbh in sub submitToDb, i dont think you need to do my $dbh = shift; in sub get_departments, because maybe your $frysdb and FM's $frysdb (which is being passed as the connection string to sub submitToDb) are not the same... Just a thought!

[bazz]

I have tried another method for now though, I'll look at Fishmonger's code later.

Thanks Bazz

[bazz]

OK, I have connected to the Db and retrieved the list of fieldnames.

my $dbh = DBI->connect("DBI:mysql:$db:$server:$port", $user, $pass,
{RaiseError => 1, PrintError => 0, AutoCommit => 1})
or die "Can't connect: DBI->errstr()";

# all is well!
print "Success: connected!<br />\n";

my $sth = $dbh->prepare("SHOW FIELDS From $table");
$sth->execute;

while (my @fields = $sth->fetchrow_array) {
# each loop is 1 record (row) from the db
# you can either process each row 1 by 1
# or push it onto another array and process it after the loop
print "fields =@fields<br />";
}



the output from the first field (and others), is like this:

fields =AccID int(11) PRI auto_increment

Can I retrieve only the AccID or should I process this data in perl to use only the bit I need to?

Bazz

[nkrgupta]

Well... i guess parsing needs to be done in PERL. I didn't come across any mysql command which list only the column names without the extra details.

[bazz]

Thanks nkrgupta. :thumbsup:

I thought that would be the reply but because I want to build really efficient code, I thought I'd ask anyway.

Bazz

[FishMonger]

Is this closer to what you want?
my $dbh = DBI->connect("DBI:mysql:$db:$server:$port", $user, $pass,
{RaiseError => 1, PrintError => 0})
or die "Can't connect: DBI->errstr()";

# all is well!
print "Success: connected!<br />\n";

my $sth = $dbh->prepare("SELECT * From $table");
$sth->execute;

my $fields = $sth->{'NAME'};
for ( @{$fields} ) {
print "Field Name: $_<br />\n";
}

while (my @fields = $sth->fetchrow_array) {
print "field values = @fields<br />\n";
}
$dbh->disconnect;

[bazz]

Thanks FishMonger.

I had been trying not to use SELECT * $table because I wanted only the field names without all the table data, which may become quite large. I thought that pulling in all the table data, unnecessarily, would build in an inefficiency.

I am now searching the web for a method for pulling in only certain fieldnames - number 4, 5 and 6.

That said however, the way you've written it, it works perfectly well (no surprise there then :) ).

bazz

[FishMonger]

I am now searching the web for a method for pulling in only certain fieldnames - number 4, 5 and 6.
my $sth = $dbh->prepare("SELECT field4, field5, field6 From $table");

When I say field4, field5, field6, I mean the actual field names and you can select as few or as meny fields as you want and they could be in any order.

You should always know what the field names are in the database because you're working with a known fixed database format. If you don't know what the feild names are, then that indicates that the database isn't properly designed. Are you dynamically adding/removing fields?

This may help.
http://dev.mysql.com/doc/refman/5.0/en/select.html

[FishMonger]

An Introduction to Database Normalization
http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html

[bazz]

Thanks FishMonger,

I am not yet adding fields remotely however, I am building a form to let new customers create their own account details, which will manifest themselves as a new record in the accounts table.

I am actually mulling over, the concept of making the form populate with textboxes, determined by the DB and certain criteria. So, if there were another relevant field added to the Db later, then, I wouldn't need to redo my form as it would change its format automatically to take account of the new field if it were relevant. Not sure if that's a good idea but, because the Db powers the whole thing, I thought it made sense as avoiding hard code, will save a lot of extra space. (efficiency is my latest buzzword :D ).

I think I have it normalised. but thanks for the links. I'll double check.

bazz