homepage Welcome to WebmasterWorld Guest from 23.22.179.210
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved