Don't know if this is the right forum, but when I want to show something from a database (from 2 tables) I get multiply values.

Example, These values I get from the database

  • Braindead - Action - Test - 2004-09-21

  • Bassie & Adriaan en de diamant - Action - Test - 2004-09-21

  • Braindead - Action - Prut - 2004-09-21

  • Bassie & Adriaan en de diamant - Action - Prut - 2004-09-21

With this SQL statement

$querylistdvd = "SELECT * FROM cdvd, lendout, cdvdgenre WHERE (cdvd.cdvdlendoutid <> '0' AND lendout.lendfromuserid=cdvd.userid AND lendout.lendfromuserid='$userid' AND cdvd.cdvdgenreid=cdvdgenre.genreid) ORDER BY lendout.lendoutdate";

Now in the datbase (phpmyadmin) I have 2 records.
Test has the movie Braindead and Prut has Bassie & Adriaan...

What is wrong with the SQL statement so that I only have 2 records instead of 4 on the php page ?

TIA.

    Not certain, but I think a JOIN should be in there somewhere.

      not knowing exactly what you are trying to accomplish... try this :

      $querylistdvd = "
SELECT 
*
FROM
cdvd
INNER JOIN lendout ON (cdvd.userid=lendout.lendfromuserid)
INNER JOIN cdvdgenre ON (cdvd.cdvdgenreid=cdvdgenre.genreid)
WHERE
lendout.lendfromuserid='$userid'
ORDER BY lendout.lendoutdate";

        I have created some screenshots to make it (hopefully 😉) more clear 😃

        Above are the result I get with the query (even more doubles with the query from trex005).

        And doing a SELECT shows me this in PHPMyAdmin

        As in the database there are 2 records wich I want to show and the page shows 4 of them. I think because of the connection with the the CDVD table wich also contains a lendoutid (cdvdlendoutid)
        This is because the DVD's I lendout to pals, the DVD title etc are stored in the CDVD table and the lendout information (date, time) are in the Lendout table.

          can you show us the select * from the other tables?

            Ofcourse, here you go 🙂

            Table CDVD

            Table CDVDGenre

              Write a Reply...