需要修复的PHP文件中的SQL查询 [英] SQL query in PHP file in need of a fix
问题描述
我问了一个类似的问题，但是无法完全得到给我想要的结果的查询，它输出了一个问题，但不是正确的问题.我将在这里更好地阐明这个问题，并对表进行一些修改. 我有一个程序，如果有人键入让我说话"，该程序会向用户询问表格中的多个问题(例如20个qns)
I have asked a similar question, however couldn't quite get the query to give me the results I wanted, it output a question, but not the right one. I will clarify the question a bit better here and have modified the tables a bit. I have a program where if a person types 'lets talk' the program asks users a number of questions from a table, (eg. 20 qns)
我有3张桌子.
(peopleiknow) (questions) (questionsasked)
NID FName QID Question QID PID Answer
1 Mark 1 Are you human 1 1 'Definately yes.'
2 Sue 2 Do you like soup? 1 2 'Most of the time.'
2 2 'Yes especially tomato.'
(*问的表开头可以是空白)
(* the questionsasked table can be blank at first)
基本上，程序会为不在问题表中的用户"$ name"获得第一个问题.然后提出问题并将答案添加到问题表中. (起初，我试图找到正确的问题)
Basically the program gets the first question for a user '$name' that is not in the questionsasked table. Then asks the question and adds the answer to the questionsasked table. (At first I am trying to get the right question)
要与php交互的javascript/jQuery的片段如下:
A snippet of the javascript/jQuery to go to interface with the php is as follows:
var uname='Lindsay'; //will be assigned dynamically after I get this fixed.
$.post('ajax/getQuestions.php', { name: uname },function(data2) {
var qn = data2.value1; //question
var pid = data2.value2; // PID
var qid = data2.value3; //QID
alert(qn);
alert(pid);
alert(qid);
var answer1 = prompt(qn, 'Please answer here');
$.post('ajax/addQuestionAsked.php', {answer: answer1, PID: pid, QID: qid},
function(data3) {alert('added question');} );
//$('div#PlaceOfResponse').html(data3);}
});
PHP文件的重要部分如下:
The important part of the PHP file is as follows:
* 连接部分被省略* $ link是连接
*connection part omitted * $link is the connection
if (isset($_POST['name']) && !empty($_POST['name']))
{
$name = trim($_POST['name']); //could do better protection..
$query1 = "SELECT * //(q.Question, q.QID, p.person)?
FROM questions q
INNER JOIN questionsasked qa
ON q.QID = qa.QID
INNER JOIN peopleiknow p
ON qa.PID = p.NID
WHERE qa.Asked='N' OR qa.Asked IS NULL
AND p.Person = '$name'";
上面的查询效果不佳，还尝试了以下注释中的一个..但没有效果.
The above query didn't quite work and also tried the one in comments below.. but didn't work..
/*
$query1 = "SELECT FIRST(Question)
FROM questions q
WHERE NOT EXISTS
(SELECT *
FROM questionsasked qa
RIGHT JOIN peopleiknow p
ON p.NID = qa.PID
INNER JOIN questions q
ON qa.QID = q.QID
WHERE q.QID = qa.QID
AND p.FName = '$name')";
*/
if ($result = mysqli_query($link, $query1))
{
if (0 == mysqli_num_rows($result))
{
echo('all asked');
}
else
{
/* fetch associative array */
$row = mysqli_fetch_assoc($result);
{header('Content-Type: application/json');
$question = $row['Question'];
$PID = $row['PID'];
$QID = $row['QID'];
$response = array('value1' => $question, 'value2' => $QID, 'value3' => $PID);
echo json_encode($response);
}
}
}
}
else {
?><script>alert('$name');</script>
<?php
echo 'variable \'name\' not set properly';}
/* close connection */
mysqli_close($link);
?>
我几乎可以正常工作了，但是当我将问题的答案添加到所问的表中，然后刷新并说让我说话"时，它又回问了同样的问题. 然后我稍微修改了代码，现在js文件中的警报正在发出警报 (未定义)返回的所有3个变量.(在我稍稍修改表之前就起作用了). 现在看不到为什么这样做了.
I had it almost working, but when I added the answer to the questionsasked table, and refreshed and said 'lets talk' it went back and asked the same question. Then I changed the code a little and now the alerts in the js file are now alerting (undefined) for all 3 variables that got sent back.. (was working before I modified the tables a bit). now can't see why it's doing that.
这是我最近一次查询尝试:
This is my latest attempt at a query:
$query1 = "SELECT FIRST(q.question,p.NID,q.QID)
FROM questions q
LEFT JOIN questionsasked qa
ON q.QID = qa.QID
LEFT JOIN peopleiknow p
ON qa.PID = p.NID
WHERE qa.QID
NOT IN (SELECT * FROM questionsasked WHERE q.QID = qa.QID)
AND p.FName = '$name'";
推荐答案
您可能需要考虑阅读左联接的工作原理
You might want to consider reading how left join works
尝试这样的事情:
"SELECT *
FROM questions q
LEFT JOIN questionsasked qa
ON q.QID = qa.QID
and qa.PID = (select NID from peopleiknow where FName = '$name')
WHERE qa.PID IS NULL "
这次我用SQLite DB进行了验证:-) 由于左联接中的Missin PID，以前的版本不起作用！
this time i verified it with an SQLite DB :-) pervious version did not work because of the missin PID in the Left Join!
问候，
马丁
这篇关于需要修复的PHP文件中的SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
