asked on

Help with an Array

Hi Experts,
I need help with getting two values from my MySQL database and storing them in an array. After that, I need to display them on my webpage. I have my SQl done as far as getting the values, it is putting them into an array that I need help with. Basically what I am doing is getting the number of open jobs that have been posted on our website sorted by the state abbreviation. This is my DB query:

SELECT count(*) AS Projects, hosp_state AS State FROM projects GROUP BY hosp_state;

That is about as far as I have gotten. Any help would be greatly appreciated.

Thanks,

jahmekyat

Tintin

What language do you want to write the CGI program in? If it is Perl, are you using the DBI module?

jahmekyat

ASKER

Yes, it is perl. I already have a global db connect method set up for the DB. When I run my queries they look usually look something like this:

my $array_values = $db->d_value(SELECT count(*), hosp_state FROM projects where status = '' GROUP BY hosp_state");

This is what a block of code looks like when I get and manipulate values from a database. I just have never used an array.
I need to get the number of projects sorted by the state names into an array. This is what the data looks like when I run my query:

Projects State
1 CA
17 CO
1 HI
3 IA
3 IN
1 KS
2 MA
2 MN
1 MO
2 MT
3 NC
2 NE
5 NM
2 NY
12 OH
3 OR
2 TN
1 UT
4 WA
2 WY

Basically, what I need to do is store these in an array and then print them out in my webpage. I am just not that familiar with arrays and then pulling the data out of the array and displaying it on the webpage.

Thanks for all your help in advance. I am so glad that you guys are willing to help out.
T Bryan

ASKER CERTIFIED SOLUTION

Tintin

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jahmekyat

ASKER

Hi Tintin,
Thanks for your help. How do I get my two data fields(count and State Abbrieviation) into the array before I print them out. Where I am confused is how I do that part of it, and how I get two variables into one array variable.

Thanks,

T Bryan

jahmekyat

ASKER

Hi Tintin,
I am confused with this block of code:

$sth->execute($lastname) # Execute the query
or die "Couldn't execute statement: " . $sth->errstr;

Where does my SQL Query fit into this? Here is my SQL query:

$sth = $db->d_value("SELECT count(*), hosp_state FROM Projects GROUP BY hosp_state");

Thanks for your help,

T Bryan

Tintin

I'm taking the example from the DBI documentation examples.

Here's the full code:

use DBI;

my $dbh = DBI->connect('DBI:Oracle:payroll')
or die "Couldn't connect to database: " . DBI->errstr;
my $sth = $dbh->prepare('SELECT * FROM people WHERE lastname = ?')
or die "Couldn't prepare statement: " . $dbh->errstr;

print "Enter name> ";
while ($lastname = <>) { # Read input from the user
my @data;
chomp $lastname;
$sth->execute($lastname) # Execute the query
or die "Couldn't execute statement: " . $sth->errstr;

# Read the matching records and print them out
while (@data = $sth->fetchrow_array()) {
my $firstname = $data[1];
my $id = $data[2];
print "\t$id: $firstname $lastname\n";
}

if ($sth->rows == 0) {
print "No names matched `$lastname'.\n\n";
}

$sth->finish;
print "\n";
print "Enter name> ";
}

$dbh->disconnect;