How to check within query results for variable to change same query's while loop?

Question

I can simply not figure this out. I am quite sure I am doing it the wrong way, but I am fairly new to PHP and SQL.

What I am doing is generating these HTML 'question blocks' in a while loop, these contain a little query information, including a voting button. What I want to do is to remove that voting button if the user has already voted.

I am using a query to check if the User has already voted using the QuestionID and the user's IP address.

I am able to stop the user from voting, (the button does nothing), but I can't for the life of me, remove the button for the questions the user has already voted on.

Below is a code I have tried to write to accomplish this (IP address is gotten elsewhere)

//Query for selecting the information I want in the blocks.
$stmt = $conn->prepare("SELECT * FROM question_answers
    INNER JOIN question
    ON question_answers.QuestionID=question.QuestionID
    INNER JOIN answers
    ON question_answers.AnswerID=answers.AnswerID
    WHERE HasBeenTray = 0 OR HasBeenTray = 1 AND QuestionVotes > 2000
    ORDER BY QuestionVotes DESC LIMIT 8");
    $stmt->execute();
    $result = $stmt->get_result();

//Checking to see if Query has generated any result (rows)
    if ($result->num_rows > 0) {

//Counter for generating HTML at the right place
      $counter = 0;
      echo "<div class=\"row tabs\">";
      echo "<h2>Top 8 over stillede spørgsmål:</h2>";

//Use results from first query to generate HTML
      while($row = $result->fetch_assoc()) {

//Save QuestionAnswerID - Id of the question block clicked
        $id = $row["QuestionAnswerID"];

//Second query to check if QuestionAnswerID and UserID (IP Address) has already been paired
        $stmt = $conn->prepare("SELECT * FROM user_votes where UserID = ? and QuestionAnswerID = ?");
        $stmt->bind_param('ss', $ip_long, $id);
        $stmt->execute();
        $result = $stmt->get_result();

//Second while loop to generate 'question blocks' without vote button 
        while($row = $result->fetch_assoc()) {
          $counter++;
            echo "<div class=\"col-md-3\"><h3>". $row["Answer1Text"]. " vs. ". $row["Answer2Text"]. " </h3><p>". $row["QuestionText"]. "</p></div>";
          if($counter % 4 == 0) {
            echo "</div><div class=\"row tabs\">";
          }
        }
//Generate rest of 'question blocks' with voting buttons

          $counter++;
            echo "<div class=\"col-md-3\"><h3>". $row["Answer1Text"]. " vs. ". $row["Answer2Text"]. " </h3><p>". $row["QuestionText"]. "</p><p><a  data-ks-load-selector=\"#change\" href=\"index.php?id=". $row["QuestionAnswerID"]. "\" class=\"btn btn-success\"> " . $row["QuestionVotes"] . "</a></p></div>";
          if($counter % 4 == 0) {
            echo "</div><div class=\"row tabs\">";
          }
        }echo "</div>";

Tables with elements of importance:
question - QuestionID(PK), QuestionText
answers - AnswerID(PK), Answer1Text, Answer2Text
question_answers - QuestionAnswerID(PK), AnswerID(FK), QuestionID(FK), QuestionVotes
user_votes - QuestionAnswerID(FK), UserID

reaanb · Accepted Answer · 2015-12-12 17:57:03Z

1

At first, I changed $stmt and $result inside the loop to $stmt2 and $result2 so as not to overwrite the original resultset, and used an if to generate the vote button if the user hasn't voted.

However, I don't like running queries in a loop, so we can rather use a left join and check whether we got a matching UserID or not. Additionally, I modified your query to select fields explicitly. This can prevent field name conflicts and it helps performance to limit the size of your result.

//Query for selecting the information I want in the blocks.
$stmt = $conn->prepare("SELECT qa.QuestionAnswerID, a.Answer1Text, a.Answer2Text, q.QuestionText, uv.UserID, qa.QuestionVotes
    FROM question_answers qa
    INNER JOIN question q
    ON qa.QuestionID=q.QuestionID
    INNER JOIN answers a
    ON qa.AnswerID=a.AnswerID
    LEFT JOIN user_votes uv
    ON uv.UserID = ? AND uv.QuestionAnswerID = qa.QuestionAnswerID
    WHERE HasBeenTray = 0 OR HasBeenTray = 1 AND qa.QuestionVotes > 2000
    ORDER BY qa.QuestionVotes DESC LIMIT 8");
    $stmt->bind_param('s', $ip_long);
    $stmt->execute();
    $result = $stmt->get_result();

//Checking to see if Query has generated any result (rows)
    if ($result->num_rows > 0) {

//Counter for generating HTML at the right place
      $counter = 0;
      echo "<div class=\"row tabs\">";
      echo "<h2>Top 8 over stillede spørgsmål:</h2>";

//Use results from first query to generate HTML
      while($row = $result->fetch_assoc()) {

//Save QuestionAnswerID - Id of the question block clicked
        $id = $row["QuestionAnswerID"];

        $counter++;
        echo "<div class=\"col-md-3\"><h3>". $row["Answer1Text"]. " vs. ". $row["Answer2Text"]. " </h3><p>". $row["QuestionText"]. "</p>";
//If user hasn't voted, generate vote button 
        if (is_null($row['UserID']) {
            echo "<p><a  data-ks-load-selector=\"#change\" href=\"index.php?id=". $row["QuestionAnswerID"]. "\" class=\"btn btn-success\"> " . $row["QuestionVotes"] . "</a></p>";
        }
        echo "</div>";
        if($counter % 4 == 0) {
          echo "</div><div class=\"row tabs\">";
        }
      }echo "</div>";

edited Dec 12, 2015 at 17:57

answered Dec 12, 2015 at 17:16

reaanb

10.1k2 gold badges27 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Nenn Over a year ago

Thank you for your answer - that is a more structured version of my code. But I sadly think that it is my second query which is at fault. It is saying that Answer1Text, Answer2Text and QuestionText (which should have been generated by the first query) are undefined? Is it even possible to properly run 1 query within the loop of another? Again, I am very new to this and I just tried it cause I couldn't think of anything else.

reaanb Over a year ago

I didn't notice it at first, but you're overwriting your $result variable in the loop. I'll update my answer.

Nenn Over a year ago

This is completely and wonderfully fantastic, thank you very much for the help. It works like a charm!

Collectives™ on Stack Overflow

How to check within query results for variable to change same query's while loop?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related