StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POPHP & mySQL: Repeating entries problem - Need each entry to show up only once but they repeat
text
Body
copied!<p><strong>MY PLATFORM:</strong></p> <p>PHP & mySQL</p> <p><strong>WHAT I HAVE HERE:</strong></p> <p>I have 4 tables, namely, 'books', 'book_type', 'book_categories', 'all_categories'. </p> <p><strong>WHAT I AM TRYING TO DO:</strong></p> <p>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.</p> <p><strong>THE CURRENT PROBLEM:</strong></p> <p>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.</p> <p><strong>MY SUSPICION:</strong></p> <p>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.</p> <p><strong>WHAT I NEED:</strong></p> <p>I need the <strong>PHP & mySQL code</strong> 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.</p> <p>All the tables and the corresponding data to replicate the problem are as follows:</p> <pre><code>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'); </code></pre> <p><strong>MY TARGET:</strong></p> <pre><code>//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' </code></pre> <p><strong>CURRENT OUTPUT:</strong></p> <pre><code>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 </code></pre> <p><strong>NEED THE FOLLOWING OUTPUT:</strong></p> <pre><code>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 </code></pre> <p>Thanks to all in advance.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload