I’m working on a project where I want to build a table to hold information the application can use system wide. From Phone numbers to blocks of text. I’m thinking a matching pair table would be the way to go since I would build the infrastructure once and then just configure matching pairs of data. This requires a key and the data value. Real simple to configure and use. Beats a static table that needs to be modified every time there is a new value to be stored and referenced. Might even replace several tables depending on the application.

This approach will save you a ton of time once you build it. For example let’s say your business phone number is displayed on your website in 4 or 5 places. It would be much easier to use a matching pair table to store and retrieve your phone number. That way you only change your phone number in one place and your website will be updated immediately. Quick and easy.

The uses for this idea are endless.

Here is the table layout. Very simple.

id int 10
config_key varchar 255
config_value longtext (4GB)

Using the phone number example, we need a matching pair, the key : “business_phone”, and the value : “999-999-9999”.

I want to use the PHP Data Objects (PDO) interface. As you may know as of PHP 5.5.0 the “old” style MySql extensions are deprecated. I assume we will have 2 – 4 years to upgrade our code. I’ll be using PDO for everything going forward.

I’m not going to create any forms, just queries and I’ll add some echoes to the code so we can see the data when it is modified.

Here is my example:

TABLE: config

CREATE TABLE IF NOT EXISTS `config` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`config_key` varchar(255) NOT NULL,
`config_value` longtext NOT NULL,
PRIMARY KEY (`id`),
KEY `config_key` (`config_key`)
) ;

We need to be able to
Create
Read
Update
Delete

Also known as CRUD.

Connect via PDO

$dbtype = ‘mysql’;
$dbhost = ‘localhost’;
$user = ‘oop’;
$pass = ‘oop’;
$dbname = ‘oop’;
$dsn = “$dbtype:host=$dbhost;dbname=$dbname”;
$db = new PDO($dsn, $user, $pass);

Create a Record

$table = “config”;
$insert = “INSERT INTO $table
(config_key,config_value)
VALUES(‘business_phone’,’999-999-9999′)
“;
$num_rows = $db->exec($insert);
echo “<p>Insert num_rows : $num_rows</p>”;

Read a Record

$table = “config”;
$where = “config_key = ‘business_phone'”;
$select = “SELECT * FROM $table WHERE $where”;
$RS = $db->query($select);
echo “<p>Read : “. print_r($RS->fetchAll(PDO::FETCH_ASSOC),true) .”</p>”;

Update a Record

$table = “config”;
$where = “config_key = ‘business_phone'”;
$update = “UPDATE $table SET config_value = ‘777-777-7777’ WHERE $where”;
$rtn = $db->query($update);
echo “<p>Updates : “. $rtn->rowCount() .”</p>”;

Delete a Record

$table = “config”;
$where = “config_key = ‘business_phone'”;
$delete = “DELETE FROM $table WHERE $where”;
$rtn = $db->query($delete);
echo “<p>Deletes : “. $rtn->rowCount() .”</p>”;

$db = null; // close the connection

As you can see creating and accessing a matching pair table is rather simple, however it is very powerful. This approach can add to the robustness of your application and make your life just a little easier. Obviously you will want modify this code to meet your needs and take into consideration your security needs.

You are free to use this code as you see fit. By using this code you agree to be responsible for any effects it may have on any system you may use it on. You agree that Keith Smith Internet Marketing LLC is not responsible for anything. Also you agree this code is not created or displayed for any specific purpose and is not merchantable for any particular purpose. Use this code at your own risk.