Take the 2-minute tour ×

Lets say that I have a MySQL table that looks like this :

 BookTitle    Chapter    DateAdded  
  Book1          3       2012-12-15
  Book1          2       2012-12-16 
  Book1          1       2012-12-15
  Book2          2       2012-12-16

I want to show it in HTML so it can look like this :

 Book2      2
 Book1      2,3,1  

Basically I want to get rid of the title of the book if existed! and group all the chapters together and sort them by the date

I was thinking of using Group By but all my experiments did not work. Please give me a solution or an advice. I am able to use PHP and MySQL I am currently using a Smarty template on this project.

share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

Actually, you can do this in MySQL. Use GROUP_CONCAT function.

SELECT BookTitle, GROUP_CONCAT(Chapter) ChapterList
FROM tableName
GROUP BY BookTitle

Other Source

share|improve this answer
what if I wanted to put a limit on the "chapters results" so it can only show the two most recent chapters. I tried GROUP_CONCAT(Chapter ORDER BY DateAdded DESC, Chapter DESC Limit 0,2) but it is not working! –  shnisaka Dec 23 '12 at 6:25
@shnisaka GROUP_CONCAT doesn't accept a LIMIT parameter. From what I can tell, you'd have to use string manipulation functions (in MySQL query or in PHP) to get the text from the beginning of the string up to the second comma. –  user113215 Dec 23 '12 at 6:57
@user113215 is right shnisaka. Other way is to modify the query but you need to read some important points here: How to select the first/least/max row per group in SQL –  John Woo Dec 23 '12 at 7:00
add comment

Your Answer


