Solved

shorten php code

Posted on 2011-02-23
28
325 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to use md5 hashing 3 29
Hacked File Timestamps 4 50
PHP warning 4 30
how to use a function in heredoc 5 23
Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
The viewer will learn how to count occurrences of each item in an array.
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…

867 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

20 Experts available now in Live!

Get 1:1 Help Now