?
Solved

shorten php code

Posted on 2011-02-23
28
Medium Priority
?
333 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 14
  • 9
  • 5
28 Comments
 
LVL 27

Accepted Solution

by:
Lukasz Chmielewski earned 1000 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
Secure Your WordPress Site: 5 Essential Approaches

WordPress is the web's most popular CMS, but its dominance also makes it a target for attackers. Our eBook will show you how to:

Prevent costly exploits of core and plugin vulnerabilities
Repel automated attacks
Lock down your dashboard, secure your code, and protect your users

 
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 1000 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

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
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.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Suggested Courses

752 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