使用MySQL Vertical Designed表 [英] Working with MySQL Vertical Designed table

问题描述

我有一个html表单(根据日期，每个表单都有不同的页面)，有五个文本输入，分别称为date，inp1，inp2，inp3和inp4.

I have an html form (each form has different page, according to date), with five text inputs, called date, inp1, inp2, inp3 and inp4.

例如，我有数据库表，其结构如下: id |父|名称|值.在这种情况下，我要存储数据，并按父级将其添加到日期中，如下所示:

For example, I have database table, with structure like this: id | parent | name | value. In this case, I want to store my data, and adding it to date by parent, like this:

id | parent | name | value
1  | 0      | date | 20.07.2011
2  | 1      | inp1 | value-from-inp1
3  | 1      | inp2 | value-from-inp2
4  | 0      | date | 21.07.2011
5  | 4      | inp2 | value-from-inp2
6  | 1      | inp3 | value-from-inp3
7  | 4      | inp1 | value-from-inp1

以此类推...

但是问题从这里开始.我想创建一个选项，您可以为以前添加的值更新这些值.但是问题是，如何检查，是否

But problem starts here. I want to create option, that you can update those values for previously added. But the QUESTIONS are, how to check, if

1. 我已经为该日期分配了任何东西吗?

1. 如果已分配，如何确定并使用MySQL Update
2. 如果未分配，如何确定并为此使用MySQL插入

对于这个问题，我愚蠢的加载页面解决方案效率低了50％，听起来像这样(不用担心安全性，它只是一个模型):

My stupid and 50% more inefficient of loading page solution for this problem sounds like this (Don't worry about security, its just mock-up):

if($_POST['submit']){
$inp1_data = $db->get_row("SELECT name FROM table WHERE parent = ".$parent_id." and name='inp1'");
if($inp1_data){$db->update($query)}else{$db->insert($query)}

// ... and so on, four copies of same code, just replacing "inp1" to "inp2", "inp3"...
}

是的，我知道这是没有用的脚本，以防万一，如果我添加了一个或数百个inpNUMBER，它将是压缩测试站点，保证100％的超时，而不是有效的脚本.

Yes, I know, this is useless script, in case, if I have added one more, or hundreds of inpNUMBER, it would be crush test site, 100% guaranteed timeout, not working script.

推荐答案

在我看来，您要使用的单表实际上需要两个表.要回答您的问题，由于日期与值分开存储，因此无法使用单个 SELECT 来找出是否已将值分配给特定问题.您也许可以使用嵌套选择来做到这一点，但我不会尝试通过扭曲使其实现.

It sounds to me like you're trying to use a singe table where you really need two. To answer your question, because the date is stored separately from the value, there is no way to find out if you have a value assigned to a particular question using a single SELECT. You may be able to do this with a nested select, but I won't try to go through the contortions to make it happen.

考虑这种替代设计，该设计假定对于给定的提交不能重复给定的字段:

Consider this alternative design, which assumes that a given field can't be repeated for a given submission:

提交表

submission_id | date
1             | 20.07.2011
2             | 21.07.2011

答案表

submission_id | field_name | field_value
1             | inp1       | value-from-inp1
1             | inp2       | value-from-inp2
2             | inp2       | value-from-inp2
2             | inp4       | value-from-inp4
1             | inp3       | value-from-inp3

请注意，RandolfRincón-Fadul提出的将数据类型按字段分开的建议是一个很好的建议，但是在这里我并没有这样做以保持简单.现在，您可以通过加入表格来查找特定日期的答案.

Note that Randolf Rincón-Fadul's suggestion of keeping the data types separated by field is a good one, but I haven't done it here to keep things simple. Now you can find out if you have an answer for a particular date by joining the tables.

这篇关于使用MySQL Vertical Designed表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！