Hi there mates on my site www.myhotimage.com i've a report section in admin cp that makes me know how much image views and bandwidth used did the site get today,yesterday and since it was started now i want an option to know the imageviews and bandwidth used in the current whole month there is a box that should make a range of dates that i select to know the stats of this period but that box isn't working here is the page of code i'm talking about // get date ranges $link = mysql_connect($db_server, $db_user, $db_password) or die("Could not connect"); mysql_select_db($db_name) or die("Could not select database"); $query = "SELECT substring(timestamp, 1, 8) as ts from imagehits order by timestamp desc"; $result = mysql_query($query) or die("Query failed"); while ($line = mysql_fetch_array($result)) { $dates[$line[ts]] = ""; } mysql_free_result($result); $date = $_GET['date']; // get bandwidth if ($date == "") { $date = date("Ymd"); } if ($date == "all") { $report = "All Dates"; $query1 = "select sum(kb) as ttl from imagehits"; $query2 = "select count(*) as ct from imagehits"; } else { $report = substr($date, 4, 2) . "/" . substr($date, 6, 2) . "/" . substr($date, 0, 4); $start = $date . "000000"; $end = $date . "235959"; $query1 = "select sum(kb) as ttl from imagehits where timestamp >= $start and timestamp <= $end"; $query2 = "select count(*) as ct from imagehits where timestamp >= $start and timestamp <= $end"; } $result = mysql_query($query1) or die("Query failed."); $bandwidth = 0; while ($line = mysql_fetch_array($result)) { $bandwidth = $line[ttl]; } mysql_free_result($result); $result = mysql_query($query2) or die("Query failed."); $images = 0; while ($line = mysql_fetch_array($result)) { $images = $line[ct]; } mysql_close($link); ?> <span class="ctracks">STATISTICS</span><br> <br> <hr> <br> <form name="stats" action="<?= $_SERVER['PHP_SELF'] ?>" method="post" style="margin-bottom: 0px;"> <div align="center"><a href="<?= $_SERVER['PHP_SELF'] ?>?date=<?= date("Ymd") ?>" class="submenu">Today</a> - <a href="<?= $_SERVER['PHP_SELF'] ?>?date=<?= date("Ymd", mktime(0, 0, 0, date("m"), date("d")-1, date("Y"))) ?>" class="submenu">Yesterday</a> - Date: <select name="date" size="1"><option value="" selected></option><? foreach ($dates as $dt1 => $dt2) { ?><option value="<?= substr($dt1, 0, 4) . substr($dt1, 4, 2) . substr($dt1, 6, 2) ?>"><?= substr($dt1, 4, 2) . "/" . substr($dt1, 6, 2) . "/" . substr($dt1, 0, 4) ?></option><? } ?></select> <input type="submit" name="go" value="go" style="font-size: smaller;"> - <a href="<?= $_SERVER['PHP_SELF'] ?>?date=all" class="submenu">Forever</a></div> </form> <br> <br> <div align="center">Report for <?= $report ?></div><br> <table border="0" align="Center" cellpadding="4" cellspacing="0"> <tr> <td align="right"><strong>Image Views:</strong></td> <td align="left"><?= number_format($images) ?></td> </tr> <tr> <? $bandwidthmb = ($bandwidth / 1048576) ; $bandwidthkb = ($bandwidth / 1024) ; ?> <td align="right"><strong>Bandwidth Used MB:</strong></td> <td align="left"><?= number_format($bandwidthmb) ?> MB</td> </tr><br><br><tr> <td align="right"><strong>Bandwidth Used KB:</strong></td> <td align="left"><?= number_format($bandwidthkb) ?> KB</td> </tr><br><br><tr> <td align="right"><strong>Bandwidth Used Byets:</strong></td> <td align="left"><?= number_format($bandwidth) ?> Byets</td> </tr> </table> PHP: now the "imagehits" table -- Table structure for table `imagehits` DROP TABLE IF EXISTS `imagehits`; CREATE TABLE `imagehits` ( `idx` bigint(20) NOT NULL auto_increment, `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `filename` varchar(200) NOT NULL default '', `referer` varchar(255) NOT NULL default '', `ip` varchar(15) NOT NULL default '', `kb` bigint(20) NOT NULL default '0', UNIQUE KEY `idx` (`idx`), KEY `filename` (`filename`) ) ENGINE=MyISAM AUTO_INCREMENT=399 DEFAULT CHARSET=latin1; PHP: sample of the table entries INSERT INTO `imagehits` (`idx`, `timestamp`, `filename`, `referer`, `ip`, `kb`) VALUES (10,'2006-09-02 03:26:06','5vV67529.jpg','','196.202.XX.XXX',3637)' PHP: this is a screenshot for the reporting section http://www.myhotimage.com/images/Nej24219.jpg would you please help me doing 2 things 1- making the dates dropdown menu list the last 7 days or something 2- adding an option for showing stats of the whole current month from day 1 till now thanks in advance - DP is the best
Well...turns out I was kind of bored this morning....here's what I came up with Its kind of long, so I attached the file too... Hopefully it works out for you! <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "[URL]http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd[/URL]"> <html xmlns="[URL]http://www.w3.org/1999/xhtml[/URL]"> <head> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" /> <title>Image Hit Statistics</title> <? function CreateDateSelect($Days) { //creates a select box with the last $Days number of days; $SelectBox = "<select name='date'>"; $StartDate = date("Y-m-d", strtotime("- $Days days")); $EndDate = date("Y-m-d"); $RunningDate = $StartDate; while($RunningDate < $EndDate) { $SelectBox.="<option value='$RunningDate'>$RunningDate</option>"; $RunningDate = date("Y-m-d", strtotime($RunningDate . " + 1 day")); } $SelectBox.="</select>"; return $SelectBox; } ?> </head> <body> <? if((isset($_GET['date'])) || (isset($_GET['month'])) || (isset($_GET['forever']))) { $ReportData = array(); //initialize report data array //initialize the query $Query = "SELECT COUNT(idx) AS imagect, SUM(kb) AS bandwith FROM imagehits"; if(isset($_GET['date'])) { //Append where string $Query.=" WHERE DATE(timestamp) = \"{$_GET['date']}\""; $ReportData['period'] = "Report for " . date("l, F jS, Y", strtotime($_GET['date'])) . ":"; } elseif(isset($_GET['month'])) { //Append where string $Query.=" WHERE MONTH(timestamp) = \"{$_GET['month']}\""; $ReportData['period'] = "Report for the month of " . date("F", mktime(0,0,0,$_GET['month'])).":"; } else { //Looking at forever doesn't require a where statement //Lets get the earliest date in the database $GetDate = "SELECT DATE(MIN(timestamp)) AS firstdate FROM imagehits"; $link = mysql_connect(HOST, USER, PASS) or die("Could not connect"); mysql_select_db(DB) or die("Could not select database"); $Result = mysql_query($GetDate) or die("Error executing query. MySQL Error: " . mysql_error()); $Row = mysql_fetch_assoc($Result); $ReportData['period'] = "Report data starting ". date("l, F jS, Y", strtotime($Row['firstdate'])) . ":"; mysql_close($link); unset($Result,$Row); } //Lets execute the query once and put it into an array $link = mysql_connect(HOST, USER, PASS) or die("Could not connect"); mysql_select_db(DB) or die("Could not select database"); $Result = mysql_query($Query) or die("Error executing query. MySQL Error: " . mysql_error()); $Row = mysql_fetch_assoc($Result); mysql_close($link); $ReportData['countofhits'] = $Row['imagect']; $ReportData['bw_kb'] = $Row['bandwith']; $ReportData['bw_mb'] = $Row['bandwith'] / 1024; $ReportData['bw_bytes'] = $Row['bandwith'] * 1024; } ?> <!--form section--> <div align='center'> <form name='reports' action='<? echo $_SERVER['PHP_SELF']; ?>' method='GET'> <a href="<? echo $_SERVER['PHP_SELF']; ?>?date=<? echo date("Ymd"); ?>">Today</a> - <a href="<? echo $_SERVER['PHP_SELF']; ?>?date=<? echo date("Ymd", strtotime("-1 day")); ?>">Yesterday</a> - <a href="<? echo $_SERVER['PHP_SELF']; ?>?month=<? echo date("m"); ?>">This Month</a> - Select a Date: <? echo CreateDateSelect(7); ?> <input type='submit' value='Go' name='submit' /> - <a href="<? echo $_SERVER['PHP_SELF']; ?>?forever=true">All Time</a> </form> </div> <!--Report Section--> <? if(isset($ReportData)) { ?> <table border="0" align="Center" cellpadding="4" cellspacing="0"> <tr> <td colspan='2'><? echo $ReportData['period']; ?></td> </tr> <tr> <td><strong>Image Hits:</strong></td> <td><? echo $ReportData['countofhits']; ?></td> </tr> <tr> <td><strong>Bandwith (MB):</strong></td> <td><? echo number_format($ReportData['bw_mb'], 2); ?></td> </tr> <tr> <td><strong>Bandwith (KB):</strong></td> <td><? echo number_format($ReportData['bw_kb'], 0); ?></td> </tr> <tr> <td><strong>Bandwith (Bytes):</strong></td> <td><? echo number_format($ReportData['bw_bytes'], 0); ?></td> </tr> </table> <? } ?> </body> </html> Code (markup):
man, thanks alot for caring to do this for me i had to do some edit to make the database connect however, the dropdown box isn't working still functions showing "today " and "yesterday" aren't working ( the link for them shows the right date but clicking on it shows no stats ) functions showing "this month" and "all time " are working fine this means the database connection is fine this is the code i'm using now, please see what to do to make "today" and "yesterday" and the dropdown box work ( i think they are associated with today's date ) $link = mysql_connect($db_server, $db_user, $db_password) or die("Could not connect"); mysql_select_db($db_name) or die("Could not select database"); function CreateDateSelect($Days) { //creates a select box with the last $Days number of days; $SelectBox = "<select name='date'>"; $StartDate = date("Y-m-d", strtotime("- $Days days")); $EndDate = date("Y-m-d"); $RunningDate = $StartDate; while($RunningDate < $EndDate) { $SelectBox.="<option value='$RunningDate'>$RunningDate</option>"; $RunningDate = date("Y-m-d", strtotime($RunningDate . " + 1 day")); } $SelectBox.="</select>"; return $SelectBox; } if((isset($_GET['date'])) || (isset($_GET['month'])) || (isset($_GET['forever']))) { $ReportData = array(); //initialize report data array //initialize the query $Query = "SELECT COUNT(idx) AS imagect, SUM(kb) AS bandwith FROM imagehits"; if(isset($_GET['date'])) { //Append where string $Query.=" WHERE DATE(timestamp) = \"{$_GET['date']}\""; $ReportData['period'] = "Report for " . date("l, F jS, Y", strtotime($_GET['date'])) . ":"; } elseif(isset($_GET['month'])) { //Append where string $Query.=" WHERE MONTH(timestamp) = \"{$_GET['month']}\""; $ReportData['period'] = "Report for the month of " . date("F", mktime(0,0,0,$_GET['month'])).":"; } else { //Looking at forever doesn't require a where statement //Lets get the earliest date in the database $GetDate = "SELECT DATE(MIN(timestamp)) AS firstdate FROM imagehits"; $link = mysql_connect($db_server, $db_user, $db_password) or die("Could not connect"); mysql_select_db($db_name) or die("Could not select database"); $Result = mysql_query($GetDate) or die("Error executing query. MySQL Error: " . mysql_error()); $Row = mysql_fetch_assoc($Result); $ReportData['period'] = "Report data starting ". date("l, F jS, Y", strtotime($Row['firstdate'])) . " Till Today"; mysql_close($link); unset($Result,$Row); } //Lets execute the query once and put it into an array $link = mysql_connect($db_server, $db_user, $db_password) or die("Could not connect"); mysql_select_db($db_name) or die("Could not select database"); $Result = mysql_query($Query) or die("Error executing query. MySQL Error: " . mysql_error()); $Row = mysql_fetch_assoc($Result); $ReportData['countofhits'] = $Row['imagect']; $ReportData['bw_gb'] = $Row['bandwith'] / 1073741824; $ReportData['bw_kb'] = $Row['bandwith'] / 1024; $ReportData['bw_mb'] = $Row['bandwith'] / 1048576; $ReportData['bw_bytes'] = $Row['bandwith']; } mysql_close($link); ?> <!--form section--> <div align='center'> <form name='reports' action='<? echo $_SERVER['PHP_SELF']; ?>' method='GET'> <a href="<? echo $_SERVER['PHP_SELF']; ?>?date=<? echo date("Ymd"); ?>">Today</a> - <a href="<? echo $_SERVER['PHP_SELF']; ?>?date=<? echo date("Ymd", strtotime("-1 day")); ?>">Yesterday</a> - <a href="<? echo $_SERVER['PHP_SELF']; ?>?month=<? echo date("m"); ?>">This Month</a> - Select a Date: <? echo CreateDateSelect(7); ?> <input type='submit' value='Go' name='submit' /> - <a href="<? echo $_SERVER['PHP_SELF']; ?>?forever=true">All Time</a> </form> </div> <!--Report Section--> <? if(isset($ReportData)) { ?> <table border="0" align="Center" cellpadding="4" cellspacing="0"> <tr> <td colspan='2'><? echo $ReportData['period']; ?></td> </tr> <tr> <td><strong>Image Hits:</strong></td> <td><? echo $ReportData['countofhits']; ?> Hits</td> </tr> <tr> <td><strong>Bandwith (GB):</strong></td> <td><? echo number_format($ReportData['bw_gb'], 2); ?> GB</td> </tr> <tr> <td><strong>Bandwith (MB):</strong></td> <td><? echo number_format($ReportData['bw_mb'], 2); ?> MB</td> </tr> <tr> <td><strong>Bandwith (KB):</strong></td> <td><? echo number_format($ReportData['bw_kb'], 0); ?> KB</td> </tr> <tr> <td><strong>Bandwith (Bytes):</strong></td> <td><? echo number_format($ReportData['bw_bytes'], 0); ?> B</td> </tr> </table> Code (markup):
Hey, no problem! Caught me on a kind of off day, I had three choices, do this script, change the oil in my truck, and steam clean the carpets....glad I can help. I copied and pasted the exact code you placed up. I included your database connection variables but changed them to match my parameters... You can view the page at www.geft-online.org/test/test.php Is there a chance I can get you to post a hot link to it or is it password protected? The only thing I can think of has to do with the MySQL query itself, what version of MySQL are you using? If you're using a MySQL version prior to 4.1.1, the DATE() Function is not included. If this is the case we'll have to figure something out.
Replace this: if(isset($_GET['date'])) { //Append where string $Query.=" WHERE DATE(timestamp) = \"{$_GET['date']}\""; $ReportData['period'] = "Report for " . date("l, F jS, Y", strtotime($_GET['date'])) . ":"; } PHP: with: if(isset($_GET['date'])) { //Append where string $Query.=" WHERE DATE_FORMAT(timestamp, '%Y%m%d') = \"{$_GET['date']}\""; $ReportData['period'] = "Report for " . date("l, F jS, Y", strtotime($_GET['date'])) . ":"; } PHP: Lets not use the DATE function, but instead use the DATE FORMAT function. The DATE function returns 'yyyy-mm-dd' and we we're comparing it to 'yyyymmdd', maybe this is not working in your version. MySQL 5 appears to make the distinction, but maybe not version 4......just a guess though.... As far as the drop down date select box. Is there anything showing up? maybe an empty box, maybe an empty box with 7 lines? Can you check the html source code using the view source and paste it here?
Okay, think I got the drop down figured out. I loaded it on a PHP4 server at work, and it didnt work...so...I think this should fix it. Replace this line in the CreateDateSelect Function: $StartDate = date("Y-m-d", strtotime("- $Days days")); PHP: With this one: $StartDate = date("Y-m-d", strtotime("-$Days days")); PHP: Apparently the space between the "-" character and the $Days variable threw it off in PHP4, but works in PHP5....sloppy me, sorry! Do the last couple of changes, and let me know the status!
Okay, I did some more testing. PHP Version 4.3.9, MySQL 4.0.21. I had to change the query in the final else statement (where we handle the forever view) to this: $GetDate = "SELECT DATE_FORMAT(MIN(timestamp), '%Y-%m-%d') AS firstdate FROM imagehits"; PHP: MySQL 4.0 does not handle the DATE() function, so I used the DATE_FORMAT() function. You probably won't have this problem, but just in case you do, here is something to try. Hope this helps, and let me know the results.
after doing those changes, the "today" and "yesterday" work fine thanks to u now the only thing remaining is the drop down box it shows the dates of the last 7 days but when i select a date (15) then click "go" 1- the date on the box is reverted to the 1st day of the 7 ( currently 12 ) but the stats say Report for Friday, September 15th, 2006: 2- it doesn't show stats for that date also i hope i'm not bothering u with my requests note:i'm satisfied with the results so far
Okay, thanks for the info...not a problem, I havent been too busy...Although, I've got to go flying so this poor student can get his Instrument rating...so, It'll be a few hours before I can look into this. I'll post back when I find something out. #1 is an easy fix, #2 might be a little different... When you use the drop down box, does it actually display the heading: "Report for Friday, September 15th, 2006" and not any information below that, or does it display all the blocks but include 0's with everything, or maybe display nothing at all? I think we're getting closer!!
Okay, to fix #1, replace the function with this: function CreateDateSelect($Days) { //creates a select box with the last $Days number of days; $SelectBox = "<select name='date'>"; $StartDate = date("Y-m-d", strtotime("-$Days days")); $EndDate = date("Y-m-d"); $RunningDate = $StartDate; while($RunningDate < $EndDate) { $SelectBox.="<option value='$RunningDate'"; if($RunningDate == $_GET['date']) $SelectBox.=" selected='selected'"; $SelectBox.=">$RunningDate</option>"; $RunningDate = date("Y-m-d", strtotime($RunningDate . " + 1 day")); } $SelectBox.="</select>"; return $SelectBox; } PHP:
Okay, I've been trying to figure out what could be causing the other problem...can you post the link or no? How about another screen shot? or the html source code for the page? Thanks!
sorry for being late in replying, but i lost my connection for about 10 days and now back I used the code in your post #15 and the select box now shows the right date but no stats here is a screenshot http://www.myhotimage.com/viewimage.php?file=/images/Jqx17297.jpg this is the latest code used atm <? $link = mysql_connect($db_server, $db_user, $db_password) or die("Could not connect"); mysql_select_db($db_name) or die("Could not select database"); function CreateDateSelect($Days) { //creates a select box with the last $Days number of days; $SelectBox = "<select name='date'>"; $StartDate = date("Y-m-d", strtotime("-$Days days")); $EndDate = date("Y-m-d"); $RunningDate = $StartDate; while($RunningDate < $EndDate) { $SelectBox.="<option value='$RunningDate'"; if($RunningDate == $_GET['date']) $SelectBox.=" selected='selected'"; $SelectBox.=">$RunningDate</option>"; $RunningDate = date("Y-m-d", strtotime($RunningDate . " + 1 day")); } $SelectBox.="</select>"; return $SelectBox; } if((isset($_GET['date'])) || (isset($_GET['month'])) || (isset($_GET['forever']))) { $ReportData = array(); //initialize report data array //initialize the query $Query = "SELECT COUNT(idx) AS imagect, SUM(kb) AS bandwith FROM imagehits"; if(isset($_GET['date'])) { //Append where string $Query.=" WHERE DATE_FORMAT(timestamp, '%Y%m%d') = \"{$_GET['date']}\""; $ReportData['period'] = "Report for " . date("l, F jS, Y", strtotime($_GET['date'])) . ":"; } elseif(isset($_GET['month'])) { //Append where string $Query.=" WHERE MONTH(timestamp) = \"{$_GET['month']}\""; $ReportData['period'] = "Report for the month of " . date("F", mktime(0,0,0,$_GET['month'])).":"; } else { //Looking at forever doesn't require a where statement //Lets get the earliest date in the database $GetDate = "SELECT DATE_FORMAT(MIN(timestamp), '%Y-%m-%d') AS firstdate FROM imagehits"; $link = mysql_connect($db_server, $db_user, $db_password) or die("Could not connect"); mysql_select_db($db_name) or die("Could not select database"); $Result = mysql_query($GetDate) or die("Error executing query. MySQL Error: " . mysql_error()); $Row = mysql_fetch_assoc($Result); $ReportData['period'] = "Report data starting ". date("l, F jS, Y", strtotime($Row['firstdate'])) . " Till Today"; mysql_close($link); unset($Result,$Row); } //Lets execute the query once and put it into an array $link = mysql_connect($db_server, $db_user, $db_password) or die("Could not connect"); mysql_select_db($db_name) or die("Could not select database"); $Result = mysql_query($Query) or die("Error executing query. MySQL Error: " . mysql_error()); $Row = mysql_fetch_assoc($Result); $ReportData['countofhits'] = $Row['imagect']; $ReportData['bw_gb'] = $Row['bandwith'] / 1073741824; $ReportData['bw_kb'] = $Row['bandwith'] / 1024; $ReportData['bw_mb'] = $Row['bandwith'] / 1048576; $ReportData['bw_bytes'] = $Row['bandwith']; } mysql_close($link); ?> <!--form section--> <div align='center'> <form name='reports' action='<? echo $_SERVER['PHP_SELF']; ?>' method='GET'> <a href="<? echo $_SERVER['PHP_SELF']; ?>?date=<? echo date("Ymd"); ?>">Today</a> - <a href="<? echo $_SERVER['PHP_SELF']; ?>?date=<? echo date("Ymd", strtotime("-1 day")); ?>">Yesterday</a> - <a href="<? echo $_SERVER['PHP_SELF']; ?>?month=<? echo date("m"); ?>">This Month</a> - Select a Date: <? echo CreateDateSelect(7); ?> <input type='submit' value='Go' name='submit' /> - <a href="<? echo $_SERVER['PHP_SELF']; ?>?forever=true">All Time</a> </form> </div><br><br> <!--Report Section--> <table border="0" align="Center" cellpadding="4" cellspacing="0"> <tr> <td colspan='2'><? echo $ReportData['period']; ?></td> </tr> <tr> <td><strong>Image Hits:</strong></td> <td><? echo $ReportData['countofhits']; ?> Hits</td> </tr> <tr> <td><strong>Bandwith (GB):</strong></td> <td><? echo number_format($ReportData['bw_gb'], 2); ?> GB</td> </tr> <tr> <td><strong>Bandwith (MB):</strong></td> <td><? echo number_format($ReportData['bw_mb'], 2); ?> MB</td> </tr> </table> Code (markup): also i need it to show today's stats by default when i enter the page instead of clicking on "today" to get todays stats we are getting closer as u said ^^
Okay, its taking me a minute to figure out where we left off... I guess I have to ask, is there data in the database for October 3rd?
Can you refresh my memory a little more? What works? Today? Yesterday? This Month? All Time? The select box? Any of it? Thanks!