extracting data from an array

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
zipperhead
Forum Newbie
Posts: 1
Joined: Mon Sep 01, 2008 3:33 pm

extracting data from an array

Post by zipperhead »

Hello, I'm hoping someone can help me with this...I have a query which counts the number of locations per category and outputs the results into an array. What I am trying to do is extract the COUNT value out of the array and save it to a variable based on location.

The query looks like this:

Code: Select all

 
$query = "SELECT location, COUNT(*), category FROM table1 
          WHERE category='football' GROUP BY location"; 
 
Outputting the results using the statement below appears to give the correct answers.
 

Code: Select all

 
$result = mysql_query($query);
while($row = mysql_fetch_array($result)){
    echo $row['COUNT(*)'] ." ". $row['location'];
    echo "<br />";
}?>
Results:
3 California
6 Colorado
1 Florida
etc...

What I am trying to do is extract out the results (the COUNT value) based on location from the array into variables to use in other places. I know the code below is wrong but I hope it at least shows the intent.
 
$cal=$row['COUNT(*)'] while ($row['location'] ='California');
$col=$row['COUNT(*)'] while ($row['location'] ='Colorado');
$flo=$row['COUNT(*)'] while ($row['location'] ='Florida');

Then can display the variables in other places as needed.

echo $cal;

results in: "3"

I hope this makes sense as I am quite new to this stuff. So far nothing I have tried seems to work, Any suggestions on how to do this would be most helpful. Maybe using something besides the "fetch_array" would work better?

thanks a bunch,

ZH
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: extracting data from an array

Post by califdon »

Just substitute your array assignment instead of the echo statements:

Code: Select all

$result = mysql_query($query);
while($row = mysql_fetch_array($result)){
    MyArray[][0] = $row['location']
    MyArray[][1] = $row['COUNT(*)'];
}?>
 
Post Reply