Storing global site preferences [PHP/MySQL]

Question

Can anyone recommend the best practice for storing general site preferences? For example, the default page title if the script doesn't set one, or the number of featured items to display in a content box, or a list of thumbnail sizes that the system should make when a picture is uploaded. Centralizing these values has the obvious benefit of allowing one to easily alter preferences that might be used on many pages.

My default approach was to place these preferences as attribute/value pairs in a *gulp* EAV table.

This table is unlikely ever to become of a significant size, so I'm not too worried about performance. The rest of my schema is relational. It does make for some damn ugly queries though:

$sql = "SELECT name, value FROM preferences"
.    " WHERE name = 'picture_sizes'"
.    " OR name = 'num_picture_fields'"
.    " OR name = 'server_path_to_http'"
.    " OR name = 'picture_directory'";
$query = mysql_query($sql);
if(!$query) {
    echo "Oops! ".mysql_error();
}
while($results = mysql_fetch_assoc($query)) {
    $pref[$results['name']] = $results['value'];
}

Can anyone suggest a better approach?

Answer 1

That looks fine the way you're doing it.

If you're worried that your queries are looking ugly, you could try cleaning up your SQL a bit.

Here's a cleaner version of the query you gave in your question:

SELECT name, value FROM preferences
WHERE name IN ('picture_sizes','num_picture_fields','server_path_to_http','picture_directory')";

Or perhaps create a stored function to return a preference value; for example, using a stored function like this:

DELIMITER $$

CREATE FUNCTION `getPreference` (p_name VARCHAR(50)) RETURNS VARCHAR(200)
BEGIN
  RETURN (SELECT `value` FROM preferences WHERE `name` = p_name);
END $$

DELIMITER ;

You could get your preferences using a query like this:

SELECT getPreference('server_path_to_http')

You sacrifice a bit of speed by not having your preferences hard-coded (obviously). But if you plan to enable a "site administrator" to change the default preferences - you should keep them in the database.