Doesn't show data on PHP but on workbench the SQL is correct

Question

If I run the sql query on workbench, I can see the data. But on PHP there is nothing. I found this part doesn't work.

echo '<ul><li>'.$title1.'</li>';
echo '<li>'.$content1.'</li>';   
echo '<li>'.$answer1.'</li></ul>';

Does anyone know how to solve it?

                  $qtitle =$_GET['id'];

                  $sql ="select Q1.title, Q1.content, Q1.answer from questionset_question as Q2";
                  $sql .=" inner join question as Q1 on Q1.question_id=Q2.question_id inner join questionset as Q3";
                  $sql .=" on Q3.questionset_id=Q2.questionset_id where Q3.title='$qtitle'";

                  if ($result = $db->query($sql)) { 
                      while ($row = $result->fetch_assoc()) {   
                        $title1=$row['Q1.title'];
                        $content1=$row['Q1.content'];
                        $answer1=$row['Q1.answer'];

                        echo '<ul><li>'.$title1.'</li>';
                        echo '<li>'.$content1.'</li>';   
                        echo '<li>'.$answer1.'</li></ul>'; 
                      }

                      $result->free();
                    }
                    else {
                      echo "no result";
                    }

What debugging have you done? What query parameter value are you using in the URL for the `id` parameter? Is your PHP script looking at the same database as your MySQL Workbench? Do you see _"no result"_ or something else (or nothing at all)? — Phil, May 14 '20 at 23:40
Learn to use parameterized queries. Your program is vulnerable to SQL injections. — sticky bit, May 14 '20 at 23:51
I added echo 'test'; to the end of the while loop, then I can see the 'test' twice since there are two rows of data in MySQL. — Jianguo Jin, May 14 '20 at 23:54
You're missing out on valuable error messages due to your PHP configuration. See https://stackoverflow.com/questions/845021/how-can-i-get-useful-error-messages-in-php — Phil, May 15 '20 at 00:03

Ahmet Kürşat AKICI · Answer 1 · 2020-05-15T00:09:00.687

1

can you Try this way?

while ($row = $result->fetch_assoc()) {   
 $title1=$row['title'];
 $content1=$row['content'];
 $answer1=$row['answer']

Delete Q1 table name. Because the result in pdo returns without table name. MySQL workbench can list column name with table name.

edited May 15 '20 at 00:09

answered May 14 '20 at 23:52

Ahmet Kürşat AKICI

70
2

Nicely spotted. FYI, OP is using MySQLi, not PDO (would be the same problem / answer though) – Phil May 15 '20 at 00:02
Stick bit is says true.please use parameterized queries for your securit. – Ahmet Kürşat AKICI May 15 '20 at 00:03

Nava Bogatee · Answer 2 · 2020-05-29T15:01:05.120

In PHP or other web applications it is always good idea to see the contents of the variable before proceeding ahead and displaying the data

Here I would use print_r() function to see what $result contains inside.

if ($result = $db->query($sql)) { 

    //check the value in $result.. used only for debugging
    print_r($result);
    exit('debugging');

     while ($row = $result->fetch_assoc()) {   
         $title1 = $row['Q1.title'];
         $content1 = $row['Q1.content'];
         $answer1 = $row['Q1.answer'];
         ...
     }

Doesn't show data on PHP but on workbench the SQL is correct

2 Answers2