How can I efficiently retrieve a large number of database settings as PHP variables?

Question

Currently all of my script's settings are located in a PHP file which I 'include'. I'm in the process of moving these settings (about 100) to a database table called 'settings'. However I'm struggling to find an efficient way of retrieving all of them into the file.

The settings table has 3 columns:

• ID (autoincrements)
• name
• value

Two example rows might be:

admin_user            john
admin_email_address   [email protected]  

The only way I can think of retrieving each setting is like this:

$result = mysql_query("SELECT value FROM settings WHERE name = 'admin_user'");
$row = mysql_fetch_array($result);
$admin_user = $row['value'];

$result = mysql_query("SELECT value FROM settings WHERE name = 'admin_email_address'");
$row = mysql_fetch_array($result);
$admin_email_address = $row['value'];

etc etc

Doing it this way will take up a lot of code and will likely be slow.

Is there a better way?

Answer 1

100 settings? Load them all at once. That will take no time at all. You absolutely do not want to load them one at a time.

$result = mysql_query('SELECT * FROM settings');
$settings = array();
while ($row = mysql_fetch_assoc($result)) {
  $settings[$row['name']] = $row['value'];
}

If you need to compartmentalize these somehow, depending on how you need to do it, you could put a category or something on the table and then just load all the settings in a particular category.

What I would suggest is abstracting this behind an object of some kind:

class Settings {
  private $settings;

  public function __get($name) {
    if (!$this->settings)) {
      $result = mysql_query('SELECT * FROM settings');
      $this->settings = array();
      while ($row = mysql_fetch_assoc($result)) {
        $this->settings[$row['name']] = $row['value'];
      }
    }
    return $this->settings[$name];
  }
}

This way the settings aren't loaded until you try and access one:

$settings = new Settings;
echo $settings->admin_name; // now they're loaded

Answer 2

$result = mysql_query('SELECT * FROM settings');
$settings = array();
while ($row = mysql_fetch_assoc($result)) {
$settings[$row['name']] = $row['value'];
}

Thanks but that seems to output the following:

john  
[email protected]

whereas it should be:

$admin_user = "john";  
$admin_email_address = "[email protected]";

Also, I forgot to mention, this would be inside a function so I think I need to make the retrieved variables global?

To others:

Thanks, I did consider keeping the settings in the file.

The thing is, all of the settings will need to be editable in the script's admin panel. So I guess whenever the user saves their settings in the admin panel it would have to create a PHP file containing the settings which then overwrites the exisiting settings file?

But won't the user's settings then be overwritten when they upload the next version of the script?

Answer 3

Well I seem to have figured it out:

$settings = mysql_query("SELECT * FROM settings");

while ($row = mysql_fetch_assoc($settings)) {
eval('global $' . $row['name'] . ';');
eval('$' . $row['name'] . ' = "' . $row['value'] . '";');
}

It works although I wasn't keen on using eval(), but I think it's the only way.

I'm now wondering whether many hosts have eval() disabled. Any thoughts?