(PHP & mySQL) Treat rows as columns

Question

Hi all, I am working on PHP & mySQL based website. I am trying to setup the 'Settings' page in administration panel where I can enter different settings to manage the site. The settings can range upto 100 (or even more) depending upon the requirements. So instead of making 100 columns (and increase if I have to add more columns in future), I want to store the data in row wise format and fetch the values as if I am fetching it from columns.

REFERENCE:

I found a similar real life implementation of such feature in the most popular blogging tool 'Wordpress'. For reference, it is the 'wp_options' table that I am talking about.(Please correct me I am wrong)

EXAMPLE:

Here's a quick example of what (& why) I am trying to do it that way:

--Table settings

P.KEY   option_name      option_value
1       site_name        XYZ site inc.
2       siteurl          http://www.xyz.com
3       slogan           Welcome to my XYZ site
4       admin_email      [email protected]
5       mailserver_url   mail.xyz.com
6       mailserver_port  23
..... etc.

As you can see from above, I have listed very few options and they are increasing in number. (Just for the records, my installation of Wordpress has 902 rows in wp_options table and I did not see any duplicate option_name). So I have the feeling that I am well off if I apply the same working principle as Wordpress to accomodate growth of the settings. Also I want to do it so that once I save all the settings in DB, I want to retrieve all the settings and populate the respective fields in the form, for which the entries exist in DB.

ONE OF MY CODE TRIALS:

--
-- Table structure for table `settings`
--

CREATE TABLE IF NOT EXISTS `settings` (
  `set_id` tinyint(3) NOT NULL auto_increment,
  `option_name` varchar(255) NOT NULL,
  `option_value` varchar(255) NOT NULL,
  PRIMARY KEY  (`set_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Dumping data for table `settings`
--

INSERT INTO `settings` (`set_id`, `option_name`, `option_value`) VALUES
(1, 'site_name', 'XYZ site inc.'),
(2, 'slogan', 'Welcome to my XYZ site');

$result = mysql_query("SELECT option_name, option_value FROM settings");
$defaults = array('option_name', 'option_value');


while( list($n, $v) = mysql_fetch_array($result) )
{
 $defaults['option_name'] .= $n;
 $defaults['option_value'] .= $v;
}

echo  $defaults['option_name'].'---'.$defaults['option_value'].'<br />';

//The above code gives me the following Output:
//site_nameslogan---XYZ site inc.Welcome to my XYZ site

When I run the above query, I also receive 2 PHP Notices that says:

Undefined index: option_name

Undefined index: option_value

I would appreciate any replies that could show me the PHP code to retrieve the options successfully and eliminate the Undefined index issues as well. Also, like I mentioned earlier, I want to retrieve all the existing settings and populate the respective fields in the form when I visit the settings page next, after storing the data.

Thanks fly out to all in advance.

Answer 1

This should do the trick:

$defaults = array('option_name' => array( ), 'option_value' => array( ) );

while( list($n, $v) = mysql_fetch_array($result) )
{
 $defaults['option_name'][] = $n;
 $defaults['option_value'][] = $v;
}

Then in your view iterate over $defaults['option_name'] and $defaults['option_value']

Answer 2

PHP gives you warning because $defaults['option_name'] and $defaults['option_value'] are not being initialized before they are used in .= operation.

So just put

$defaults['option_name'] = '';
$defaults['option_value'] = '';

before the loop and warning will go away.

The rest of the code is completely correct, although you don't have to have set_id column at all since every setting will have unique name, that name (option_name column) can be used as primary key.

Another thing that you can improve your code, is to use $defaults differently, like so

$defaults[$n] = $v;

Then you can use every setting on its own without looking through two huge strings.

$site_url = $defaults['site_url'];
foreach ($defaults as $name => $value) {
   echo $name, ' = ', $value, '<br>';
}