[ 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, and value

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'];