Thursday, August 14, 2008

Querying a SQLite 3 database using PHP

Previously, I detailed a way of creating a database in A basic hit counter using PHP and SQLite 3 using PHP Data Objects and PHP 5. For most web sites, SQLite would be fine, but for very high volume (as in many hundreds of thousands of hits per day), there are better options - Appropriate Uses For SQLite (SQLite.org) has more details on when SQLite is a good option.

Here is an example of how to query the data and display it on a page (to see which pages are popular for example).

<?
$dbfolder = $_SERVER["DOCUMENT_ROOT"]."/data/";
$dbname = $_SERVER["HTTP_HOST"]."_log.sq3";

$logdb = new PDO("sqlite:".$dbfolder.$dbname);

$starttable = "<table>
<tr>
 <th>Page</th>
 <th>Counter</th>
</tr>";
$endtable = "</table>";
$tablecontents = "";
foreach ($logdb->query("SELECT * FROM hits ORDER BY counter DESC") as $row)
{
 $tablecontents .= "
 <tr>
  <td>{$row['page']}</td>
  <td>{$row['counter']}</td>
 </tr>";
}
echo $starttable.$tablecontents.$endtable;

// close connection
$logdb = null;
?>

No comments: