...

View Full Version : Assigning MySQL Query to Array



bjblackmore
12-16-2008, 04:06 PM
Hi,

I have a database of support requests, and I'd like to query some information to create a barchart for a report. I'd like to find out how many requests were made on each day of the month. I need the data in the following array format:
$data = array(
"1" => 5,
"2" => 4,
"3" => 3,
...<snip>...
"29" => 2,
"30" => 5,
"31" => 1);
With the day number on the left, and no of requests on the right.



$month=12;
$year=2008;
$numdays = cal_days_in_month(CAL_GREGORIAN, $month, $year) ;


for ( $i = 1; $i <= $numdays; $i++) {
$data = array();
$sql = "SELECT COUNT(id) FROM `support_requests` WHERE `date` LIKE '$year-$month-$i %'";
$result = db_query($sql) or die('Query failed: ' . mysql_error());
while($row=mysql_fetch_array($result)){
$data = array($i=> $row['COUNT(id)']);
}
}


However, when I print_r($data) all I get is:Array ( [31] => 0 ). But if I move print_r($data) inside of the for() function:


$data = array($i=> $row['COUNT(id)']);
}
print_r($data);
}

I get something slightly better, although I'm not sure if this is correctly formatted for what I need:

Array ( [1] => 10 )
Array ( [2] => 5 )
Array ( [3] => 3 )
Array ( [4] => 6 )

What am I doing wrong? How do I set the array, so I can access the $data outside of for()

Thanks

Ben

abduraooft
12-16-2008, 04:17 PM
$data = array($i=> $row['COUNT(id)']); Try

$data[] = array($i=> $row['COUNT(id)']); instead.

Fumigator
12-16-2008, 04:21 PM
You are assigning a brand new value to $data in every iteration of the "for" loop. Not surprisingly, after the "for" loop is done, all you see is the very last thing that was assigned to $data.

Even worse, you're re-assigning stuff to $data inside the "while" loop too, so you're double-clobbering.

You really need a 2-dimensional array, one dimension to handle multiple results from your query and one dimension to handle the multiple days. You'll want to change the "while" loop to another "for" loop and then use the iterators from both "for" loops as indexes in $data.



$data[$i][$j] = $row['COUNT(id)'];

bjblackmore
12-16-2008, 04:32 PM
Thanks for the replies.

abduraooft, adding the [] to the $data array resulted in:

Array ( [0] => Array [1] => Array [2] => Array [3] => Array [4] => Array [5] => Array [6] => Array [7] => Array [8] => Array [9] => Array [10]

Fumigator, which for loop would "$row=mysql_fetch_array($result)" fit in?

Thanks

Ben

bjblackmore
12-16-2008, 04:44 PM
Ah,

I think I have it, I changed $data[] to $data[$i], and it seems to be giving the correct output when I print_r outside of the for loop


for ( $i = 1; $i <= $numdays; $i++) {
$sql = "SELECT COUNT(id) FROM `support_requests` WHERE `date` LIKE '$year-$month-$i %'";
$result = mysql_query($sql) or die('Query failed: ' . mysql_error());
while($row=mysql_fetch_array($result)){
$data[$i] = $row['COUNT(id)'];
}
}

Does this look right? Output seems to be correct, and my graphical barchart code seems to understand the $data array.

Fumigator
12-16-2008, 05:48 PM
That works fine, but you should keep in mind you are still going to get only one result from your query. Maybe that's what you want, or maybe that query will only ever produce only one row, I don't know.

More stable would be to scrap the "while" loop entirely and just call mysql_fetch_array() once, to get one result from the query.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum