MySQL query working in workbench, but does not work via PHP interface

Question

I have a MySQL query that I know to be working in Workbench/Sequel Pro that appears to return empty in my PHP code:

$SQL = "SELECT * FROM staff, invigilation, exam, occupation, room, module
            WHERE staffID='$inputID'
            AND invigilation.examID=exam.exam_ID
            AND exam.exam_ID=occupation.examID
            AND room.room_ID=occupation.roomID
            AND exam.module_ID=module.module_ID
            AND staff.password='$inputPassword'";
            $result = mysql_query($SQL);

            if($db_field = mysql_fetch_assoc($result)) {

            echo "
            <table border='1'>
                <tr>
                    <th>Exam ID</th>
                    <th>Module ID</th>
                    <th>Module name</th>
                    <th>Duration</th>
                    <th>Start time</th>
                    <th>Room</th>
                </tr>";



                while ($db_field = mysql_fetch_assoc($result)) {
                echo "
                <tr>
                    ";
                    echo "<td>" . $db_field['exam_ID'] . "</td>";
                    echo "<td>" . $db_field['module_ID'] . "</td>";
                    echo "<td>" . $db_field['module_name'] . "</td>";
                    echo "<td>" . $db_field['duration'] . "</td>";
                    echo "<td>" . $db_field['start_datetime'] . "</td>";
                    echo "<td>" . $db_field['room_name'] . "</td>";

                    echo "
                </tr>";
                } 

            } else {
                    echo "Incorrect login details. Please try again";
                    die();
                }

The result of this code is the first table row (Exam ID, Module ID etc.) is displayed but the table content is not.

I cannot work out why this is happening. Other than the SQL query, the code is identical to code used elsewhere (where it is working fine).

That means that it's either not the same query in both or you aren't connecting to the same database. — duffymo
– duffymo, Commented Apr 30, 2014 at 12:24
how many rows returned by the query in the workbench? because you are eating the first one — guido
– guido, Commented Apr 30, 2014 at 12:24
What is this: if($db_field = mysql_fetch_assoc($result))?? — gbestard
– gbestard, Commented Apr 30, 2014 at 12:25

Halcyon · Accepted Answer · 2014-04-30 12:25:53Z

2

You are doing mysql_fetch_assoc twice. The first time it reads the record and prints the table header. The second time there are no more records so it doesn't print a row.

Change

if($db_field = mysql_fetch_assoc($result)) {

to

if(mysql_num_rows($result) > 0) {

answered Apr 30, 2014 at 12:25

Halcyon

57.7k10 gold badges93 silver badges131 bronze badges

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

1 Comment

Cohagen Over a year ago

This is correct. I had completely missed this error, as in this particular query there is only one result, and many results in similar queries.

Nilesh Sothiya · Accepted Answer · 2014-04-30 12:33:27Z

Try this. You are using mysql_fetch_assoc($result); two times, First time it get result and second time there is no result.

 $SQL = "SELECT * FROM staff, invigilation, exam, occupation, room, module
                WHERE staffID='$inputID'
                AND invigilation.examID=exam.exam_ID
                AND exam.exam_ID=occupation.examID
                AND room.room_ID=occupation.roomID
                AND exam.module_ID=module.module_ID
                AND staff.password='$inputPassword'";
                $result = mysql_query($SQL);
    $db_field = mysql_fetch_assoc($result)
                if($db_field != "" ) {

                echo "
                <table border='1'>
                    <tr>
                        <th>Exam ID</th>
                        <th>Module ID</th>
                        <th>Module name</th>
                        <th>Duration</th>
                        <th>Start time</th>
                        <th>Room</th>
                    </tr>";



                    while ($db_field) {
                    echo "
                    <tr>
                        ";
                        echo "<td>" . $db_field['exam_ID'] . "</td>";
                        echo "<td>" . $db_field['module_ID'] . "</td>";
                        echo "<td>" . $db_field['module_name'] . "</td>";
                        echo "<td>" . $db_field['duration'] . "</td>";
                        echo "<td>" . $db_field['start_datetime'] . "</td>";
                        echo "<td>" . $db_field['room_name'] . "</td>";

                        echo "
                    </tr>";
                    } 

                } else {
                        echo "Incorrect login details. Please try again";
                        die();
                    }

Collectives™ on Stack Overflow

MySQL query working in workbench, but does not work via PHP interface

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related