Question

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      admin@xyz.com
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

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> ;
}

Answer 2

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 ]

友情链接