Link to home
Start Free TrialLog in
Avatar of XK8ER
XK8ERFlag for United States of America

asked on

shorten php code

hello there,
I am using this code to get stats from mySQL as it is a 24hrs unique web counter for traffic..
it works fine.. im just trying to make the code look better and shorter..
$connection = GetMyConnection();
$kNow = time();

$sql = mysql_query("INSERT INTO counter (ipAddress, dateTime) VALUES ('".$raddr."', '".$kNow."')", $connection);
$hr24 = $kNow-3600*24;
$hr23 = $kNow-3600*23;
$hr22 = $kNow-3600*22;
$hr21 = $kNow-3600*21;
$hr20 = $kNow-3600*20;
$hr19 = $kNow-3600*19;
$hr18 = $kNow-3600*18;
$hr17 = $kNow-3600*17;
$hr16 = $kNow-3600*16;
$hr15 = $kNow-3600*15;
$hr14 = $kNow-3600*14;
$hr13 = $kNow-3600*13;
$hr12 = $kNow-3600*12;
$hr11 = $kNow-3600*11;
$hr10 = $kNow-3600*10;
$hr09 = $kNow-3600*9;
$hr08 = $kNow-3600*8;
$hr07 = $kNow-3600*7;
$hr06 = $kNow-3600*6;
$hr05 = $kNow-3600*5;
$hr04 = $kNow-3600*4;
$hr03 = $kNow-3600*3;
$hr02 = $kNow-3600*2;
$hr01 = $kNow-3600*1;
$KTotal = '0';


$sq2 = mysql_query("SELECT count(dateTime) as KTotal FROM counter", $connection);
$re3 = mysql_fetch_assoc($sq2) ;
$KTotal = number_format($re3['KTotal']);

$sq2 = mysql_query("SELECT count(dateTime) as KTotal FROM counter WHERE dateTime BETWEEN '".$hr24."' AND '".$hr23."'", $connection);
$re3 = mysql_fetch_assoc($sq2) ;
$hr24 = number_format($re3['KTotal']);

$sq2 = mysql_query("SELECT count(dateTime) as KTotal FROM counter WHERE dateTime BETWEEN '".$hr23."' AND '".$hr22."'", $connection);
$re3 = mysql_fetch_assoc($sq2) ;
$hr23 = number_format($re3['KTotal']);

$sq2 = mysql_query("SELECT count(dateTime) as KTotal FROM counter WHERE dateTime BETWEEN '".$hr22."' AND '".$hr21."'", $connection);
$re3 = mysql_fetch_assoc($sq2) ;
$hr22 = number_format($re3['KTotal']);

$sq2 = mysql_query("SELECT count(dateTime) as KTotal FROM counter WHERE dateTime BETWEEN '".$hr21."' AND '".$hr20."'", $connection);
$re3 = mysql_fetch_assoc($sq2) ;
$hr21 = number_format($re3['KTotal']);

$sq2 = mysql_query("SELECT count(dateTime) as KTotal FROM counter WHERE dateTime BETWEEN '".$hr20."' AND '".$hr19."'", $connection);
$re3 = mysql_fetch_assoc($sq2) ;
$hr20 = number_format($re3['KTotal']);

$sq2 = mysql_query("SELECT count(dateTime) as KTotal FROM counter WHERE dateTime BETWEEN '".$hr19."' AND '".$hr18."'", $connection);
$re3 = mysql_fetch_assoc($sq2) ;
$hr19 = number_format($re3['KTotal']);

$sq2 = mysql_query("SELECT count(dateTime) as KTotal FROM counter WHERE dateTime BETWEEN '".$hr18."' AND '".$hr17."'", $connection);
$re3 = mysql_fetch_assoc($sq2) ;
$hr18 = number_format($re3['KTotal']);

$sq2 = mysql_query("SELECT count(dateTime) as KTotal FROM counter WHERE dateTime BETWEEN '".$hr17."' AND '".$hr16."'", $connection);
$re3 = mysql_fetch_assoc($sq2) ;
$hr17 = number_format($re3['KTotal']);

$sq2 = mysql_query("SELECT count(dateTime) as KTotal FROM counter WHERE dateTime BETWEEN '".$hr16."' AND '".$hr15."'", $connection);
$re3 = mysql_fetch_assoc($sq2) ;
$hr16 = number_format($re3['KTotal']);

$sq2 = mysql_query("SELECT count(dateTime) as KTotal FROM counter WHERE dateTime BETWEEN '".$hr15."' AND '".$hr14."'", $connection);
$re3 = mysql_fetch_assoc($sq2) ;
$hr15 = number_format($re3['KTotal']);

$sq2 = mysql_query("SELECT count(dateTime) as KTotal FROM counter WHERE dateTime BETWEEN '".$hr14."' AND '".$hr13."'", $connection);
$re3 = mysql_fetch_assoc($sq2) ;
$hr14 = number_format($re3['KTotal']);

$sq2 = mysql_query("SELECT count(dateTime) as KTotal FROM counter WHERE dateTime BETWEEN '".$hr13."' AND '".$hr12."'", $connection);
$re3 = mysql_fetch_assoc($sq2) ;
$hr13 = number_format($re3['KTotal']);

$sq2 = mysql_query("SELECT count(dateTime) as KTotal FROM counter WHERE dateTime BETWEEN '".$hr12."' AND '".$hr11."'", $connection);
$re3 = mysql_fetch_assoc($sq2) ;
$hr12 = number_format($re3['KTotal']);

$sq2 = mysql_query("SELECT count(dateTime) as KTotal FROM counter WHERE dateTime BETWEEN '".$hr11."' AND '".$hr10."'", $connection);
$re3 = mysql_fetch_assoc($sq2) ;
$hr11 = number_format($re3['KTotal']);

$sq2 = mysql_query("SELECT count(dateTime) as KTotal FROM counter WHERE dateTime BETWEEN '".$hr10."' AND '".$hr09."'", $connection);
$re3 = mysql_fetch_assoc($sq2) ;
$hr10 = number_format($re3['KTotal']);

$sq2 = mysql_query("SELECT count(dateTime) as KTotal FROM counter WHERE dateTime BETWEEN '".$hr09."' AND '".$hr08."'", $connection);
$re3 = mysql_fetch_assoc($sq2) ;
$hr09 = number_format($re3['KTotal']);

$sq2 = mysql_query("SELECT count(dateTime) as KTotal FROM counter WHERE dateTime BETWEEN '".$hr08."' AND '".$hr06."'", $connection);
$re3 = mysql_fetch_assoc($sq2) ;
$hr08 = number_format($re3['KTotal']);

$sq2 = mysql_query("SELECT count(dateTime) as KTotal FROM counter WHERE dateTime BETWEEN '".$hr07."' AND '".$hr06."'", $connection);
$re3 = mysql_fetch_assoc($sq2) ;
$hr07 = number_format($re3['KTotal']);

$sq2 = mysql_query("SELECT count(dateTime) as KTotal FROM counter WHERE dateTime BETWEEN '".$hr06."' AND '".$hr05."'", $connection);
$re3 = mysql_fetch_assoc($sq2) ;
$hr06 = number_format($re3['KTotal']);

$sq2 = mysql_query("SELECT count(dateTime) as KTotal FROM counter WHERE dateTime BETWEEN '".$hr05."' AND '".$hr04."'", $connection);
$re3 = mysql_fetch_assoc($sq2) ;
$hr05 = number_format($re3['KTotal']);

$sq2 = mysql_query("SELECT count(dateTime) as KTotal FROM counter WHERE dateTime BETWEEN '".$hr04."' AND '".$hr03."'", $connection);
$re3 = mysql_fetch_assoc($sq2) ;
$hr04 = number_format($re3['KTotal']);

$sq2 = mysql_query("SELECT count(dateTime) as KTotal FROM counter WHERE dateTime BETWEEN '".$hr03."' AND '".$hr02."'", $connection);
$re3 = mysql_fetch_assoc($sq2) ;
$hr03 = number_format($re3['KTotal']);

$sq2 = mysql_query("SELECT count(dateTime) as KTotal FROM counter WHERE dateTime BETWEEN '".$hr02."' AND '".$hr01."'", $connection);
$re3 = mysql_fetch_assoc($sq2) ;
$hr02 = number_format($re3['KTotal']);

$sq2 = mysql_query("SELECT count(dateTime) as KTotal FROM counter WHERE dateTime > '".$hr01."'", $connection);
$re3 = mysql_fetch_assoc($sq2) ;
$hr01 = number_format($re3['KTotal']);

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Lukasz Chmielewski
Lukasz Chmielewski
Flag of Poland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Remove line 20
//    $hr[$i] = $kNow-3600*$i;
Avatar of XK8ER

ASKER

im getting this..

Notice: Undefined variable: hr
Notice: Undefined offset: 3
Notice: Undefined offset: 4
etc..
Add $hr = array()

<?php
$hr = array();
$connection = GetMyConnection();
$kNow = time();

$sql = mysql_query("INSERT INTO counter (ipAddress, dateTime) VALUES ('".$raddr."', '".$kNow."')", $connection);
for($i=1;$i<=24;$i++){
    $hr[$i] = $kNow-3600*$i;
}
$KTotal = '0';

$sq2 = mysql_query("SELECT count(dateTime) as KTotal FROM counter", $connection);
$re3 = mysql_fetch_assoc($sq2) ;
$KTotal = number_format($re3['KTotal']);

for($i=2;$i<=24;$i++){
    $sq2 = mysql_query("SELECT count(dateTime) as KTotal FROM counter WHERE dateTime BETWEEN '".$hr[$i]."' AND '".$hr[$i-1]."'", $connection);
    $re3 = mysql_fetch_assoc($sq2) ;
    $hr[$i] = number_format($re3['KTotal']);

    $hr[$i] = $kNow-3600*$i;
}


$sq2 = mysql_query("SELECT count(dateTime) as KTotal FROM counter WHERE dateTime > '".$hr01."'", $connection);
$re3 = mysql_fetch_assoc($sq2) ;
$hr[0] = number_format($re3['KTotal']);
?>

Open in new window

Also, line 25, replace $hr01 with $hr[1]
you may need to change his line 24 to:
$sq2 = mysql_query("SELECT count(dateTime) as KTotal FROM counter WHERE dateTime > '".$hr[1]."'", $connection);

Open in new window


as $hr01 no longer exists...
good catch, Roads.. Beat me w/ the refresh... :)
It was just a matter of seconds ;)
Avatar of XK8ER

ASKER

for some reason is not giving me the same results as the original code I posted..
Avatar of XK8ER

ASKER

the first record shows fine and the total.. but the rest are displayed as timestamps
which first record? hr24 or hr01?
Avatar of XK8ER

ASKER

yes hour 1
so when you echo out $hr[2] through $hr[24], what do you get? as an example...
Avatar of XK8ER

ASKER

this is what I have as of now..

 
252   507   751    1,018    0   0    0    0    0

but it should be like this

253       254       243       268       198       207       253       253       0       0       0

its like it sums the first then with the second then with the third.. etc..
$connection = GetMyConnection();
$kNow = time();
$KTotal = '0';
$hr = array();

$sq2 = mysql_query("SELECT count(dateTime) as KTotal FROM counter", $connection);
$re3 = mysql_fetch_assoc($sq2) ;
$KTotal = number_format($re3['KTotal']);

for($i=0;$i<=64;$i++){
		$hr[$i] = $kNow-3600*$i; 
}		

for($i=1;$i<=24;$i++){
		$hr[$i] = $kNow-3600*$i; 
    $sq2 = mysql_query("SELECT count(dateTime) as KTotal FROM counter WHERE dateTime BETWEEN '".$hr[$i]."' AND '".$hr[$i-1]."'", $connection);
    $re3 = mysql_fetch_assoc($sq2) ;
    $hr[$i] = number_format($re3['KTotal']);
}

$sq2 = mysql_query("SELECT count(dateTime) as KTotal FROM counter WHERE dateTime > '".$hr[1]."'", $connection);
$re3 = mysql_fetch_assoc($sq2) ;
$hr[0] = number_format($re3['KTotal']);

Open in new window

so, your lines 10 through 12, what are you accomplishing? you're doing the same thing in row 15...

I'm not sure why you're doing it twice....
Avatar of XK8ER

ASKER

well I have been trying to get your code to work because its not doing the same thing as the original code that I posted.
try this:
<?php
$connection = GetMyConnection();
$kNow = time();
$hr = array();

$sql = mysql_query("INSERT INTO counter (ipAddress, dateTime) VALUES ('".$raddr."', '".$kNow."')", $connection);

$sq2 = mysql_query("SELECT count(dateTime) as KTotal FROM counter", $connection);
$re3 = mysql_fetch_assoc($sq2) ;
$KTotal = number_format($re3['KTotal']);

for($i=2;$i<=24;$i++){
	$hr[$i] = $kNow-3600*$i;
	$sq2 = mysql_query("SELECT count(dateTime) as KTotal FROM counter WHERE dateTime BETWEEN '".$hr[$i]."' AND '".$hr[$i-1]."'", $connection);
	$re3 = mysql_fetch_assoc($sq2) ;
	$hr[$i] = number_format($re3['KTotal']);
}

$sq2 = mysql_query("SELECT count(dateTime) as KTotal FROM counter WHERE dateTime > '".$hr[1]."'", $connection);
$re3 = mysql_fetch_assoc($sq2) ;
$hr[0] = number_format($re3['KTotal']);
?>

Open in new window

wait... that's not going to do it.... the $hr[$1-1] in the query isn't going to be what you want... jas...
wait... yeah it will... i'm not thinking straight... try the code that i posted up there... :)
Avatar of XK8ER

ASKER

its not showing any records.. and gives this..

Notice: Undefined offset: 1

I think that's because we haven't set $hr[1] yet...

<?php
$hr = array();
$connection = GetMyConnection();
$kNow = time();

$sql = mysql_query("INSERT INTO counter (ipAddress, dateTime) VALUES ('".$raddr."', '".$kNow."')", $connection);
for($i=1;$i<=24;$i++){
    $hrCompare[$i] = $kNow-3600*$i;
}

$sq2 = mysql_query("SELECT count(dateTime) as KTotal FROM counter", $connection);
$re3 = mysql_fetch_assoc($sq2) ;
$KTotal = number_format($re3['KTotal']);

for($j=2;$j<=24;$j++){
    $sq2 = mysql_query("SELECT count(dateTime) as KTotal FROM counter WHERE dateTime BETWEEN '".$hrCompare[$j]."' AND '".$hrCompare[$j-1]."'", $connection);
    $re3 = mysql_fetch_assoc($sq2) ;
    $hr[$j] = number_format($re3['KTotal']);
}

$sq2 = mysql_query("SELECT count(dateTime) as KTotal FROM counter WHERE dateTime > '".$hr[1]."'", $connection);
$re3 = mysql_fetch_assoc($sq2) ;
$hr[0] = number_format($re3['KTotal']);
?>

Open in new window

shoot -- change line 24 to:
$sq2 = mysql_query("SELECT count(dateTime) as KTotal FROM counter WHERE dateTime > '".$hrCompare[1]."'", $connection);
$re3 = mysql_fetch_assoc($sq2) ;
$hr[0] = number_format($re3['KTotal']);
?>

Open in new window

Avatar of XK8ER

ASKER

ok, now this Is more like it.. but hr1 shows

Notice: Undefined offset: 1

the rest show

233       264       265       216       203       239       159       0       0       0
final line to:
$hr[1] = number_format($re3['KTotal']);

Open in new window


(there was no hr[0] set... :)
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of XK8ER

ASKER

perfection.. thank you so much!!
No problem...  glad to help, and good luck with the rest of your project!