Archive

Posts Tagged ‘database’

Delete Trailing Commas In PHP

April 14th, 2009 Tech 1 comment

Converting an array of information into a string is easy, but when you are doing this for insertion into a database having trailing commas is going to mess up your SQL statements.

Take the following example, which takes an array of values and converts them into a string of values. This practice is quite common in PHP database manipulation.

$values = array('one', 'two', 'three', 'four', 'five');
$string = '';
 
foreach ( $values as $val ) {
    $string .= '"'.$val.'", ';
}
 
echo $string; // prints "one", "two", "three", "four", "five",

Obviously we need to strip the trailing comma from the end of this string. To do this you can use the following function.

function deleteTrailingCommas($str)
{
    return trim(preg_replace("/(.*?)((,|s)*)$/m", "$1", $str));
}

This function uses a regular expression to match for one or more commas or spaces after the main bulk of text and before the end of the string and prints out the main bulk of text. The trailing commas are not returned.

Here is another example:

$string = '"one", , ,  , , , ,,';
echo $string;
$string = deleteTrailingCommas($string);
echo $string;

This prints out the following:

"one", , ,  , , , ,,
"one"

A Simple Introduction To Zend_Cache

April 2nd, 2009 Tech No comments

The Zend_Cache class is part of the Zend Framework and is used (as its name suggests) to cache things. This can be anything from the front end browser output to the outcome of a complex calculation or even the results of database queries. Zend_Cache is an enormous topic, not just how the class works, but what the best practices are for caching.

The best place to start with caching is one of the simpler topics of caching database queries. Normally, a call to a database table in Zend Framework might be done like this.

$houses    = new Houses();
$result      = $houses->fetchAll();

The result would then be processed. To use Zend_Cache instead of calling the database we first need to set up our Zend_Cache object so that we can use it. To do this we need to call the Zend_Cache static function factory() with a few parameters, which will give us a cache object. Here is a typical example.

$query_cache = Zend_Cache::factory('Core', 'File', $frontendoptions, $backendoptions);

The parameters are as follows:

  • 'Core' - This can be a number of different options which dictate what sort of things are cached on the frontend, the value here is mapped to a class. In this case the class is Zend_Cache_Core, but other classes are mapped to Zend_Cache_Frontend_*. The Zend_Cache_Core class is best used for database calls because there is no specific frontend class that deals with database calls.
  • 'File' - This indicates where the cache is to be stored in the backend. Again this value maps to a class, in this case Zend_Cache_Backend_File. In most cases the Zend_Cache_Backend_File class is the simplest and easiest option to use.
  • $frontendoptions - This is an array of options that relates to the frontend class you have chosen.
  • $backendoptions - This is an array of options that relates to the backend class you have chosen.

The following code sets up an instance of Zend_Cache using some common parameters. Note that different frontend and backend classes have a different set of parameters, but the parameters used below are for the Core frontend and the File backend. The APPLICATION_PATH constant just points to our application folder.

$frontendoptions = array(
    'lifetime' => 60 * 5, // 5minutes
    'automatic_serialization'=>true
);
$backendoptions = array(
    'cache_dir'=> APPLICATION_PATH . '/cache/',
    'file_name_prefix' => 'zend_cache_query',
    'hashed_directory_level' => 2
);
$query_cache = Zend_Cache::factory('Core', 'File', $frontendoptions, $backendoptions);

Here is an explanation of the frontend options used.

  • lifetime - This is self explanatory. If the cache created is greater than the number of seconds for this parameter then the cache is deleted. This can be set to null if we wan’t the cache to last forever.
  • automatic_serialization - If set to true this will automatically serialise the cache data. This allows you to store complex data like objects and arrays. If you are storing a numeric value or text string only then you can set this to false.

Here is an explanation of the backend options used.

  • cache_dir - This is the directory that the cache is to be kept in. The default to this is /tmp/ but it is best to keep the cache within the application folder so that you can manage the files manually if need be.
  • file_name_prefix - This sets the start of the filename to be used, because I want to cache database queries I have selected zend_cache_query as my prefix.
  • hashed_directory_level - Some file systems have great difficulty handling lots of files in a single directory. This option splits the cache into different levels or directories. The default is 0, but for this example I have selected 2. This means that our cache files will be stored inside 2 levels of directories.

To load a cache we use the load() function. This function takes a parameter that identifies the cache, but because we are getting all data from the houses table we don’t need to worry too much about this. If there is no cache with that name present then the function returns false. If this occurs we run our normal database query but in each case the $result variable will contain our data.

if ( !($result = $query_cache->load('allhouses')) ) {
    $houses    = new Houses();
    $result      = $houses->fetchAll();
    $query_cache->save($result, 'allhouses');
}

Once we have run the normal query we save the result to the cache using the save() function. This contains the data we want to save in the first parameter and the same cache name as the load() function in the second parameter. The next time the page is loaded the cache is loaded instead of calling the database.

We can also cache single data rows in the same way by using a unique identifier for our cache name. Assuming that have our house id we can do the following:

$cacheName = 'house'.$id;
if ( !($result = $query_cache->load($cacheName )) ) {
    $houses    = new Houses();
    $result      = $houses->fetchRow($houses->select()->where('id = ?', $id));
    $query_cache->save($result, $cacheName );
}

Note that if you want to do anything more than display the results of the query then you will need to access the database directly. It is not possible to interact with a database through the cached object.

Optimize Your MySQL Database Tables

March 3rd, 2009 Tech No comments

You can optimize MySQL tables using the OPTIMIZE command. This can be used if you have a table with any variable length rows and you make lots of changes to the data in that table. Variable length columns are VARCHAR, VARBINARY, BLOB, or TEXT. The OPTIMIZE TABLE will defragment the data and reclaim any unused space. This also has the effect of resetting any auto incrementing numbers to the highest value in the table.

OPTIMIZE TABLE tbl_name;

You can do this for every table in the database, but to save time MySQL comes with a command line script that will optimize all tables in a database. This program is called mysqlcheck and can be run like this.

mysqlcheck -o database -u user -ppassword

Note that when writing this command you should not put a space in between the -p and your password.

The mysqlcheck program can also be used to check (-c/-m/-C), repair (-r) and analyze (-a) the tables in a database. These commands can be done by using the CHECK, REPAIR and ANALYZE MySQL commands.

Optimizing tables will clear out unnecessary settings and speed up your tables slightly, but there is little point in doing this more than once a month, or once a week for very high traffic MySQL servers.

Categories: MySQL Tags: , , ,

Manage MySQL Databases With phpMyAdmin

August 7th, 2008 Tech No comments

phpMyAdmin is a tool, written in PHP, that allows you to handle the administration of a MySQL database server. You could always download the MySQL GUI tools, but the problem there is that you need to give external access to a user account, which isn’t always possible to do. This is where phpMyAdmin steps in.

Manage MySQL Databases With phpMyAdmin

The tool is easy to use and I have done some tricky stuff with it in the past. It can do everything that you need it to do with MySQL.

I have tried to install phpMyAdmin a couple of times and have failed each time. Admittedly, I only attempted it half-heartedly, but this is the only stumbling block I can see in the average user trying to use this system. However, many hosts will give you access to this tool as part of the hosting package, and it is only really needed if you expect to do a lot of database administration otherwise you can just run PHP scripts.

The website contains extensive documentation, and even if this isn’t enough there are a few books available for the tool.

Get Information About A MySQL Database With PHP

June 11th, 2008 Tech No comments

By using the MySQL command.

SHOW TABLE STATUS FROM database;

You can get all sorts of information about a database. The query returns each table as a row and gives lots of information about each table. Using this query it is possible to work out some usage data for the database as a whole. The following function will take a database name and a database resource handle and return how big that database is, the number of tables, and the number of rows in those tables. You will probably have a maximum limit to the amount of data that you can store in your database, so this function is useful to make sure that you don’t exceed this limit. This function also uses another function found on the Talk In Code site called readableFileSize() to give more meaningful data sizes.

function getDatabaseInfo($database, $db, $readableSizes=true) {
 // set default return values
 $size = 0;
 $dataLen = 0;
 $indexLen = 0;
 $rows = 0;
 $tables = 0;
 // run SQL
 $result = mysql_query('SHOW TABLE STATUS FROM '.$database.';');
 if($result){
  // if result exsits then work out data values
  $tables = mysql_num_rows($result);
  while($data = mysql_fetch_assoc($result)){
    $size += ($data['Index_length'] + $data['Data_length']);
    $dataLen += $data['Index_length'];
    $indexLen += $data['Data_length'];
    $rows += $data['Rows'];
  };
 };
 // return
 if($readableSizes){
  return array('size'=>readableFileSize($size),'rows'=>$rows,'index-length'=>readableFileSize($indexLen),'data-length'=>readableFileSize($dataLen),'tables'=>$tables);
 }else{
  return array('size'=>$size,'rows'=>$rows,'index-length'=>$indexLen,'data-length'=>$dataLen,'tables'=>$tables);
 };
}

To run this function just connect to the database and run the function, passing the database name and the connection resource.

// open mysql connection
$handle = mysql_connect('localhost', 'root', 'wibble');
 
if(!$handle){
 die('Connection failed!');
}
 
echo '<pre>'.print_r(getDatabaseInfo('wordpress', $handle,false),true).'</pre>';

This prints the results.

Array
(
 [size] => 1835008
 [rows] => 1805
 [index-length] => 1277952
 [data-length] => 557056
)

Just to check I had a look at what the MySQL administrator application tells me about the database structure and found that they were the same.

Categories: PHP Tags: , , , ,