Page 1 of 1

[SOLVED] $sql = "SELECT packet FROM log WHERE time

Posted: Tue Nov 11, 2003 12:14 am
by infolock
hello. i've attempted to post this a few times, but each time i do, i think i solve the problem and i end up just getting more frustrating results :twisted:

so, I'm officially at a standstill and I need some help with a query i'm trying to perform.


a little background :

currently, my script parses a log file, and dumps it into a mysql database so that i can query it easier, and always have that data there (instead of having to parse the log file each and every time i want to run a report ).

So, right now i'm just trying to sort my packets based on when they were sent. I'm wanting to add up all the packets sent between each hour of the day ( ie, between 12:00am - 01:00am total packets =$a, 01:00am - 02:00am total packets = $b, etc ).

I currently have written a rather extensive loop that I have been biting nails with to get to work, but no matter how i arrange the loop, all i get back is a blank page... I was hoping someone could shed some light on where i'm going wrong. I'd really appreciate it..

without further adue, here is what i'm doing :

Code: Select all

<?php

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

$this = "Select * from log";
$some = mysql_query($this);
$numrows = mysql_num_rows($some);

$ctime = array('00:00:00','01:00:00','02:00:00','03:00:00','04:00:00','05:00:00','06:00:00','07:00:00','08:00:00','09:00:00','10:00:00',
'11:00:00','12:00:00','13:00:00','14:00:00','15:00:00','16:00:00','17:00:00','18:00:00','19:00:00',
'20:00:00','21:00:00','22:00:00','23:00:00','24:00:00');

$i=0;
$ii=count($numrows);
$b=1;
$bb=count($numrows);

for ($i=0; $i < $ii; $i++)
{
	foreach($ctime as $time)
	{
		for ($b=1; $b<$bb; $b++)
		{

// $i will stand for the first array value of $ctime and $b will stand for the second.

			$bob = "SELECT packets FROM log WHERE time > '".$time[$i]."' and time < '".$time[$b]."'";
			$result = mysql_query($bob);
			echo $result;
			echo '<br />';
		}
	}
}


?>

i've tried taking out the incrimenting variables and sticking with just the "foreach" statement, tried it with just the inc variables and no foreach (replacing $time with $ctime of course ), and on and on.

all i want to do is grab data from a table that may look like this :

Code: Select all

time        |    Packets Sent
-------------|-------------------
00:40:44   |         28348
00:50:34   |         39493
00:55:00   |         34834
02:41:24   |           8384
05:10:44   |        101000
00:15:44   |          40040
query each time ( like above ), and be able to spit out :

between 12:00am and 1:00am
sent 102675 packets

between 1:00am and 2:00am
Sent - 0 packets

and so on .


can anyone help this tired young man that's about to lose his mind ? 8O

Posted: Tue Nov 11, 2003 3:29 am
by infolock
and just be clear on something, yes, i do know i can write 24 SQL statements ( which i have already done... ), i just am trying to figure out how to do all those queries in just one while loop, or for loop. curiosity killed the cat they say, but i believe curiosity gave birth to knowledge.

Posted: Tue Nov 11, 2003 5:54 am
by JAM
I just had an idea about something like this untested code

Code: Select all

select packets, hour(time) FROM log group by hour(time) order by hour(time) desc
I think that by using group by (in one way or the other) you might get some better results as well as lessen the amount of queries to the db.

Posted: Tue Nov 11, 2003 2:27 pm
by infolock
thanks jam. i'll try that when i get home.

Posted: Tue Nov 11, 2003 3:57 pm
by infolock
ok, let me start over here since i made so many mistakes trying to explain this lol...

as you can see above, i have a lot of different time stamps. each time may or maynot have a packet value, but what i'm wanting to do is group all packets sent within each hour of the day, average up those values, and then post them in a readable format on my screen.

I did, however, use your code and i did get some results, but only 9. there are about 108 total results that should have been posted, but again i'm only getting nine. also, i'm unable to retrieve the time stamp for each result.

here is how i structured the code, maybe i missed something :

Code: Select all

<php?

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


	$sql = "select packets, hour(time) FROM log group by hour(time) order by hour(time) desc";
	$result = mysql_query($sql) or die(MySQL_Error());
	$num_results = mysql_num_rows($result);
	
	for ($i=0; $i < $num_results; $i++)
	{
		$row = mysql_fetch_assoc($result);
		echo '<p><strong>', ($i+1).'. Packets Sent : ';
		echo $row['packets'];
		echo '<br />';
		echo 'Time : ';
		echo $row['time'];
		echo '</strong></p>';
	}
?>
however, my results look like this :

Code: Select all

1. Packets Sent : 347667
Time : 

2. Packets Sent : 101749
Time : 

3. Packets Sent : 344271
Time : 

4. Packets Sent : 0
Time : 

5. Packets Sent : 349418
Time : 

6. Packets Sent : 95160
Time : 

7. Packets Sent : 20806
Time : 

8. Packets Sent : 343523
Time : 

9. Packets Sent : 20849
Time :
as you can see, the time field ( which should post which hour it is reporting )is blank, neglects all the other hours from which it found nothing, and i only get 1 record per hour. i need to get all records per hour.

thanks for your help so far as you have gotten further then i did :) lol, just hope there is an easy solution to this mind boggling issue. 8O



:::::::::: edit ::::::::::

found out the problem with the time. i wasn't putting hour(time) within my $row call... solved that problem, but still unsure what's going on with the packet results.

Posted: Tue Nov 11, 2003 5:07 pm
by infolock
i figured it out. thanks jam :)


the answer was so easy i almost slapped myself...

here you go :

Code: Select all

<?php

	mysql_connect ('localhost','username','password');
	mysql_select_db ('logparser');


	$sql = "select avg(packets), hour(time) FROM log group by hour(time) order by hour(time) desc";
	$result = mysql_query($sql) or die(MySQL_Error());
	$num_results = mysql_num_rows($result);

	$row = mysql_fetch_assoc($result);
	mysql_data_seek ($result, 0);
	echo '<strong><p>';
	while ($row = mysql_fetch_assoc ($result))
	{
		echo 'Time : ';
		echo $row['hour(time)'];
		echo '<br />';
		echo 'Average Packets : <br />';
		echo $row['avg(packets)'];
		echo '<br /> <br />';
	}
	echo '</strong></p>';

/*  didn't even need this stuff  :-\

                 for ($i=0; $i < $num_results; $i++)
	{
		$row = mysql_fetch_assoc($result);
		echo '<p><strong>', ($i+1).'. Time : ';
		echo $row['hour(time)'];
		echo '<br />';
		echo 'Packets Sent : ';
		mysql_data_seek ($result, 0);
		while ($row = mysql_fetch_assoc ($result))
		{
			echo $row['packets'];
			echo '<br />';
		}

		echo '</strong></p>';
	}
*/
?>

Posted: Wed Nov 12, 2003 11:16 am
by JAM
Glad it worked ;)