Skip to main content

This site requires you to update your browser. Your browsing experience maybe affected by not having the most up to date version.

Customising the CMS /

Simple SQL question


Reply


4 Posts   715 Views

Avatar
Mr. Neave

Community Member, 8 Posts

19 May 2012 at 11:37am

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?

Avatar
Sphere

Community Member, 44 Posts

19 May 2012 at 11:45am

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.

Avatar
Mr. Neave

Community Member, 8 Posts

19 May 2012 at 12:00pm

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

Avatar
Willr

Forum Moderator, 5513 Posts

20 May 2012 at 11:49am

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