Solved

shorten php code

Posted on 2011-02-23
28
326 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
ID: 34964147
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
ID: 34964152
Remove line 20
//    $hr[$i] = $kNow-3600*$i;
0
 
LVL 1

Author Comment

by:XK8ER
ID: 34964244
im getting this..

Notice: Undefined variable: hr
Notice: Undefined offset: 3
Notice: Undefined offset: 4
etc..
0
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 34964265
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
ID: 34964293
Also, line 25, replace $hr01 with $hr[1]
0
 
LVL 2

Expert Comment

by:TimBare
ID: 34964347
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
ID: 34964355
good catch, Roads.. Beat me w/ the refresh... :)
0
 
LVL 27

Expert Comment

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

Author Comment

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

Author Comment

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

Expert Comment

by:TimBare
ID: 34966090
which first record? hr24 or hr01?
0
 
LVL 1

Author Comment

by:XK8ER
ID: 34966093
yes hour 1
0
 
LVL 2

Expert Comment

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

Author Comment

by:XK8ER
ID: 34966139
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
 
LVL 2

Expert Comment

by:TimBare
ID: 34966166
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
ID: 34966178
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
ID: 34966207
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
ID: 34966216
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
ID: 34966227
wait... yeah it will... i'm not thinking straight... try the code that i posted up there... :)
0
 
LVL 1

Author Comment

by:XK8ER
ID: 34966260
its not showing any records.. and gives this..

Notice: Undefined offset: 1

0
 
LVL 2

Expert Comment

by:TimBare
ID: 34966288
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
ID: 34966292
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
ID: 34966320
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
ID: 34966329
final line to:
$hr[1] = number_format($re3['KTotal']);

Open in new window


0
 
LVL 2

Expert Comment

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

Assisted Solution

by:TimBare
TimBare earned 250 total points
ID: 34966338
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
ID: 34966351
perfection.. thank you so much!!
0
 
LVL 2

Expert Comment

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

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article discusses four methods for overlaying images in a container on a web page
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

775 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