(PHP & mySQL) 将行视为列 [英] (PHP & mySQL) Treat rows as columns

问题描述

我正在研究 PHP &基于 mySQL 的网站.我正在尝试在管理面板中设置设置"页面，我可以在其中输入不同的设置来管理站点.根据要求，设置范围可达 100(或更多).因此，与其制作 100 列(如果我将来必须添加更多列，还会增加)，我想以行方式存储数据并获取值，就像我从列中获取它一样.

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.

参考:

我在最流行的博客工具Wordpress"中发现了此类功能的类似现实实现.作为参考，我说的是'wp_options'表.(请纠正我我错了)

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)

示例:

以下是我尝试这样做的一个简单示例(以及为什么):

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      [e-mail]
5       mailserver_url   mail.xyz.com
6       mailserver_port  23
..... etc.

从上面可以看出，我列出的选项很少，而且数量还在增加.(只是为了记录，我安装的 Wordpress 在 wp_options 表中有 902 行，我没有看到任何重复的 option_name).所以我有一种感觉，如果我应用与 Wordpress 相同的工作原理来适应设置的增长，我会过得很好.我也想这样做，一旦我将所有设置保存在数据库中，我想检索所有设置并填充表单中的相应字段，其中条目存在于数据库中.

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.

我的代码试验之一:

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

当我运行上述查询时，我还收到了 2 个 PHP 通知，内容为:

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

未定义索引:option_name

Undefined index: option_name

未定义索引:option_value

Undefined index: option_value

如果您能向我展示 PHP 代码以成功检索选项并消除未定义索引问题，我将不胜感激.此外，正如我之前提到的，在存储数据后，我想检索所有现有设置并在接下来访问设置页面时填充表单中的相应字段.

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.

提前感谢大家.

推荐答案

PHP 给你警告，因为 $defaults['option_name'] 和 $defaults['option_value'] 在用于 .= 操作之前没有被初始化.

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

所以就放

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

在循环和警告消失之前.

before the loop and warning will go away.

其余的代码是完全正确的，尽管您根本不需要 set_id 列，因为每个设置都有唯一的名称，该名称 (option_namecolumn) 可以用作主键.

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.

您可以改进代码的另一件事是，以不同的方式使用 $defaults，就像这样

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

这篇关于(PHP & mySQL) 将行视为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！