[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Help with an Array

Posted on 2005-05-02
6
Medium Priority
?
216 Views
Last Modified: 2013-12-25
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
0
Comment
Question by:jahmekyat
  • 3
  • 3
6 Comments
 
LVL 48

Expert Comment

by:Tintin
ID: 13913901
What language do you want to write the CGI program in?  If it is Perl, are you using the DBI module?
0
 

Author Comment

by:jahmekyat
ID: 13914339
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


0
 
LVL 48

Accepted Solution

by:
Tintin earned 2000 total points
ID: 13914656
Generally, it would look something like:

          $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";
          }

0
How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

 

Author Comment

by:jahmekyat
ID: 13918120
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
0
 

Author Comment

by:jahmekyat
ID: 13919214
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
0
 
LVL 48

Expert Comment

by:Tintin
ID: 13931461
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;
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is becoming increasingly popular to have a front-page slider on a web site. Nearly every TV website,  magazine or online news has one on their site, and even some e-commerce sites have one. Today you can use sliders with Joomla, WordPress or …
The Windows functions GetTickCount and timeGetTime retrieve the number of milliseconds since the system was started. However, the value is stored in a DWORD, which means that it wraps around to zero every 49.7 days. This article shows how to solve t…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses
Course of the Month19 days, 11 hours left to enroll

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question