Building on last week’s article about closing mysql connections, I wanted to pass on this further expanded class, that allows for caching on heavier queries. To start, create a folder in your main web directory called “_cache” Here is our file, save it as DbConn.php same as last time:
define('DBCACHE_PATH', realpath('.').'/_cache/');
class DbConn {
public $host = "localhost";
public $user = "";
public $pass = "";
public $DB = "";
public $conn;
public function __construct($host,$user,$pass,$db) {
$this->host = $host;
$this->user = $user;
$this->pass = $pass;
$this->DB = $db;
$this->conn = mysql_connect($this->host, $this->user, $this->pass) or die("Couldn't connection to $host");
mysql_select_db($this->DB,$this->conn);
}
public function __destruct(){
mysql_close($this->conn);
}
public function query($sql,$cache=true,$cachetime = "" ){
if(!$cachetime) $cachetime = (60*60*1);
if( $cache ){
$oCache = new DBCache($sql, $cachetime );
if (!$oCache->Check()) {
$res = $this->_query($sql);
$oCache->Set($res);
}
$res = $oCache->Get();
}else{
$res = $this->_query($sql);
}
return $res;
}
public function nonquery($sql){
$res = myquery($sql,$this->conn);
mysql_free_result($res);
return $results;
}
private function _query($sql){
$results = array();
$res = myquery($sql,$this->conn);
while( $row = mysql_fetch_assoc($res) ){
$results[] = $row;
}
mysql_free_result($res);
return $results;
}
}
class DBCache {
public $sFile;
public $sFileLock;
public $iCacheTime;
public $oCacheObject;
function __construct($sKey, $iCacheTime) {
$this->sFile = DBCACHE_PATH.md5($sKey).".txt";
$this->sFileLock = "$this->sFile.lock";
$iCacheTime >= 10 ? $this->iCacheTime = $iCacheTime : $this->iCacheTime = 10;
}
function Check() {
$val = 0;
if (file_exists($this->sFileLock)) return true;
$val = (file_exists($this->sFile) && ($this->iCacheTime == -1 || time() - filemtime($this->sFile) <= $this->iCacheTime));
if( !$val ){ if (file_exists($this->sFile)) { unlink($this->sFile); } }
return $val;
}
function Reset(){ if (file_exists($this->sFile)) { unlink($this->sFile); } }
function Exists() { return (file_exists($this->sFile) || file_exists($this->sFileLock)); }
function Set($vContents) {
if (!file_exists($this->sFileLock)) {
if (file_exists($this->sFile)) { copy($this->sFile, $this->sFileLock); }
$oFile = fopen($this->sFile, 'w');
fwrite($oFile, serialize($vContents));
fclose($oFile);
if (file_exists($this->sFileLock)) {unlink($this->sFileLock);}
return true;
}
return false;
}
function Get() {
if (file_exists($this->sFileLock)) {
return unserialize(file_get_contents($this->sFileLock));
} else {
return unserialize(file_get_contents($this->sFile));
}
}
function ReValidate() { touch($this->sFile); }
}
You may notice that we have a few new functions here this time. We still start same as we did last time:
$dbconn = new DbConn("localhost","mydbuser","mydbpass","mydbname");
define( "DBH", $dbconn->conn );
But now, we can use a few extra functions, namely query and nonquery. You use query like so:
$results = $dbconn->query("Select * from people",true,(60*60*2));
This will return an array containing the records from the people table and will also tell the class to cache it for 2 hours (60 minutes * 60 seconds * 2). This way, we are cleaning up our results, and storing them away. Especially useful for larger tables, that can be slower. Once you return results, you would loop through the array and display them:
$results = $dbconn->query("Select * from people",true,(60*60*2));
foreach($results as $row){
echo $row['name'];
}
Now, if you decided not to do any caching, we would do this:
$results = $dbconn->query("Select * from people",false);
foreach($results as $row){
echo $row['name'];
}
This will return the array of results, without doing any caching. The last function, is a function called nonquery, this function is used for executing queries that wouldn’t return results, like insert or update queries.
$dbconn->nonquery("update people set name='Roger' where name='Wayne';");
Anyhow, this may seem kind of long winded and probably not all that convenient, but when you start getting a larger site with more traffic and start to worry about server load, then knowing about caching your results can be a handy tool.
Today I’m pleased to announce the release of FeedNews. FeedNews is a stand-alone pro version of newsPage. I’ve added a backend so you can set up topics and feeds for each topic, and you can also choose to make a feed display as a wide feed, this allows the display of thumbnails on feeds that have thumbnails included. You can specify feeds to display on the homepage, or let the script create an index of topics instead.
Topics are automatically handled as seperate pages, and you can also specify which topics will show feeds on the main page. Fully template driven, easy to maintain and change via the backend.
Each topic also features it’s own RSS feed, which takes the feeds for that topic and creates one unified feed to display on other sites. Other features to come soon include an ad system, and the ability to use google blog search to locate feeds. The script is for sale for $5.00, and includes updates and support.
I’m going to show you a small but useful function that is handy to keep in your toolbox today, it’s called mysql_get_var. This function lets you run a SQL query, and only return the variable you choose. Here’s the function:
Now, let’s talk about what it does. When you call this function, like for example here:
$name = mysql_get_var("SELECT name from people where email = 'roger@freekrai.net'");
You will return the name field, so what gets returned will be “Roger” (if that was my name in the database). Now, you may notice that this function had a second argument called $y, this is so that you can choose which variable to return when your query has multiple fields:
$city = mysql_get_var("SELECT name,address,city from people where email = 'roger@freekrai.net'",2);
In the example above, I told it to return the 2nd argument, which due to PHP starting off arrays with a 0, would actually be the 3rd argument, so it returns the city of the person selected. This function is only for returning 1 field from 1 row to a time, so if there are more rows, this wouldn’t be as useful, but it does work well for grabbing say a user’s name everytime they log in, or some similar function.
Mozilla Prism (prism is an interesting prototype application that lets users split web applications out of their browser and run them directly on their desktop.)
I’m in the process of redoing some aspects of my food site, foodizu.com, it’s a slow process but one of the things I am addressing is speed. I looked into some CSS compression ideas, but I wanted one script that would read a CSS or a javascript file and automatically strip any white spaces out, and return a more compressed CSS file. First, we create a file called csszip.php:
This file will take anything passed via the file argument and compress it. Next, we want to make this process automatic, so we open up .htaccess and add this line:
RewriteEngine On
RewriteRule ^(.*).css$ /csszip.php?file=$1.css [L]
Obviously, if you already have a RerwriteEngine On line, then you can leave it out and place the RewriteRule on the line directly below it. This will tell the website to compress any css file you attempt to load. You can also have this work on javascript files by adding:
RewriteRule ^(.*).js$ /csszip.php?file=$1.js [L]
This will load and compress any javascript file as well. This method works well for reducing your bandwidth usage and cutting back on page load time, which are always nice bonuses.
Last week, ThemeForest introduced 2 new categories Javascript and PHP Scripts. I thought about it and figured why not, so I added 4 scripts to the marketplace (3 to PHP, 1 to javascript). So far, it’s been working out well, feedNews has sold quite a few copies, and the Shorter URL site and googlecharts plugin for PHP are doing decent as well. The new javascript google chart library has been picking up in sales, so far themeforest is working well for script sales.
