[RESOLVED] How to display records from main table even if they don't exist in the join table?
Hello, I'm new to php and mysql, I searched all over before I post this question here, so a little help is really appreciated.
I have 5 tables, which are:
books (which is the main table to join with others)
And I have 4 join tables to join between the table books with the other 4 tables:
The problem is, that when there is a book that has a record in the books table, and does not have in books_locations, or in any of the other join tables, this book does not appear with the books on the web page, and it appears only if I add it to all the join tables, the question is, how to display all the books in books table on the page, even if some of them don't have a record in some of the join tables?
Here is the sql select and join statement:
$sql = "SELECT DISTINCT books.title AS Title, books.date_released AS Year, books.price AS Price, authors.name AS Author FROM books
ON books_covers.book_id = books.id
ON covers.id = books_covers.cover_id
ON books_authors.book_id = books.id
ON authors.id = books_authors.author_id
ON books_locations.book_id = books.id
ON locations.id = books_locations.location_id
ON books_languages.book_id = books.id
ON languages.id = books_languages.language_id
This solution resolved the problem but created another one!
Adding LEFT JOIN to the tables join clause really allowed displaying all the products, but the new problem occured is that this solution has made the id of all products disappear from the fetched results.
Originally Posted by Derokorian
Take a look, these are is the records array fetched from mysql database while the words LEFT JOIN removed from joining clause:
And this is when LEFT JOIN added to the joining clause:
 => Array ( [id] => 1 [brand] => Coleman [model] => 6509-C1 Sunglasses [condition] => New [availability] => [overview] => [description] => )
As you know is necessary to include the id of all the products with their arrays as all operations in the application depend on it.
 => Array ( [id] => [brand] => Coleman [model] => 6509-C1 Sunglasses [condition] => New [availability] => [overview] => [description] => )
Do you know how to resolve this please?