| [mySql] Ordering books in function of most recent chapter
|
fm86
msg:4332428 | 7:17 am on Jun 29, 2011 (gmt 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!
|
rocknbil
msg:4333672 | 3:37 pm on Jul 1, 2011 (gmt 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.
|
|
|
