SQL query in PHP file in need of a fix

Question

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)

I have 3 tables.

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

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)

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

  });

The important part of the PHP file is as follows:

*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);

 ?>

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

Answer 1

You might want to consider reading how left join works

try something like this:

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

this time i verified it with an SQLite DB :-) pervious version did not work because of the missin PID in the Left Join!

Greetings,

Martin