Welcome to WebmasterWorld Guest from 54.159.214.250

Forum Moderators: open

[mySql] Ordering books in function of most recent chapter

   
7:17 am on Jun 29, 2011 (gmt 0)

5+ Year Member



Hello everybody!

In mySql I have two tables 'Books'('id', 'title') and 'Chapters'('id', 'text', 'booksId', 'date'). Authors can add chapters to books. I would like to select all the books and order them so that the books whose chapter is more recent are displayed first. I tried with an inner join but I probably did it wrong.

The concept is first order chapters by descending date and retrieve the corresponding book
Can someone help me?

Thanks a lot!
3:37 pm on Jul 1, 2011 (gmt 0)

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member



'Books'('id', 'title') and 'Chapters'('id', 'text', 'booksId', 'date'). Authors can add chapters to books. I would like to select all the books and order them so that the books whose chapter is more recent are displayed first.


Date is a reserved word in mysql (function) so you should name it something else, or, `backtick` (NOT 'quote' or "quote") all your queries (below.) You also might make sure "date" is a datetime format. Assuming "date" reflects the last modified - that is, updated every time you update or add a chapter,

select `Books`.`id` as id,`Books`.`title` as title,`Chapters`.`text` as txt, date_format(`Chapters`.`date`,"%m/%d/%Y") as lastmod where `Books`.`id`=`Chapters`.`booksId` order by `Chapters`.`date` asc;

id|title|txt|lastmod
1234|Widgets|This is a chapter about widgets|07/01/2011

If it is indeed datetime, date_format(`Chapters`.`date`,"%m/%d/%Y %h:%i:s") will give you 07/20/2011 08:33:24

date_format() [dev.mysql.com]

Selecting fields is much more efficient than the clobbering of select *, and allows you to set the output field names using the 'as' keyword. It's also required in many joins - if you select both the id's,

select `Books`.`id` as bookid,`Chapters`.`id` as chapterid,.......

If it's not a lastmod and only when the record was created,

alter table Chapters add lastmod datetime not null default '0000-00-00 00:00:00';

select `Books`.`id` as id,`Books`.`title` as title,`Chapters`.`text` as txt, date_format(`Chapters`.`date`,"%m/%d/%Y") as created, date_format(`Chapters`.`lastmod`,"%m/%d/%Y %h:%i:s") as lastmod where `Books`.`id`=`Chapters`.`booksId` order by `Chapters`.`lastmod` asc;

id|title|txt|created|lastmod
1234|Widgets|This is a chapter about widgets|06/08/2011|07/01/2011 08:33:24

You'll have to update your scripting to accommodate the new field in inserts, updates, and selects.
 

Featured Threads

Hot Threads This Week

Hot Threads This Month