how to use a database prefix?

[Alkimuz]

hi,

 

i was wondering what an easy and right way would be to use a variable that can stand before the names of your tables. I've seen that a CMS like joomla or wordpress calls this a 'database prefix'

 

what does work is for example:

 

[config.php]

$prefix = 'prefix_';

 

[index.php]

$table = $prefix.'tablename';

$tableresult = mysql_query("SELECT * FROM $table") or die ("could not execute tableresult");

 

but i gues there should be a better way, as it looks kind of silly to declare the $table every time before the actual query..

 

thanks for any help!

       

[trq]

Another way would be:

 

mysql_query("SELECT * FROM {$prefix}tablename")

 

but I don't see that it really matters.

[Alkimuz]

awesome, i didn't know this kind of variable handling in SQL, thank you!

 

for now, my question is kind of answered, as i will use your suggestion, although i was also curious if there is some kind of "standard" way to handle with database prefixes in coding; unfortunately, i am not advanced enaugh to figure out how the major CMS-systems are doing it, but for now, i am happy  

[xyph]

As long as you make it obvious you're using a prefix for other developers who look at your code, you're doing it right.

[scootstah]

unfortunately, i am not advanced enaugh to figure out how the major CMS-systems are doing it

 

For the most part, they do it the way thorpe suggested.

 

The only thing I have to add is make sure you use proper namespacing, instead of just $prefix, to make sure the variable is not replaced somewhere.

[xyph]

unfortunately, i am not advanced enaugh to figure out how the major CMS-systems are doing it

 

For the most part, they do it the way thorpe suggested.

 

The only thing I have to add is make sure you use proper namespacing, instead of just $prefix, to make sure the variable is not replaced somewhere.

 

Constants are a great, easy way to do this. PHP won't let you redeclare them

[haku]

You can also run your code through a function that adds the prefix:

 

function my_query($query)
{
  $prefix = 'someprefix';
  $prefixed_query = preg_replace('/{(.*?)}/', $prefix . $1, $query);
  return mysql_query($prefixed_query);
}

$result = my_query('SELECT something FROM {some_table}');

(not tested, probably has bugs, but should give the general idea)

[scootstah]

You can also run your code through a function that adds the prefix:

 

function my_query($query)
{
  $prefix = 'someprefix';
  $prefixed_query = preg_replace('/{(.*?)}/', $prefix . $1, $query);
  return mysql_query($prefixed_query);
}

$result = my_query('SELECT something FROM {some_table}');

(not tested, probably has bugs, but should give the general idea)

 

The problem with that approach is if something else is in curly brackets it will swap that out too. Like this:

$result = my_query("INSERT INTO {some_table} ('{something in curlies}')");

 

Produces: INSERT INTO someprefixsome_table ('someprefixsomething in curlies')

 

You could make the regex a little more elaborate, but I don't think the problem would go away.

 

EDIT:

Constants are a great, easy way to do this. PHP won't let you redeclare them

 

I'm not a huge fan of loading my config files with constants, but it is an option.

 

I'd probably go with something like:

function config($key)
{
$config = include 'config.php';

if (array_key_exists($key, $config)) {
	return $config[$key];
}

return null;
}

 

config.php

return array(
'db_prefix' => 'prefix_'
);

 

[xyph]

Eh, constants exist for this reason, IMO. Your config file is going to get loaded with key/value pairs regardless, it makes more sense to store them in a way that's immutable.

 

If you gotta have things grouped, and you want a function to check if a given configuration exists, why not use a static class?

 

<?php

class config {
const DB_PREFIX = 'prefix_';
const ETC = 'etc';

public static function getConstant($name) {
	if( defined('self::'.$name) )
		return constant('self::'.$name);
	return FALSE;
}
}

?>

 

In your case, you can just use an array within the class, or properties instead of constants... Again though, I don't see what you have against constants

[scootstah]

Again though, I don't see what you have against constants

 

Nothing, they just seem clunky for this purpose. What if you need multiple database connections with different credentials?

[xyph]

Good point, suppose if you're making something as flexible as a framework, inflexible things like constants can be annoying.