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:

function mysql_get_var($query,$y=0){
   $res = mysql_query($query);
   $row = mysql_fetch_array($res);
   mysql_free_result($res);
   $rec = $row[$y];
   return $rec;
}

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.

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) &#038;&#038; ($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.

I spend a lot of time working in PHP, and one thing I run into a lot, is garbage collection. It’s important to make sure you DB connections are closed when the script finishes excution, or it can cause other problems (like memory, resources, etc).

So I handle my DB connection through a very simple class that is set up to do a mysql disconnect at the end of the script’s execution.

class DbConn {
  public $conn;
  public function __construct($host,$user,$pass,$db) {
    $this->conn = mysql_connect($host, $user, $pass) or die("Couldn't connection to $host");
    mysql_select_db($db,$this->conn);
  }
  public function __destruct(){
    mysql_close($this->conn);
  }
}
$dbconn = new DbConn("localhost","mydbuser","mydbpass","mydbname");
define( "DBH", $dbconn->conn );

Then when you do your database queries, you just make sure to include the DBH handle in the code.

$result = mysql_query("Select * from people",DBH);

When the script ends, the code makes sure it closes your database connections.

This can be built on quite a bit to include handling of mysql queries, but I wanted to at least share this simple class that has gotten a lot of use in various projects and has come in handy several times.

When I redid this blog, I wanted to make a “tweet this” button, but I didn’t want to send the regular URL to twitter, between my name and some of my articles, you can reach the 140 character limit pretty quick.

I also didn’t want to have to rely on a url shortening service for the shorter URLs, as that can cause it’s own problems since people aren’t seeing your domain name in the URL and what happens if that URL service closes it’s doors or decides to delete all links that are older than x number of days? You have links to your blog that don’t go anywhere.

So I decided to cut out the middle man and create my own functions. There are 2 functions, one handles creating the shorter URLs, and the other function handles the redirect.

Place them in your functions.php file in your theme:

function get_shorter_url(){
  global  $wp_query, $post;
  $post_id = $wp_query->post->ID;
  $tinyURL = get_post_meta($post_id, 'Shorter URL', true);
  if ( $tinyURL == ''){
    $tinyURL =  '/' . base_convert($post_id, 10, 36) . '.goto';
    add_post_meta($post_id, 'Shorter URL', $tinyURL, true);
  }
  $tinyurl = get_bloginfo('wpurl').$tinyURL;
  $tinyurl = str_replace("www.","",$tinyurl);
  return $tinyurl;
}
function redirect_shorter_url() {
  if(is_404()){
    global $wpdb;
    $postmetaquery = "SELECT `post_id`, `meta_value` FROM `$wpdb->postmeta` WHERE meta_key LIKE 'Shorter URL' AND meta_value != ''";
    $postarr = $wpdb->get_results($postmetaquery,ARRAY_A);
    foreach($postarr as $arr){
      if((get_bloginfo('wpurl') .$arr['meta_value']) == ('http://' . $_SERVER["SERVER_NAME"].$_SERVER["REQUEST_URI"])){
        $url = get_permalink($arr['post_id']);
        header("HTTP/1.0 301 Moved Permanently");
        header ("Location: $url");
        exit;
      }
    }
  }
}
add_action('template_redirect', 'redirect_shorter_url');

Now, whenever you want to use a shorter url, you just call the function in your theme, like so:

echo get_shorter_url();

So for example, if I wanted to add a tweet this link to the bottom of my article, I would open single.php and add this link:

<A href="http://twitter.com/home?status=Currently reading <?php echo get_shorter_url() ?>" title="Click to send this page to Twitter!" target="_blank">Tweet this</a>

Ok, so today newsPage got another quick update, as I added a feature that was requested by a few users.

That feature is the ability to choose to open links from RSS feeds in new windows.

I promise to keep the updates down for the next couple days.

Recently,  some clients of mine decided to move a site that was fairly established on it’s own platform to wordpress. So I was faced with importing all the old articles into wordpress.

For the import, I decided to set up an RSS feed that contained the content, and so I imported all articles to the new system.

But then, we found a problem. The old site, used article.php?id=ARTICLEID, whereas the new site used /article-name so if you went to an article from the old site, you got a 404 error. This caused a problem when the site sent out thousands of emails a day, and was used as a central area of conversation for it’s industry.

We were faced with setting up 301 redirects for over 2000 articles, or finding another solution.

After some research and testing, I noticed that wordpress stores the original article’s address as the new article’s permalink during the import. So we wrote a quick little function that could check for old article references.

function redirect_fix() {
   global $wpdb;
   if ( isset($_GET['id']) || $_SERVER['QUERY_STRING'] != "" || substr($_SERVER['REQUEST_URI'],-1) == "?") {
      $likestr = "?id=".$_GET['id'];
      $likestra = explode("#",$likestr);
      $likestr = $likestra[0];
      $querystr = "SELECT wposts.* FROM $wpdb->posts AS wposts WHERE wposts.guid LIKE '%".$likestr."'";
      $pageposts = $wpdb->get_results($querystr, OBJECT);
      foreach ($pageposts as $post){
         $url = get_permalink($post->ID);
         if( count($likestra) > 0) $url .= "#".$likestra[1];
         wp_redirect($url,301);
         exit;
      }
   }
}
add_action('plugins_loaded','redirect_fix',0);

I placed this in the functions.php file of our theme, and set it to work. Now, whenever someone goes to the site using an old URL, they are given a 301 redirect to the new URL. Since we put this fix live over a month ago, we have had no problems with the site.

This tip is just something to show you an idea for handling importing an old established site into wordpress and how to address some of the issues that can come up.

This method has worked well for me on the site I used it with, and may help you out later too. To change it for your needs, change the reference to id to whatever field your site used to use. so that if you used to have a site that called article as the id tag, then you would replace references to “id” with “article”, as shown below:

function redirect_fix() {
   global $wpdb;
   if ( isset($_GET['article']) || $_SERVER['QUERY_STRING'] != "" || substr($_SERVER['REQUEST_URI'],-1) == "?") {
      $likestr = "?article=".$_GET['article'];
      $likestra = explode("#",$likestr);
      $likestr = $likestra[0];
      $querystr = "SELECT wposts.* FROM $wpdb->posts AS wposts WHERE wposts.guid LIKE '%".$likestr."'";
      $pageposts = $wpdb->get_results($querystr, OBJECT);
      foreach ($pageposts as $post){
         $url = get_permalink($post->ID);
         if( count($likestra) > 0) $url .= "#".$likestra[1];
         wp_redirect($url,301);
         exit;
      }
   }
}
add_action('plugins_loaded','redirect_fix',0);

It’s that simple to address, and has worked well. Since then I’ve used this trick on my own blog when I merged 360affiliate.com with this blog.

Ok, so today I started to redo the theme that this blog is using, it definetly lends itself nicely to what I want to do here, and with a few more tweaks, will be right where I want it.

Some things I’ve done to the theme, was the original twitter feed to the right was javascript based, so I changed it to use PHP and added a caching method to it so that it only updates every hour.