Printing result of mySQL query with INNER JOIN

Question

I have a working SQL statement that returned correct results when I checked it on MAMP.

SELECT `questions`.`questionID` AS question, `questions`.`questionText`, 
       `questions`.`categoryID`,`answers`.`answerID`,`answers`.`answerText`,
       `answers`.`isTrue`
FROM `questions`,`answers`
WHERE `questions`.`questionID` = `answers`.`questionID`

But I can't figure out how to print the output with php. Please help. This is the code:

<html>
<body>

<?php
    header('Content-Type: text/html; charset=utf-8');
    $con=mysqli_connect("localhost","root","root","Theory");
    // Check connection
    if (mysqli_connect_errno())
    {
        echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }
    $result = mysqli_query($con,"SELECT `questions`.`questionID` AS question, `questions`.`questionText`, `questions`   .`categoryID`, `answers`.`answerID`,`answers`.`answerText`,`answers`.`isTrue`
                                 FROM `questions`,`answers`
                                 WHERE `questions`.`questionID` = `answers`.`questionID`");

    if (!$result)
    {
        die('Error: ' . mysqli_error($con));
    }
   while($row = mysqli_fetch_array($result))
   {
       echo "{";
       echo "{" . $row['questions'.'questionID'] . "}";   //this is not the full print
       echo "{" . $row['questions'.'questionText'] . "}"; //just for chaking
       echo "}";
   }

    mysqli_close($con);
    ?>

</body>
</head>

I get:"{{}{}}{{}{}}{{}{}}{{}{}}{{}{}}{{}{}}{{}{}}{{}{}}" echoed.

Turn on error reporting, use error handlers and then come back. Also, learn about explicit SQL syntax. — Kermit, Jul 15 '13 at 15:22
@FreshPrinceOfSO I am just a beginner, so I'll appreciate some references. — Luda, Jul 15 '13 at 15:23
@Luda [Error reporting](http://php.net/manual/en/function.error-reporting.php), [error handler](http://php.net/manual/en/mysqli.error.php), [Explicit vs implicit SQL joins](http://stackoverflow.com/questions/44917/explicit-vs-implicit-sql-joins) — Kermit, Jul 15 '13 at 15:25

STT LCU · Accepted Answer · 2015-05-12T13:52:38.377

7

You're executing a query again inside your if condition... But the $sql query is empty because the variable is not defined!

replace if (!mysqli_query($con,$sql)) with if (!$result) since you have already executed the query in the rows above.

EDIT to answer the question's comments:

when you're fetching the resulting array, you don't need to specify the table alias but just the column name OR the column alias if present.

Try this:

while($row = mysqli_fetch_array($result))
{
   echo "{";
   echo "{" . $row['questionID'] . "}";   //this is not the full print
   echo "{" . $row['questionText'] . "}"; //just for checking
   echo "}";
}

edited May 12 '15 at 13:52

answered Jul 15 '13 at 15:25

STT LCU

4,348
4
29
47

1

+1 since your comment was faster than my answer (I had to make sure that the returned value was FALSE in the documentation). – Francisco Presencia Jul 15 '13 at 15:26
1

@FranciscoPresencia +1 to you too because your answer was nevertheless correct. – STT LCU Jul 15 '13 at 15:28

score 5 · Answer 2 · answered Jul 15 '13 at 15:24

$sql is aparently not set. You can do:

$result = mysqli_query($con,"SELECT `questions`.`questionID` AS question, `questions`.`questionText`, `questions`   .`categoryID`, `answers`.`answerID`,`answers`.`answerText`,`answers`.`isTrue`
                             FROM `questions`,`answers`
                             WHERE `questions`.`questionID` = `answers`.`questionID`");

if (!$result)
{
    die('Error: ' . mysqli_error($con));
}

Printing result of mySQL query with INNER JOIN

2 Answers2