Geek Answers Handbook

PHP & mySQL: a problem with duplicate records - it is necessary that each record is displayed only once, but they are repeated

MY PLATFORM:

PHP and mySQL

WHAT I AM HERE:

I have 4 tables, namely "books", "book_type", "book_categories", "all_categories".

WHAT I TRY TO DO:

In simple words, I want to display all the books that are in stock, that is, in_stock = 'y', with all the information related to the book, from all the tables, only once, without repeating the entries. Each of the books is currently being repeated, and I want to show them only once.

CURRENT PROBLEM:

In the interface in my application, records are displayed again when in fact, when I expect them to appear only once (as in DISTINCT / UNIQUE), and not be repeated.

MY LIGHTING:

, , . , . ? , 1 4 , book1 4 . book2 2 , 2 .

:

PHP mySQL, . , GROUP_CONCAT mySQL, (1024?). , GROUP_CONCAT. . .

:

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'

:

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

:

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

.

+5
6

, , . PHP, , :

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

..

Edit:

, 100 , , , , . PHP, .

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

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');
+3

, GROUP_CONCAT() MySQL. http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

- :

SELECT stuff, GROUP_CONCAT(DISTINCT category ORDER BY category DESC SEPARATOR ', ') 
GROUP BY category, price, book_type
+1

CONCAT_WS , - :

SELECT  books.book_title, 
    books.book_price, 
    book_type.book_type, 
    CONCAT_WS(', ', (   
                        SELECT  all_categories.category
                          FROM  all_categories 
                         WHERE  all_categories.cat_id = book_categories.cat_id
                     )
             ) AS book_category_list
 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
    WHERE books.in_stock = 'y'

, .

+1

. . :

EDIT: , - concat ( ), , , . :

SET GLOBAL group_concat_max_len = SOME_BIG_VALUE; #SOME_BIG_VALUE >> 1024

SELECT books.book_title,  books.book_price,
       book_type.book_type,
       GROUP_CONCAT(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'
GROUP BY books.book_title
+1

, GROUP_CONCAT, reset .

SET SESSION group_concat_max_len=@@max_allowed_packet;

SELECT books.book_title,  books.book_price,
       book_type.book_type,
       GROUP_CONCAT(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'
GROUP BY books.book_id;

SET SESSION group_concat_max_len=@@group_concat_max_len;
+1

Have you tried adding a separate one for your choice?

SELECT DISTINCT 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'
GROUP BY books.book_title
0
source

More articles:


All Articles