Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    New Coder
    Join Date
    Dec 2008
    Posts
    34
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Question Assigning MySQL Query to Array

    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.


    Code:
    $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:

    Code:
    	$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

  • #2
    Supreme Master coder! abduraooft's Avatar
    Join Date
    Mar 2007
    Location
    N/A
    Posts
    14,864
    Thanks
    160
    Thanked 2,224 Times in 2,211 Posts
    Code:
    $data = array($i=> $row['COUNT(id)']);
    Try
    PHP Code:
    $data[] = array($i=> $row['COUNT(id)']); 
    instead.
    The Dream is not what you see in sleep; Dream is the thing which doesn't let you sleep. --(Dr. APJ. Abdul Kalam)

  • #3
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    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.

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

  • #4
    New Coder
    Join Date
    Dec 2008
    Posts
    34
    Thanks
    5
    Thanked 0 Times in 0 Posts
    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

  • #5
    New Coder
    Join Date
    Dec 2008
    Posts
    34
    Thanks
    5
    Thanked 0 Times in 0 Posts
    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

    Code:
    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.

  • #6
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •