title: PHP & mySQL: Repeating entries problem - Need each entry to show up only once but they repeat
body: <p><strong>MY PLATFORM:</strong></p>
  
  <p>PHP &amp; 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 &amp; 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>