Tuesday, August 26, 2008

jQuery Quick Tip: In general $(this).attr("attribute") = this.attribute

jQuery is a very useful JavaScript library for manipulating you web pages. However, I have seen it used to get attribute values when it doesn't have to be, normally to get element attributes. For example, opening all links with a class external and giving them a title (tooltip).

$("a.external").click( function()
  {
    window.open($(this).attr("href"));
    return false;
  }
).each( function()
  {
    $(this).attr("title", $(this).attr("title") + " External link: " + $(this).attr("href"));
  }
);

Would be the same as

$("a.external").click( function()
  {
    window.open(this.href);
    return false;
  }
).each( function()
  {
    this.title += " External link: " + this.href;
  }
);

There are some attributes that in JavaScript aren't the same as they are in html. The ones you may have issues with are listed below.

for          (htmlFor)
class        (className)
readonly     (readOnly)
maxlength    (maxLength)
cellspacing  (cellSpacing)

Doing this rather than using attr cuts down on code size and may improve performance.

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;
?>

Tuesday, August 12, 2008

A basic hit counter using PHP and SQLite 3

SQLite is a lightweight database engine that is bundled with PHP 5. Using PHP Data Objects (PDO) you can create and edit SQLite databases and tables. A SQLite databases is just a single file that is stored in a folder that has modify rights applied to it (otherwise you can't change it).

This sample shows how to create a database, add a table to it and insert and update a record in it. By using PDO, you can easily change it to use different database engines (like MySQL, IBM DB2 etc [others, like MSSQL and Oracle are in a experimental stage though, check the PDO site (linked above) for more information]).

You can copy this code into a separate file and include it within pages that you want a counter on <? include("counter.php"); ?>.

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

// check if database file exists first
if(!file_exists($dbfolder.$dbname))
{
 $logdb = new PDO("sqlite:".$dbfolder.$dbname);
 $logdb->exec("CREATE TABLE hits(page VARCHAR(255) PRIMARY KEY, counter INTEGER)");
}
else
{
 $logdb = new PDO("sqlite:".$dbfolder.$dbname);
}

$page = $_SERVER["SCRIPT_URL"];

// check if page is already in the hits table
$statement = $logdb->query("SELECT counter FROM hits WHERE page='$page'");
$record = $statement->fetchAll();

// if a record is found
if(sizeof($record) != 0)
{
 $counter = $record[0]['counter']++;
 $logdb->exec("UPDATE hits SET counter=$counter WHERE page='$page'");
 echo "Counter: ".$counter;
}
else
{
 $logdb->exec("INSERT INTO hits(page, counter) VALUES ('$page', 1)");
 echo "Counter: 1";
}

// close connection
$logdb = null;
?>

In writing this code, LiteWebsite was a valuable resource, containing many SQLite examples.