[ How to create wordpress-like option table and get values for each row? ]
I'm looking to create an options table in my db that makes every record a system option, so I can work with a little number of fields.
My db has the following structure:
- 3 columns named
id
,name
, andvalue
The following data is inserted as an example:
+--+-----------+--------------------------+
|id|name |value |
+--+-----------+--------------------------+
| 1|uri |www.example.com |
| 2|sitename |Working it out |
| 3|base_folder|/folder1/folder2/ |
| 4|slogan |Just a slogan for the site|
+--+-----------+--------------------------+
That way I can include a large number of customizable system options very easily.
The problem is that I don't know how to retrieve them.
How do I get the value of uri and
store it as a var?
And better yet, how do I get, for exmaple, values of id
1 and 4 only without making a query each time? (I assume multiple queries are useless and a pretty ugly method.)
I know the question is pretty basic but I'm lost here. I'd really appreciate your answer!
Answer 1
Here's something to get you started. No error checking or other niceties included. I assume your db name is $db_name and the table you describe above is called 'site_params'.
To get a single parameter from your table...
// select your db
mysql_select_db($db_name);
// get the uri setting
$query = "SELECT value FROM site_params WHERE name='uri'";
$result = mysql_query( $query );
If there aren't too many settings, you could just get all of them at once...
// get all the settings
$query = "SELECT * from site_params";
$result = mysql_query( $query );
Or get some of them...
// get uri and sitename
$query = "SELECT * from site_params where name in ('uri', 'sitename') ";
$result = mysql_query( $query );
You can receive the result as an array called $site_info by adding...
$site_info = mysql_fetch_array($result, MYSQL_ASSOC);
Clarification: this puts a single row in $site-info. Call it repeatedly to get multiple rows of the result.
To change one of the parameters...
// set new sitename
$newsitename = 'http://mynewsite.com';
$query = "UPDATE site_params SET value='" . $newsitename . "' where name='sitename'";
$result = mysql_query( $query );
Is that what you're looking for? Go to http://php.net for all the info you need. For example:
http://php.net/manual/en/function.mysql-query.php
Answer 2
$values = array();
$q = "SELECT * FROM table";
$resu = mysql_query($q);
while ($row = mysql_fetch_array($resu)) {
$values[$row['name']] = $row['value'];
}
usage:
echo $value['variable_name'];