Website visits analysis - 7 days visits display

Website visits analysis.

3. Seven last days visits display.

3.1.

Aim: nothing less and nothing more than displaying the visits count on the site (home page) and on its major sections (Computer Basics, Lazarus/Free Pascal Programming...) for the last 7 days. Approach: Reading the relevant data from the database and displaying it as a HTML table.

3.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 dates of the last 7 days.
Read the visitors count for each URL and each of these dates from the database.
Create a HTML table, using the actual data as cell values.

3.3.

PHP code.

You can view the code by opening the tab below. Or click the following link to download the PHP script.

The 7 days visitors counting script (/sitestats/stats_7days.php).

<?php

// Connect to the database
$user = 'stats'; $passwd = 'password';
$pdo = new PDO('mysql:host=localhost;dbname=statistics', $user, $passwd);
// Determine dates of last 7 days
for ($i = 7; $i >= 1; $i--) {
$day = mktime(0, 0, 0, date("m"), date("d") - $i + 1, date("Y"));
$dates[7 - $i + 1] = date("Y", $day) . "-" . date("m", $day) . "-" . date("d", $day);
}
$firstdate = $dates[1]; $lastdate = $dates[7];
// Query the database (for all sites and with 7 last days 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 these 7 days, display the visits counts (as HTML table)
if ($sth->rowCount() > 0) {
// Create (display) the HTML table
echo '<table border="1" cellpadding="5">';
// Table header
echo '<tr>';
echo '<th>Site url</th>';
for ($i = 1; $i <= 7; $i++) {
$dateparts = explode('-', $dates[$i]);
$date = $dateparts[2] . '.' . $dateparts[1];
echo '<th>' . $date . '</th>';
}
echo '<th>Total</th>';
echo '</tr>';
$oldsite = '';
// Display a table entry for each record returned from the database
while ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
// The daily counts for a given site will fill up a row in the table
if ($row['_site'] != $oldsite) {
// Record data contains "new" site URL
if ($oldsite != '') {
// Display the total count (for previous date)
for ($j = $i; $j <=7; $j++) {
echo '<td> </td>';
}
echo '<td align="right">' . $total . '</td>';
echo '</tr>';
}
echo '<tr>';
echo '<td>' . $row['_site'] . '</td>';
$oldsite = $row['_site'];
$i = 1; $total = 0;
}
while ($row['_date'] > $dates[$i]) {
// Days with 0 hits have no records; thus fill days fields with spaces, until
// record returned actually has data for this day
echo '<td> </td>';
$i++;
}
// Now display the count for this day
echo '<td align="right">' . $row['_count'] . '</td>';
// Add all row counts for 7 days site total
$i++; $total += $row['_count'];
}
// Display the last total count (was not yet done)
for ($j = $i; $j <=7; $j++) {
echo '<td> </td>';
}
echo '<td align="right">' . $total . '</td>';
echo '</tr>';
echo '</table>';
}
// If there are no database entries for these 7 days, display a message (instead of table)
else {
echo '<p>There are no site hits for the last 7 days...</p>';
}

?>

3.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_7days.php" in the address field of my webbrowser, produces the following output:

Computing: Website and Database Programming

Website visits analysis.

3. Seven last days visits display.