检查表中的最新值并添加到它 [英] Check latest value in table and add to it

问题描述

因为我有几个项目"，每个项目都应该包含很多问题，所以我有一个问题页面，我可以在其中填写一个视频链接、四个答案和四个下拉列表，用户可以在其中为每个答案设置分数.

但是，在数据库中我有两个表.

一张表，问题"，列有:pid(project-id)、qid(question-id) 和 question_link.另一个表answer_det"包含以下列:pid、qid、aid、answer 和 points.

当我填写并执行我制作的问题页面时，每个答案都会得到一个 ID.在answer_det"表中，正在设置Pid、Aid、answer 和points.这是它的样子:

当我用 Pid=1 为第一个项目插入第一个问题时的问题"表:

我现在要做的是设置qid(question-id).我不知道该怎么做，但我认为我应该有一个代码来检查 pid 的最大 qid 并将其加 1，这样同一个项目的每个新问题都会得到一个新的 qid.如果 pid 不在表中，则 qid 应获得值1".

因此，如果您查看第一张图片，则每个显示的行上的 qid 都应为 1，因为所有四个答案都属于同一问题，这是 pid=1 项目的第一个问题.因此，如果我想向同一个项目添加一个问题，它应该看起来相同，但 qid=2 等等.如果然后我为项目 2 添加一个新的(第一)问题，qid 应该从 1 开始，依此类推.然后，如果我想为第一个项目再次添加一个新问题，代码应该检查最大 qid 是否为 2，其中 pid 为 1，然后插入一个带有答案但 qid=3 的新问题.

它应该在表格问题"上以几乎相同的方式工作，您在第二张图片中看到.创建第一个问题时，除了我描述的应该发生在answer_det"表上的内容之外，我希望第一个项目(pid=1 的那个)的第一个问题也有 qid=1 和我的链接填充.pid=1 的项目的第二个问题应该得到 qid=2.如果我为一个新项目添加第一个问题，那么它应该是 pid=2 和 qid=1.然后，如果我想在第一个问题上加上第三个问题，它应该看到 pid=1 有 2 个问题(qid=2)并在 is 上加 1，所以第三个问题看起来像 pid=1 和 qid=3.

这是我现在拥有的代码，它没有在两个表中的 qid 中插入任何内容.

信息存储成功";}别的{回声 mysql_error();}?>

解决方案

我有一个答案和建议给你 :]

答案:

如果你想给每个 question_det 添加 qid，你需要在插入 question_det 之前插入 question 并使用 mysqli_last_insert() 函数获取 qid:

$que = "INSERT INTO question VALUES('$pid5','','$video');";mysqli_query($mysqli,$que);$qid = mysqli_insert_id($conn);//获取最后插入的问题ID$que = "INSERT INTO answer_det VALUES('$pid5','$qid','$aid1','$answ1','$point1');";//等等

我的建议:

我认为您可能需要以不同的方式构建数据库.

这是你的结构

//题表|pid |qid |问题链接 ||-----|-----|--------------------||1 |0 |http://example.com |//question_det|pid |qid |援助|回答 |点 ||-----|-----|-----|--------|--------||1 ||1 |是 |10 |

使用完全不同的列作为 PRIMARY KEY 是不好的做法……例如，您的表问题 PRIMARY_KEY 应该是 qid 而不是 pid；对于 question_det 表同样的问题，PRIMARY_KEY 应该是 aid 而不是 pid 并且两者都应该设置为 AUTO_INCREMENT 所以每次你插入一个新问题或 question_det 时它会自动增加 qid 和援助专栏

你应该有这样的东西:

//创建一个名为project"的表来存储它们:|pid |项目名称||-----|--------------------||1 |项目 1 ||2 |惊人的项目！|//创建一个名为问题"的表:|qid |pid |问题 |问题链接 ||-----|-----|--------------------|------||1 |1 |超级问题|http:/example.com ||2 |1 |问题?|http:/example.com ||3 |2 |更多问题?|http:/example.com |//最后创建一个名为answer"的表|援助|qid |回答 |点 ||-----|-----|---------------|--------||1 |1 |是 |10 ||2 |1 |没有 |20 ||3 |1 |也许|30 ||4 |1 |我不知道|40 |

注意:每个表 ID 应设置为 AUTO_INCREMENT

PHP 代码应该是这样的:

数据库 SQL 结构也应该是这样的:

创建表`项目`(`pid` INT(11) NOT NULL AUTO_INCREMENT,`project_name` VARCHAR(45) NULL DEFAULT NULL，主键 (`pid`))引擎 = MyISAM默认字符集 = utf8;创建表`问题`(`qid` INT(11) NOT NULL AUTO_INCREMENT,`pid` INT(11) NULL DEFAULT NULL,`问题` VARCHAR(60) NULL DEFAULT NULL,`question_link` VARCHAR(300) NULL DEFAULT NULL,主键 (`qid`))引擎 = MyISAM默认字符集 = utf8;创建表`答案`(`aid` INT(11) NOT NULL AUTO_INCREMENT,`qid` INT(11) NULL DEFAULT NULL,`answer` VARCHAR(100) NULL DEFAULT NULL,`points` INT(11) NULL DEFAULT NULL,主键 (`aid`))引擎 = MyISAM默认字符集 = utf8;

Since I got several "projects" that should contain many questions each, I have a question-page where I fill a videolink, four answers and four drow-down lists where the user is able to set points for every answer.

However, in the database I have two tables.

One table, "question", with the columns: pid(project-id), qid(question-id) and question_link. The other table, "answer_det", has the following columns: pid, qid, aid, answer and points.

When I fill and execute my question page I have made so every answer gets an id. In the table "answer_det", Pid, Aid, answer and points is being set. This is how it looks like:

The "question" table when I insert the first question for the first project with Pid=1 :

What I want to do now is to also set the qid(question-id). I'm not sure how to do it, but I think that I should have a code that checks the maximum qid of the pid and add 1 to it so every new question for the same project get a new qid. If the pid isn't in the table, then the qid should get the value "1".

So if you look at the first picture, the qid should be 1 on every showed row since all the four answers belongs to the same question, which is the first one for the project with pid=1. So if I would like to add a question to the same project, it should look the same but with the qid=2 and so on. If I then add a new(first) question for the project 2, the qid should begin on 1 and so on. Then, if i would like to add a new question again for the first project, the code should check that the maximum qid is 2 where pid is 1, and then insert a new question with answers but with qid=3.

It should work at almost the same way on the table "question", which you see on the second picture. When the first question is created, except from what I described should happen on the "answer_det"-table, I want the first question for the first project(the one with pid=1) to also have qid=1 and the link that I filled. The second question for the project with pid=1 should then get qid=2. If i add a first question for a new project, it should then be pid=2 and qid=1. Then, if I want to att a third question to the first question it should see that pid=1 has 2 questions(qid=2) and add 1 to is so the third questions looks like pid=1 and qid=3.

This is the code that I have now, and nothing of it inserts anything in the qid in neither of the two tables.

<?php
ini_set('display_errors',1);
error_reporting(E_ALL);

if(mysqli_connect_errno())
{
	echo mysqli_connect_error();
}

$pid5 = $_POST['pid4'];

$video = $_POST['videolink1'];
    
$aid1 = $_POST['a1'];
$aid2 = $_POST['a2'];
$aid3 = $_POST['a3'];
$aid4 = $_POST['a4'];    
    
$answ1 = $_POST['ans1'];
$answ2 = $_POST['ans2'];
$answ3 = $_POST['ans3'];
$answ4 = $_POST['ans4'];
    
$point1 = $_POST['pointset1'];  
$point2 = $_POST['pointset2'];  
$point3 = $_POST['pointset3'];  
$point4 = $_POST['pointset4'];

$que = "INSERT INTO answer_det VALUES('$pid5','','$aid1','$answ1','$point1');";
$que .= "INSERT INTO answer_det VALUES('$pid5','','$aid2','$answ2','$point2');";
$que .= "INSERT INTO answer_det VALUES('$pid5','','$aid3','$answ3','$point3');";
$que .= "INSERT INTO answer_det VALUES('$pid5','','$aid4','$answ4','$point4');";
    
$que .= "INSERT INTO question VALUES('$pid5','','$video');";

$run = mysqli_multi_query($mysqli,$que);
if($run)
{
	echo "<br>Information stored successfully";

}
else
{
	echo mysql_error();
}


?>

解决方案

I have an answer and a suggestion for you :]

Answer:

If you want to add the qid to each question_det you need to insert the question before inserting the question_det and obtain the qid with mysqli_last_insert() function:

$que = "INSERT INTO question VALUES('$pid5','','$video');";

mysqli_query($mysqli,$que);

$qid = mysqli_insert_id($conn); // get last inserted ID of the question

$que = "INSERT INTO answer_det VALUES('$pid5','$qid','$aid1','$answ1','$point1');";
// and so on

My suggestion:

I think you may need to structure your database differently.

This is your structure

// question table
| pid | qid |    question_link   |
|-----|-----|--------------------|
|  1  |  0  | http://example.com |

// question_det
| pid | qid | aid | answer | points |
|-----|-----|-----|--------|--------|
|  1  |     |  1  | Yes    | 10     |

It's bad practice to use a totally different column as PRIMARY KEY... for example your table question the PRIMARY_KEY should be qid not pid; for the question_det table same problem the PRIMARY_KEY should be aid not pid and both should be set to AUTO_INCREMENT so every time you insert a new question or question_det it automatically increase the qid and aid columns

You should have something like this instead:

//create a table called 'project' to store them:
| pid |   project_name   |
|-----|------------------|
|  1  | project 1        |
|  2  | amazing project! |

//create a table called 'question':

| qid | pid |      question    |   question_link   |
|-----|-----|------------------|-------------------|
|  1  |  1  | super question   | http:/example.com |
|  2  |  1  | question?        | http:/example.com |
|  3  |  2  | more questions?  | http:/example.com |

// and finally create a table called 'answer'

| aid | qid |     answer    | points |
|-----|-----|---------------|--------|
|  1  |  1  | Yes           | 10     |
|  2  |  1  | No            | 20     |
|  3  |  1  | Maybe         | 30     |
|  4  |  1  | I do not know | 40     |

NOTE: Each table ID should be set to AUTO_INCREMENT

This is how the PHP code should look like:

<?php
ini_set('display_errors',1);
error_reporting(E_ALL);

$pid = $_POST['pid']; // project ID
$question = $_POST['question']; // question may have a 'title' ?
$video = $_POST['videolink1'];

// the asnwers!
$answ1 = $_POST['ans1'];
$answ2 = $_POST['ans2'];
$answ3 = $_POST['ans3'];
$answ4 = $_POST['ans4'];

// the points!
$point1 = $_POST['pointset1'];
$point2 = $_POST['pointset2'];
$point3 = $_POST['pointset3'];
$point4 = $_POST['pointset4'];

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

$query = "INSERT INTO question (pid,question,question_link) VALUES ({$pid},'{$question}','{$video}');";


if(mysqli_query($conn,$query)) {
    echo "<br>Question Information stored successfully!";

    $qid = mysqli_insert_id($conn); // get last inserted ID of the question

    $query = "INSERT INTO answer(qid, answer, points) VALUES ({$qid},'{$answ1}',{$point1});";
    $query .= "INSERT INTO answer(qid, answer, points) VALUES ({$qid},'{$answ2}',{$point2});";
    $query .= "INSERT INTO answer(qid, answer, points) VALUES ({$qid},'{$answ3}',{$point3});";
    $query .= "INSERT INTO answer(qid, answer, points) VALUES ({$qid},'{$answ4}',{$point4});";

    if(mysqli_multi_query($conn,$query)) {
        echo "<br>Questions stored successfully!";
    } else {
        echo mysqli_errno($conn);
    }
} else {
    echo mysqli_errno($conn);
}

The database SQL structure should be something like this as well:

CREATE TABLE `project` (
`pid` INT(11) NOT NULL AUTO_INCREMENT,
`project_name` VARCHAR(45) NULL DEFAULT NULL,
PRIMARY KEY (`pid`))
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8;

CREATE TABLE `question` (
 `qid` INT(11) NOT NULL AUTO_INCREMENT,
 `pid` INT(11) NULL DEFAULT NULL,
 `question` VARCHAR(60) NULL DEFAULT NULL,
 `question_link` VARCHAR(300) NULL DEFAULT NULL,
PRIMARY KEY (`qid`))
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8;

CREATE TABLE `answer` (
 `aid` INT(11) NOT NULL AUTO_INCREMENT,
 `qid` INT(11) NULL DEFAULT NULL,
 `answer` VARCHAR(100) NULL DEFAULT NULL,
 `points` INT(11) NULL DEFAULT NULL,
 PRIMARY KEY (`aid`))
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8;

这篇关于检查表中的最新值并添加到它的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！