Welcome to WebmasterWorld Guest from 54.163.164.67

Forum Moderators: open

Message Too Old, No Replies

[mySql] Ordering books in function of most recent chapter

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

Junior Member

5+ Year Member

joined:Nov 30, 2008
posts: 42
votes: 0


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 July 1, 2011 (gmt 0)

Senior Member

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

joined:Nov 28, 2004
posts:7999
votes: 0


'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.
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members