PHP & mySQL: Repeating entries problem - Need each entry to show up only once but they repeat

join, mysql, php

Solution

Best way to make sure you don't lose ANY data would be multiple queries. Query the tables seperately and join them in PHP, probably so your queries look like this

book_id book_title  book_price  book_type         
    1  My Book 1    10          Good           
    2  My Book 3    30          Better          
    3  My Book 4    40          Best           
    4  My Book 6    60          Good            
    5  My Book 9    90          Good           

    book_id, category
    1   Comedy
    1   Drama
    1   Romance
    2   Comedy

etc

Edit:

No, you shouldn't need 100 hits on the DB, just two, one to get the books, the next to get the categories. The looping would be done in PHP to loop through the second query and join the data with the first. The second query could be

SELECT book.book_id, all_categories.category FROM book_category JOIN all_categories on book_categories.cat_id=all_categories.cat_id JOIN books on books.book_id=book_categories.book_id WHERE books.in_stock= 'y';

OR

SELECT book_categories.book_id, all_categories.category FROM book_category 
JOIN   all_categories on book_categories.cat_id=all_categories.cat_id  
WHERE book_id IN   (SELECT book_id FROM books WHERE books.in_stock= 'y');

Problem

MY PLATFORM: PHP & mySQL WHAT I HAVE HERE: I have 4 tables, namely, 'books', 'book_type', 'book_categories', 'all_categories'. WHAT I AM TRYING TO DO: In simple words, I want to display all the books that are in stock i.e. in_stock = 'y', with all the book related information from all the tables, only once without repeating the entries. Currently the each of the books are repeated and I want to show them only once. THE CURRENT PROBLEM: In the frontend within my app., the entries are shown repeatedly when in fact when I am expecting them to show up only once (as in DISTINCT / UNIQUE) and not repeat themselves. MY SUSPICION: I suspect that the repeating data is because of the categories that each of the books belong to. Every single book entry is shown as many times, as it belongs to a category. Confusing? I mean that if a book1 belongs to 4 categories, then book1 is shown 4 times. If book2 belong to 2 categories, then it is shown 2 times. WHAT I NEED: I need the PHP & mySQL code that would solve the above problem. I am hoping that we can solve the problem without using GROUP_CONCAT in mySQL as there's a limit (1024 ?) for the same. A book can belong to many categories and I do not want to risk losing any data by using GROUP_CONCAT. I would also like to do this in a single query without accessing the database repeatedly in a loop. Thanks for understanding. All the tables and the corresponding data to replicate the problem are as follows: ``` CREATE TABLE IF NOT EXISTS `books` ( `book_id` int(11) NOT NULL auto_increment, `book_type_id` int(11) NOT NULL, `book_title` varchar(50) NOT NULL, `book_price` smallint(4) NOT NULL, `in_stock` char(1) NOT NULL, PRIMARY KEY (`book_id`), KEY `book_type_id` (`book_type_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; -- -- Dumping data for table `books` -- INSERT INTO `books` (`book_id`, `book_type_id`, `book_title`, `book_price`, `in_stock`) VALUES (1, 1, 'My Book 1', 10, 'y'), (2, 1, 'My Book 2', 20, 'n'), (3, 2, 'My Book 3', 30, 'y'), (4, 3, 'My Book 4', 40, 'y'), (5, 2, 'My Book 5', 50, 'n'), (6, 1, 'My Book 6', 60, 'y'), (7, 3, 'My Book 7', 70, 'n'), (8, 2, 'My Book 8', 80, 'n'), (9, 1, 'My Book 9', 90, 'y'), (10, 3, 'My Book 10', 100, 'n'); -- -- Table structure for table `book_type` -- CREATE TABLE IF NOT EXISTS `book_type` ( `book_type_id` int(11) NOT NULL auto_increment, `book_type` varchar(50) NOT NULL, PRIMARY KEY (`book_type_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; -- -- Dumping data for table `book_type` -- INSERT INTO `book_type` (`book_type_id`, `book_type`) VALUES (1, 'Good'), (2, 'Better'), (3, 'Best'); -- -- Table structure for table `book_categories` -- CREATE TABLE IF NOT EXISTS `book_categories` ( `book_id` int(11) NOT NULL, `cat_id` int(11) NOT NULL, PRIMARY KEY (`book_id`,`cat_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `book_categories` -- INSERT INTO `book_categories` (`book_id`, `cat_id`) VALUES (1, 1), (1, 2), (1, 3), (1, 4), (1, 5), (2, 1), (2, 2), (3, 1), (3, 2), (3, 3); -- -- Table structure for table `all_categories` -- CREATE TABLE IF NOT EXISTS `all_categories` ( `cat_id` int(11) NOT NULL auto_increment, `category` varchar(50) NOT NULL, PRIMARY KEY (`cat_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; -- -- Dumping data for table `all_categories` -- INSERT INTO `all_categories` (`cat_id`, `category`) VALUES (1, 'Comedy'), (2, 'Drama'), (3, 'Romance'), (4, 'Horror'), (5, 'Trivia'), (6, 'Puzzles'), (7, 'Riddles'), (8, 'Kids'), (9, 'Gents'), (10, 'Ladies'); ``` MY TARGET: ``` //MY QUERY: SELECT books.book_title, books.book_price, book_type.book_type, all_categories.category FROM books LEFT JOIN book_type ON books.book_type_id = book_type.book_type_id LEFT JOIN book_categories ON books.book_id = book_categories.book_id LEFT JOIN all_categories ON book_categories.cat_id = all_categories.cat_id WHERE books.in_stock = 'y' ``` CURRENT OUTPUT: ``` book_title book_price book_type category My Book 1 10 Good Comedy My Book 1 10 Good Drama My Book 1 10 Good Romance My Book 1 10 Good Horror My Book 1 10 Good Trivia My Book 3 30 Better Comedy My Book 3 30 Better Drama My Book 3 30 Better Romance My Book 4 40 Best NULL My Book 6 60 Good NULL My Book 9 90 Good NULL ``` NEED THE FOLLOWING OUTPUT: ``` book_title book_price book_type category My Book 1 10 Good Comedy, Drama, Romance, Horror, Trivia My Book 3 30 Better Comedy, Drama, Romance My Book 4 40 Best NULL My Book 6 60 Good NULL My Book 9 90 Good NULL ``` Thanks to all in advance.

Original source