Computing: Website and Database Programming

Website visits analysis.

4. Curent month visits statistics.

4.1.	What to display?
	Aim: displaying some statistics about the visits on the site (home page) and on its major parts (Computer Basics, Lazarus/Free Pascal Programming, Website and Database Applications...) during the current month. Approach: Reading the data for this month from the database, calculating the values wanted (e.g. total, maximum, average, 0 visitors days, yesterday and today visitors, difference of totay visitors to average) and displaying them as a HTML table.
4.2.	What to do?
	To do: Create a PHP file, containing the standard HTML structure of all pages on my site. Include the PHP code that reads the database and, using the actual data, generates a HTML table. Code preview for the PHP script: Determine the date of the first and last days of the current month. Read the visitors count for each URL and all dates of this month from the database. Calculate the values needed for the table. Create a HTML table, displaying the monthly values.
4.3.	PHP code.
	You can view the code by opening the tab below. Or click the following link to download the PHP script. The Curent month visitors statistics script (/sitestats/stats_month.php). <?php // Connect to the databse $user = 'stats'; $passwd = 'password'; $pdo = new PDO('mysql:host=localhost;dbname=statistics', $user, $passwd); // Calculate first and last day (of current month) $day = mktime(0, 0, 0, date("m"), 1, date("Y")); $firstdate = date("Y", $day) . "-" . date("m", $day) . "-" . date("d", $day); $day = mktime(0, 0, 0, date("m") + 1, 1, date("Y")) - 1; $lastdate = date("Y", $day) . "-" . date("m", $day) . "-" . date("d", $day); // Query the database (for all sites and with this month dates) $sql = "SELECT site_url AS _site, access_date AS _date, access_count AS _count FROM web_sites, site_hits "; $sql .= "WHERE web_site = site_id "; $sql .= " AND access_date >= '$firstdate' AND access_date <= '$lastdate' "; $sql .= "ORDER BY site_seqnum, access_date"; $sth = $pdo->query($sql); // If there are database entries for this month, display the visits counts (as HTML table) if ($sth->rowCount() > 0) {     $days = date("j");     $oldsite = ''; $nsites = 0;     // Display a table entry for each record returned from the database     while ($row = $sth->fetch(PDO::FETCH_ASSOC)) {         // The counts for a given site will fill up a row in the table         if ($row['_site'] != $oldsite) {             // Record data contains "new" site URL: create new entry in counting array and set counters to 0             $nsites++;             $totals[$nsites] = 0; $zeros[$nsites] = 0; $nonzeros[$nsites] = 0; $maximums[$nsites] = 0;             $averages[$nsites] = 0; $yesterdays[$nsites] = 0; $todays[$nsites] = 0;             $sites[$nsites] = $row['_site'];             $oldsite = $sites[$nsites];         }         // Date as to be displayed in HTML table         $dateparts = explode('-', $row['_date']); $day = $dateparts[2];         // Update values for current site         $totals[$nsites] += $row['_count'];         $nonzeros[$nsites]++;         if ($row['_count'] > $maximums[$nsites]) {             $maximums[$nsites] = $row['_count'];         }         if ($day == $days - 1) {             $yesterdays[$nsites] = $row['_count'];         }         else if ($day == $days) {             $todays[$nsites] = $row['_count'];         }     }     // Some values have to be calculated when totals are known     for ($i = 1; $i <= $nsites; $i++) {         $zeros[$i] = $days - $nonzeros[$i];         $averages[$i] = sprintf("%01.2f", round($totals[$i] / $days, 2));         $differences[$i] = sprintf("%01.2f", abs($todays[$i] - $averages[$i]));         if ($todays[$i] - $averages[$i] > 0) {             $differences[$i] = '+' . $differences[$i];         }         else {             $differences[$i] = '–' . $differences[$i];         }     }     // Create the HTML table     echo '<table border="1" cellpadding="5">';     echo '<tr>';     echo '<th align="center">Site url</th><th align="center">Month<br/>days</th align="center"><th align="center">Zero<br/>days</th>';     echo '<th align="center">Total<br/>visits</th><th align="center">Max<br/>visits</th><th align="center">Average<br/>visits</th>';     echo '<th align="center">Yday<br/>visits</th><th align="center">Today<br/>visits</th><th align="center">Average<br/>difference</th>';     echo '</tr>';     // Display the different counts for each site     for ($i = 1; $i <= $nsites; $i++) {         echo '<tr>';         echo '<td align="left">', $sites[$i], '<td align="right">', $days, '</td><td align="right">', $zeros[$i], '</td>';         echo '<td align="right">', $totals[$i], '</td><td align="right">', $maximums[$i], '</td><td align="right">', $averages[$i], '</td>';         echo '<td align="right">', $yesterdays[$i], '</td><td align="right">', $todays[$i], '</td><td align="right">', $differences[$i], '</td>';         echo '</tr>';     }     echo '</table>'; } // If there are no database entries for this month, display a message (instead of table) else {     echo '<p>There are no site hits for this month...</p>'; } ?>
4.4.	Running the script.
	Running the script (first the script as is in the download file, then the script included in my standard HTML code) on September, 16 on my local machine, by typing "localhost/sitestats/stats_month.php" in the address field of my webbrowser, produces the following output: