Creating a recallable array from Query Result?

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
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Creating a recallable array from Query Result?

Post by infolock »

Hi again. I'm trying to figure this one out, but it's got me stumped. What I have is a query statement that is calling a GROUP BY statement, and dumbing the rows out. What I want to do, is query both the PACKETS and the TIME it goes along with in 2 seperate arrays, that I can calll later to compare to static arrays in order to post them into a Bar Graph..

Best way to show you what I mean is by examples of my code, so here goes :

Code: Select all

<?php

###############################################
# I created a static array here because what I wanted to do is be able to 
# set an array to the possible results of hour(time).  The reason is, when 
# you post your results to a chart based on the below query, it will ONLY
# post the results of times that have packet values, and then it will just 
# skip the ones it has posted, and then post the other empty times, 
# leaving the chart very unorganized.   i'll give an example of the chart
# after showing you the code. 
###############################################

//Static Array that will be called to compare with results
	$time = array('0','1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13','14','15','16','17','18','19','20','21','22','23');

	@mysql_pconnect ('localhost','username','pass');
	@mysql_select_db ('logparser');

###############################################
# Get a SUM of all Packets based on the HOUR in which they are stored
# in the database.
#
#   Example :
#                     Time         |         Packets
#                     04:00:44   |         393475
#                     04:34:00   |           39344
#                     10:00:44   |          348576
#                     18:04:33   |           34848
#
#    Would result in:    
#                  Hour (4 to 5) - 432819 Total Packets
#                  Hour (10 to 11) - 348576
#                  Hour (18 to 19) -  34848
#
#     I need to get each Hour found to have packets, and place it as an
#     aray such as $found_time[4] = '4';   Packets[4] = '432819';
#
#
#     however, when it DOES find a time, it assigns it to the very first array
#     meaning, it would assign it to $found_time[0], and screw up being
#    able to do something like     If $time[4] == $found_time[4] then
#     x = '04:00am', and then being able to match the packet array with
#     that time found..     Anyways, here is the sql statement
###############################################

	$sql="select sum(packets), hour(time) from log group by hour(time) order by time DESC";
	$result = mysql_query($sql);

?>

now, i did find a lot of different opinions on how to do this with phpBuilder, and google, but none seem to work the way i need it to...

one thing that i found that was almost it, was

Code: Select all

<?php

	while( $t = mysql_fetch_row($result))
	{ 
 		$array[] = $t;
 	}

?>
but, i have no way of knowing how to extract each array, compare it to the static array, and then be able to put the correct packet amounts with the corresponding time array :(




Right now, the bar chart is doing stuff like this to me :

Code: Select all

800000 bytes
	400000 bytes 
	200000 bytes
	100000 bytes 
	                      |_____|____|___|_____________________
	                           4      10    18      0   1    2    3   5   6  7

so basically, i need a way to be able to search the dynamically generated arrays from my SQL statement, and match each array with the corresponding static array, and THEN match that resulting array with the packet array... fun huh? well, i've almost given myself a concussion from banging my head so many times on my desk from this hairy situation, but hey I'm trying :twisted:

lol, i know this may have been long and drawn out, but I feel i have to explain myself as best as I can so i can give you all some sort of idea of what i'm doing, and what direction i'm going. at least then maybe someone can see what i'm doing wrong, and point me in the correct direction.
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post by infolock »

UH OH! I THINK I MIGHT HAVE IT! lol, i'll let you know after i alter the code. been working on this stupid array problem for 2 days now, and if it's as simple as i have just come up with, i'm gonna scream...
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post by infolock »

ok, i think i am so close that i could split a pub hair if i tried.

however, there is only ONE PROBLEM!!! :-\

When I echo the result, it repeats it over and over (ie, if i echo tob[13], it will say "1:00pm 1:00pm 1:00pm......" on and on for as many times as it found any time within the hours of 1:00pm and 2:00pm. How do I tell it to stop this? lol If it's there, I just need it to say that one time, not 15. is there way to define how many values an array can hold? in other words, jsut oen occurance of the needed value, and after that, go to the next? here is the code that makes it possible ( i slapped my forhead a few times and yelled loud enough to wake the parents :D )

Code: Select all

<?php
while ($row = mysql_fetch_assoc ($result))
	{ 
		$time = $row['hour(time)'];
		if ($time == '0') $tob[0] = '12:00am';
		if ($time == '1') $tob[1] = '01:00am';
		if ($time == '2') $tob[2] = '02:00am';
		if ($time == '3') $tob[3] = '03:00am';
		if ($time == '4') $tob[4] = '04:00am';
		if ($time == '5') $tob[5] = '05:00am';
		if ($time == '6') $tob[6] = '06:00am';
		if ($time == '7') $tob[7] = '07:00am';
		if ($time == '8') $tob[8] = '08:00am';
		if ($time == '9') $tob[9] = '09:00am';
		if ($time == '10') $tob[10] = '10:00am';
		if ($time == '11') $tob[11] = '11:00am';
		if ($time == '12') $tob[12] = '12:00pm';
		if ($time == '13') $tob[13] = '01:00pm';
		if ($time == '14') $tob[14] = '02:00pm';
		if ($time == '15') $tob[15] = '03:00pm';
		if ($time == '16') $tob[16] = '04:00pm';
		if ($time == '17') $tob[17] = '05:00pm';
		if ($time == '18') $tob[18] = '06:00pm';
		if ($time == '19') $tob[19] = '07:00pm';
		if ($time == '20') $tob[20] = '08:00pm';
		if ($time == '21') $tob[21] = '09:00pm';
		if ($time == '22') $tob[22] = '10:00pm';
		if ($time == '23') $tob[23] = '11:00pm';
		echo $tob[12];
		echo '<br />';
?>
so, when I echo $tob[12], i get for a result :
12:00pm
12:00pm
12:00pm
12:00pm
12:00pm
12:00pm
12:00pm
12:00pm

because it found 8 entries of time being > 12 and < 13.

+++++ EDIT ++++++

WHOOOOOOOOOOOOOOOOOOTTTTTT, I'm about to solve the problem with sorting time with my packets values!!!!! WHOOOT :D
Paddy
Forum Contributor
Posts: 244
Joined: Wed Jun 11, 2003 8:16 pm
Location: Hobart, Tas, Aussie
Contact:

Post by Paddy »

Dude, use a switch statement.

And stay away from the sugar. :)
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post by infolock »

I'm reading up on the switch statement as we speak. in the meanwhile, i have another question :)

Take the following code for example :

Code: Select all

<?php

	$queries = array (
		"select sum(packets) from log where hour(time) = '".$tob[5]."'" );

                $bob = array();
                $yob = array();
	$bob[5] = mysql_query($queries[5]);
	$yob[5] = mysql_fetch_assoc($bob[5]);
	print_r($yob[5]);
?>
now, when i call the print_r($yob[5], i get a very wierd and ugly result...

it is, however, the CORRECT result! but it's still got all kinds of stuff in it i dno't need.

here is what the response from print_r looks like :
Array ( [sum(packets)] => 768059 )

any idea how i can just save this value ( on the fly of course ) to only the sum value ? ( in this case, i need $yob = '768059';

btw, can't help but excited when code works ;)
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

Code: Select all

list($yob[5]) = mysql_fetch_row($bob[5]);
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

Or you can use [php_man]mysql_result[/php_man]():

Code: Select all

$yob[5] = mysql_result($bob[5], 0);
Mac
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post by infolock »

awsome!!! just what i was looking for! :) thanks :D
Post Reply