Saving global site settings [PHP / MySQL]

Question

Can anyone recommend best practice for storing general site settings? For example, the default page title if the script parameter is not set, or the number of displayed items displayed in the content window, or a list of thumbnails that the system should do when loading the image. Centralizing these values ​​has an obvious advantage, making it easy to change preferences that can be used on many pages.

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

This table is unlikely to ever become significant, so I'm not too worried about performance. The rest of my schema is relational. This makes some damn ugly requests, 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

It looks the way you do it.

If you are worried that your queries look ugly, you can try a little clean up your SQL.

Here's a cleaner version of the query you asked 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, such as:

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

You can get your preferences using this query:

 SELECT getPreference('server_path_to_http') 

You sacrifice some speed without having your hard settings (obviously). But if you plan to enable the "site administrator" to change the default settings - you must save them in the database.

Answer 2

In my application, I use this structure:

 CREATE TABLE `general_settings` ( `setting_key` varchar(255) NOT NULL, `setting_group` varchar(255) NOT NULL DEFAULT 'general', `setting_label` varchar(255) DEFAULT NULL, `setting_type` enum('text','integer','float','textarea','select','radio','checkbox') NOT NULL DEFAULT 'text', `setting_value` text NOT NULL, `setting_options` varchar(255) DEFAULT NULL, `setting_weight` int(11) DEFAULT '0', PRIMARY KEY (`setting_key`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; 

Sample data:

 mysql> select * from general_settings; +-----------------------------+---------------+------------------------------+--------------+-------------------------------+---------------------------------------+----------------+ | setting_key | setting_group | setting_label | setting_type | setting_value | setting_options | setting_weight | +-----------------------------+---------------+------------------------------+--------------+-------------------------------+---------------------------------------+----------------+ | website_name | website | Website Name | text | s:6:"DeenTV"; | NULL | 1 | 

I store the serialized value in the setting_value column. I got this trick from Wordpress to save the settings in a database.

Column

setting_options used for select , radio or checkbox setting_type . It will contain the serialized value of the array. In admin, this value will be displayed as parameters, so the administrator can select one of them.

Since I use CodeIgniter, I have a model to get a single value from a specific setting_key , so it is pretty easy to use.

Answer 3

I think this is an absolutely acceptable structure, especially for small configurations like you.

You can also save these settings in a .ini file and call parse_ini_file . If you need more flexibility than INI allows (for example, nested arrays, etc.), you can simply put them in a .php file and include this.

If you still want to go with the configuration in the database, then (given that there are only a few lines), maybe just read all the records in one pass and cache it.

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

Answer 4

I would think that going with the included file will help you figure it out a bit more, especially if you want to include the array as one of your variables. If you plan to change the configuration variables on the fly, then it might be better to use them, but if it remains relatively static, I would recommend the file "config.php"

Answer 5

Many applications, including, for example, Wordpress, use serialization and unserialization. This allows you to create a very simple table structure, possibly even with one record (for example, with the id_id for your project).

All your (many, many) variables in the array are serialized into a string and saved. Then they are returned and non-esterized back to the array structure.

Pro: You don’t have to plan the ideal configurations in advance by making a lot of ALTER TABLE materials.

Con: You cannot search your serialized array structure using SQL.

Teams

 string serialize ( mixed $value ) mixed unserialize ( string $str ) 

Also works with your objects. Non-serialization of an object can use the __wakeup () method.

Answer 6

Just create a configure class and save each value you want in the class variable.

include this class in all files that are called.

Now you can access this class in all files and declare a global function in all functions, access to the configuration class.

I hope for this help.

Answer 7

My approach to this problem is to create a table, which is a separate column for each configuration variable, as well as for any other data set, and to set the primary key so that the table cannot contain more than one record. I do this by setting the primary key as an enumeration with a single valid value, for example:

 CREATE TABLE IF NOT EXISTS `global_config` ( `row_limiter` enum('onlyOneRowAllowed') NOT NULL DEFAULT 'onlyOneRowAllowed',#only one possible value `someconfigvar` int(10) UNSIGNED NOT NULL DEFAULT 0, `someotherconfigvar` varchar(32) DEFAULT 'whatever', PRIMARY KEY(`row_limiter`)#primary key on a field which only allows one possible value ) ENGINE = InnoDB; INSERT IGNORE INTO `global_config` () VALUES ();#to ensure our one row exists 

After you make this setting, any of the values ​​can then be changed using a simple UPDATE statement, viewed using a simple SELECT statement combined with other tables that will be used in more complex queries, etc.

Another advantage of this approach is that it allows you to use the correct data types, foreign keys, and all other things that are relevant to the database design to ensure the integrity of the database. (Just make sure your foreign keys are on DELETE SET NULL or ON DELETE RESTRICT, not ON DELETE CASCADE). For example, let's say that one of your configuration variables is the user identifier of the main site administrator, you can extend the example as follows:

 CREATE TABLE IF NOT EXISTS `global_config` ( `row_limiter` enum('onlyOneRowAllowed') NOT NULL DEFAULT 'onlyOneRowAllowed', `someconfigvar` int(10) UNSIGNED NOT NULL DEFAULT 0, `someotherconfigvar` varchar(32) DEFAULT 'whatever', `primary_admin_id` bigint(20) UNSIGNED NOT NULL, PRIMARY KEY(`row_limiter`), FOREIGN KEY(`primary_admin_id`) REFERENCES `users`(`user_id`) ON DELETE RESTRICT ON UPDATE CASCADE ) ENGINE = InnoDB; INSERT IGNORE INTO `global_config` (`primary_admin_id`) VALUES (1);#assuming your DB is set up that the initial user created is also the admin 

This ensures that you always have a valid configuration, even if the configuration variable must refer to some other object in the database.