Jump to:

5540 Posts in 1738 Topics by 1224 members

Customising the CMS

SilverStripe Forums » Customising the CMS » Simple SQL question

Moderators: martimiz, Sean, biapar, Willr, Ingo, swaiba, simon_w

Page: 1
Go to End
Author Topic: 649 Views
  • Mr. Neave
    Avatar
    Community Member
    8 Posts

    Simple SQL question Link to this post

    Here's a SQL query that selects all of the unique month and year values from every instance of the class Post, which has a property, Date:

    $months = DB::query("SELECT DISTINCT DATE_FORMAT(`Post`.`Date`, '%M') AS `Month`, DATE_FORMAT(`Post`.`Date`, '%Y') AS `Year` FROM `Post` ORDER BY `Post`.`Date` DESC");

    I’d like to refine this query to include only Posts that are children of the Page running the query. Something like the following (except this doesn't actually work):

    $months = DB::query("SELECT DISTINCT DATE_FORMAT(`Post`.`Date`, '%M') AS `Month`, DATE_FORMAT(`Post`.`Date`, '%Y') AS `Year` FROM `Post` ORDER BY `Post`.`Date` DESC, WHERE parentID = '". $this->ID . "' ");

    What's the correct syntax?

  • Sphere
    Avatar
    Community Member
    44 Posts

    Re: Simple SQL question Link to this post

    Do you, by any chance, have a complete non-php-generated version of that query? That would make it easier.

    Basically, DATE_FORMAT() AS Month won't work in any way at first. it should simply be "MONTH(`DateField_Here`)" If I'm not mistaken, same goes for the year.

    You can't sort by Post.Date, if you use the AS feature, you want "SORT `Year`, `Month` DESC" method. Since you're disassembling the datefield, and trying to still address it as the original date-field, which is impossible, because you don't have a field "Date" in your query, you can't do that.

    You are calling things, then sorting by it's originator. Considering the time I post this, it's just probably a small pointer to where it's going wrong.

  • Mr. Neave
    Avatar
    Community Member
    8 Posts

    Re: Simple SQL question Link to this post

    Thanks.

    The first query works to return unique months and years - it correctly lists all of the unique month and year values from all of the Posts in the database.

    The part that's not working is filtering by ParentID, and it seems like it's a syntax problem. Here's the error from SilverStripe:

    [User Error] Couldn't run query: SELECT DISTINCT DATE_FORMAT(`Post`.`Date`, '%M') AS `Month`, DATE_FORMAT(`Post`.`Date`, '%Y') AS `Year` FROM `Post` ORDER BY `Post`.`Date` DESC, WHERE ParentID = '8' You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE ParentID = '8'' at line 1

  • Willr
    Avatar
    Forum Moderator
    5508 Posts

    Re: Simple SQL question Link to this post

    There happens to be a comma after your ORDERBY which shouldn't be there. Also, put the WHERE before the order by..

    SELECT [..] FROM [..] WHERE [..] ORDERBY [..]

    649 Views
Page: 1
Go to Top

Want to know more about the company that brought you SilverStripe? Then check out SilverStripe.com

Comments on this website? Please give feedback.