CODEWITHSUNDEEP
phpmysql
emen
emen

Reputation: 6318

SQL query only returns 3 rows

I have 3 tables and I want it to join so I can display all the records in it in an HTML table.

The tables are as follows:

  1. laporan
  2. doktor
  3. pesakit

I used the query SELECT * FROM laporan NATURAL JOIN doktor NATURAL JOIN pesakit; to join all the tables.

I've entered 5 records in laporan table because it only refers doktor and pesakit as foreign keys. I've also used the phpmyadmin query tool to display the records with this query.

The problem is, when I use that query, among those 5 records, only 3 can be displayed. What's the problem? There was nothing wrong with the mysql_fetch_array() in the php code.

EDIT:

This is how the table code looks like:

<?php
        $result = mysql_query("SELECT * FROM laporan NATURAL JOIN doktor NATURAL JOIN pesakit;");

        $i = 1;

        while($row = mysql_fetch_array($result)){
            echo "<tr>";
            echo "<td>" . $i++ . "</td>";
            echo "<td>" . $row['doktor_nama'] . "</td>";
            echo "<td>" . $row['pesakit_nama'] . "</td>";
            echo "<td>" . $row['laporan_tarikh'] . "</td>";
            echo "</tr>";
        }


        echo "</tbody>
        </table>";

?>

Database structure are as follows:

doktor:

pesakit:

laporan:

Upvotes: 0

Views: 452

Answers (2)

BellevueBob
BellevueBob

Reputation: 9618

In addition to adding columns to a query, INNER JOINS eliminate rows. If you are expecting your query to return five rows and are only getting three, I suspect that two rows in your laporan table have keys that are either NULL or not in the two reference tables.

So, try using LEFT OUTER JOIN instead, something like this:

SELECT lap.*
     , d.doktor_nama
     , p.pesakit_nama
FROM laporan lap
LEFT OUTER JOIN doktor d
   on lap.aporan_doktor_id =d.ID  
LEFT OUTER JOIN pesakit p
   on lap.laporan_pesakit_id =p.ID

Upvotes: 3

neelsg
neelsg

Reputation: 4842

You should use INNER JOIN instead of NATURAL JOIN like this:

SELECT * FROM laporan
    INNER JOIN doktor ON laporan.laporan_doktor_id = doktor.id
    INNER JOIN pesakit ON laporan.laporan_pesakit_id = pesakit.id

Upvotes: 1

Related Questions