这个查询有效吗？我想更新一个表，连接两个不同的表，并将数据显示为两种不同的形式

So, what I'm trying to do is: I've got pageA.php to get the user's name, surname and question saved to the DB (With forms and Jquery). I'm displaying those questions in another table (Used by the "Technical Team" to answer those questions. I want to as soon as I answer, not to show the question in the technical team page anymore, and to display the answer in pageA.php. I've tried several things but nothing quite right yet.

所以，我要做的是：我有pageA.php来获取用户的名字，姓氏和问题保存到DB（使用表单和Jquery）。我将这些问题显示在另一个表格中（由“技术团队”用来回答这些问题。我想尽快回答，不再在技术团队页面中显示问题，并在页面A中显示答案.php。我已经尝试了几件事，但还没有完全正确。

Here's the sql query that I'm not too sure about:

这是我不太确定的SQL查询：

    $Ssql = "UPDATE questions SET Qstatus=1 
    WHERE answers.customerName=users.name 
    FROM users, answers 
    INNER JOIN users 
    ON answers.userID=users.userID;";

What I'm trying to do is, change the Qstatus to 1 when the question is answered. 0 = Not answered, 1 = Answered. First Page to ask for details.

我要做的是，当问题得到解答时，将Qstatus更改为1。 0 =未回答，1 =已回答。要求详细信息的第一页。

Here's the code I'm using ATM to insert the data to the DB and to update if the question was answered.

这是我使用ATM将数据插入数据库并在问题得到解答时更新的代码。

        if ($_POST) 
{
    $staffName = test_input($_POST['staffName']);  
    $customerName = test_input($_POST['customerName']);
    $answer = test_input($_POST['answer']);

    try 
    {
        $host = '127.0.0.1';
        $dbname = 'webapp';
        $user = 'root';
        $pass = '';
        $DBH = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
    } 

    catch (PDOException $e) 
    {
        echo "An error occurred saving your details. Please, try again!";
    }

    $sql = "INSERT INTO `answers` (`staffName`, `customerName`, `answer`) VALUES (?,?,?);";
    $sth = $DBH->prepare($sql);

    $sth->bindParam(1, $staffName, PDO::PARAM_INT);
    $sth->bindParam(2, $customerName, PDO::PARAM_INT);
    $sth->bindParam(3, $answer, PDO::PARAM_INT);

    $sth->execute();

    $Ssql = "UPDATE questions SET Qstatus=1 WHERE answers.customerName=users.name FROM users, answers INNER JOIN users ON answers.userID=users.userID;";
    $Ssth = $DBH->prepare($Ssql);
    $Ssth->execute();
}

What I'm struggling with is how to change the Qstatus to 1, when the question was answered, and how to trigger to show a table in pageA.php with all answers when there's answers available. Hope it's understandable and all help possible is greatly appreciated! THANK YOU!!

我正在努力的是如何在问题得到解答时将Qstatus更改为1，以及如何在有可用答案的情况下触发在pageA.php中显示所有答案的表格。希望这是可以理解的，所有帮助都非常感谢！谢谢！！

1 个解决方案

#1

In your query have some issue so i modified.

在您的查询中有一些问题，所以我修改。

 $Ssql = "UPDATE questions,users,answers          
         SET Qstatus=1 
         WHERE answers.customerName=users.name   
         AND answers.userID=users.userID;";

Here may be add one conditon also. which is maintain question's anwser one-to-many relation. I dont know your datastructure of table so i cant add it here

这里也可以添加一个条件。这是维持问题的一对多关系。我不知道你的数据结构表，所以我不能在这里添加它

#1