Solved

shorten php code

Posted on 2011-02-23
28
324 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:XK8ER
  • 14
  • 9
  • 5
28 Comments
 
LVL 27

Accepted Solution

by:
Lukasz Chmielewski earned 250 total points
Comment Utility
Maybe like this, using an array

<?php
$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

0
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
Comment Utility
Remove line 20
//    $hr[$i] = $kNow-3600*$i;
0
 
LVL 1

Author Comment

by:XK8ER
Comment Utility
im getting this..

Notice: Undefined variable: hr
Notice: Undefined offset: 3
Notice: Undefined offset: 4
etc..
0
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
Comment Utility
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

0
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
Comment Utility
Also, line 25, replace $hr01 with $hr[1]
0
 
LVL 2

Expert Comment

by:TimBare
Comment Utility
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...
0
 
LVL 2

Expert Comment

by:TimBare
Comment Utility
good catch, Roads.. Beat me w/ the refresh... :)
0
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
Comment Utility
It was just a matter of seconds ;)
0
 
LVL 1

Author Comment

by:XK8ER
Comment Utility
for some reason is not giving me the same results as the original code I posted..
0
 
LVL 1

Author Comment

by:XK8ER
Comment Utility
the first record shows fine and the total.. but the rest are displayed as timestamps
0
 
LVL 2

Expert Comment

by:TimBare
Comment Utility
which first record? hr24 or hr01?
0
 
LVL 1

Author Comment

by:XK8ER
Comment Utility
yes hour 1
0
 
LVL 2

Expert Comment

by:TimBare
Comment Utility
so when you echo out $hr[2] through $hr[24], what do you get? as an example...
0
 
LVL 1

Author Comment

by:XK8ER
Comment Utility
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

0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 2

Expert Comment

by:TimBare
Comment Utility
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....
0
 
LVL 1

Author Comment

by:XK8ER
Comment Utility
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.
0
 
LVL 2

Expert Comment

by:TimBare
Comment Utility
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

0
 
LVL 2

Expert Comment

by:TimBare
Comment Utility
wait... that's not going to do it.... the $hr[$1-1] in the query isn't going to be what you want... jas...
0
 
LVL 2

Expert Comment

by:TimBare
Comment Utility
wait... yeah it will... i'm not thinking straight... try the code that i posted up there... :)
0
 
LVL 1

Author Comment

by:XK8ER
Comment Utility
its not showing any records.. and gives this..

Notice: Undefined offset: 1

0
 
LVL 2

Expert Comment

by:TimBare
Comment Utility
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

0
 
LVL 2

Expert Comment

by:TimBare
Comment Utility
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

0
 
LVL 1

Author Comment

by:XK8ER
Comment Utility
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
0
 
LVL 2

Expert Comment

by:TimBare
Comment Utility
final line to:
$hr[1] = number_format($re3['KTotal']);

Open in new window


0
 
LVL 2

Expert Comment

by:TimBare
Comment Utility
(there was no hr[0] set... :)
0
 
LVL 2

Assisted Solution

by:TimBare
TimBare earned 250 total points
Comment Utility
If this fixes it and you're happy w/ the answer, be sure to give Roads points, too... He helped out a ton...
0
 
LVL 1

Author Comment

by:XK8ER
Comment Utility
perfection.. thank you so much!!
0
 
LVL 2

Expert Comment

by:TimBare
Comment Utility
No problem...  glad to help, and good luck with the rest of your project!
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now