Options are extremely powerful when it comes to building a website. They provide strong flexibility and facilitate many changes. Furthermore, they are largely present in many CMS systems and blogging platforms, such as WordPress.

WordPress Options

For those of you who aren’t familiar with WordPress’ options system, it acts in a simple way:

// an option is stored with the specified $name and $value
add_option( $name, $value );

// an option is retrieved using the $name
get_option( $name );

// updates an option with $name, or creates a new one if it doesn't exist
// this is generally used more than add_option due to its flexibility
update_option( $name, $value );

The ability of these functions stems from their storage of data. Even through multiple requests, the data that is passed as options will stay in-tact. This is because options are stored in a database.

Your Own Options

Today’s article will focus on how to implement an options system very similar to WordPress through PHP and MySQL. We will be going through:

You may download the source code if you wish:

Creating the Table

To begin, you will have to create a table called ‘options’ in your MySQL database with the following fields:

id - unsigned int(10), NOT NULL, auto_increment, primary key
name - varchar(255), NOT NULL
value - longtext, NOT NULL

If you just want to run a MySQL command to produce the database, use this:

CREATE TABLE `options` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  `value` longtext NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4;

Connecting to the Database

To connect to your database, you will need a server, username, and password. On my local server, I use the following:

function connect() {
	mysql_connect( 'localhost:8888', 'root', 'root' );
	mysql_select_db( 'custom' );
}

Note that you will have to change the three parameters of mysql_connect( $server, $username, $password ) to suit your own database. In addition, pass in the name of your database to mysql_select_db (custom is the name of the database I am using).

clean Function

Before you start running MySQL queries, it’s always necessary to have a function that makes the input safe. Consider the following:

function clean( $input ) {
	if( get_magic_quotes_gpc() )
		$input = stripslashes( $input );

	return mysql_real_escape_string( $input );
}

This function first removes any unnecessary slashes if they exist and then cleans the input through mysql_real_escape_string.

hasOption Function

Before you start writing the majority of PHP, it’s nice to have a helper function. In this case, you can create a hasOption function that will check if an option with a given name exists:

function hasOption( $name ) {
	$query = sprintf( "SELECT id FROM options WHERE name = '%s'", clean( $name ) );
	$result = mysql_query( $query );

	if( !$result )
		return false;
	return (bool) mysql_fetch_assoc( $result );
}

$query holds the MySQL query, which tries to select an id from the database that corresponds with the option name. mysql_query is then used to run this query. Subsequently, the result is obtained and a boolean is returned that determines whether a row in the database corresponds with the parameter $name.

addOption Function

The addOption function will be used to store an option which can be retrieved at a later time. To implement this, you can use the hasOption function which you just created:

function addOption( $name, $value ) {
	if( hasOption( $name ) )
		return false;

	$query = sprintf( "INSERT INTO options( name, value ) VALUES( '%s', '%s' )", clean( $name ), clean( serialize( $value ) ) );
	$result = mysql_query( $query );

	return (bool) $result;
}

This function will try to add an option to the database and return true or false depending on its success. Note that this function is able to store data of any type. This is because it uses the PHP function serialize to generate a string representation of any value.

From the code perspective, it does the following:

  1. If the option already exists, it returns false, as it only adds options (does not update them).
  2. It then produces a MySQL query to insert a new option.
  3. Using mysql_query, the query is run and, if successful, true is returned. Otherwise, the function returns false.

updateOption Function

updateOption is very similar to addOption, but with a few changes:

function updateOption( $name, $value ) {
	if( !hasOption( $name ) )
		return addOption( $name, $value );

	$query = sprintf( "UPDATE options SET value = '%s' WHERE name = '%s'", clean( serialize( $value ) ), clean( $name ) );
	$result = mysql_query( $query );

	return (bool) $result;
}

If the option does not already exist, updateOption will create it by calling addOption. Otherwise, it will run a MySQL update command to change the value inside the database. It then returns whether it was successful or not.

getOption Function

The getOption function is almost the exact same as addOption, except that it will retrieve the value of the option and then unserialize it.

function getOption( $name ) {
	if( !hasOption( $name ) )
		return false;

	$query = sprintf( "SELECT value FROM options WHERE name = '%s'", clean( $name ) );
	$result = mysql_query( $query );

	if( $result && ( $row = mysql_fetch_assoc( $result ) ) )
		return unserialize( $row[ 'value' ] );
	return false;
}

If the option does not exist, this function returns false. It then creates a select query and executes it. When returning the value, note that getOption uses unserialize to undo the storage that was used by addOption.

delOption Function

delOption is used to delete an option. It runs a MySQL delete command to accomplish its job:

function delOption( $name ) {
	if( !hasOption( $name ) )
		return false;

	$query = sprintf( "DELETE FROM options WHERE name = '%s'", $name );
	$result = mysql_query( $query );

	return (bool) $result;
}

Note how this function returns false if the option doesn’t exist. Otherwise, it’ll run the delete command and then return true or false depending on its success.

Wrapping Up

With a MySQL table and a few functions, you have produced a working options system. You may now use the functions like so:

// connect to the database
connect();

// add an option that corresponds $name to $value
addOption( $name, $value );

// get an option with a specified name
getOption( $name );

// update an option to correspond $name to $value
updateOption( $name, $value );

// delete an option with a given $name
delOption( $name );

// check whether an option with a given $name exists
hasOption( $name );

Questions? Comments? Please post them below and tell us what you think!

Leave a Reply

The Power of Options: Simple, yet Effective