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.

We're retiring the forums!

The SilverStripe forums have passed their heyday. They'll stick around, but will be read only. We'd encourage you to get involved in the community via the following channels instead:

Data Model Questions /

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

Running a basic MySQL query with DataObject::get

Go to End

5 Posts   4564 Views


Community Member, 12 Posts

5 August 2010 at 8:44am

Hi... I've been programming for years but I've never been stumped like this. Please help!!!!

I'm new to silverstripe and I've been searching for two days and can't seem to find a GOOD tutorial on how to do a simple sql query in my NewsPage.php and then retrieve the results in my

I need to translate the query: SELECT DISTINCT SUBSTRING(`Date`,1,4) AS Year FROM `ArticlePage`

using the 'DataObject::get' method.

If anyone can translate the above query into something like the function below, I will be eternally grateful!!!!!!!!!!!!!!!!

/*EXAMPLE FUNCTION For NewsPage.php*/
function NewsYear($num=3) {
$news = DataObject::get_one("NewsPage");
return ($news) ? DataObject::get("ArticlePage", "ParentID = $news->ID", "Date DESC", "", $num) : false;

<% control NewsYear %>
<% end_control %>

God bless!


Community Member, 344 Posts

5 August 2010 at 9:18am

Edited: 05/08/2010 9:31am

Well, you could do:

$Date = substr($ArticlePage->Date,0,4);
$DateYear = date('Y',$ArticlePage->Date);

Bad idea?

Actually, what you want to do is:

<% control NewsYear %>
<% end_control %>


Community Member, 12 Posts

5 August 2010 at 6:03pm

Hi Jay!

Thanks for your reply! My real problem is the structure of the function. What would the function in the controller (NewsPage.php) look like for the query "SELECT DISTINCT SUBSTRING(`Date`,1,4) AS Year FROM `ArticlePage`" ?

Many thanks!


Community Member, 12 Posts

5 August 2010 at 6:21pm

Hi I found the solution using code from someone else's post:

My function in the NewsPage.php controller now looks like:

function NewsYear() {

$sqlQuery = new SQLQuery();
$sqlQuery->select = array(
'DISTINCT SUBSTRING(`Date`,1,4) AS Year');
$sqlQuery->from = array("ArticlePage_Live");
//$sqlQuery->where = array("ds_language_photos.pAlt LIKE '%SIA%'");
// get the raw SQL
$rawSQL = $sqlQuery->sql();
// execute and return a Query-object
$result = $sqlQuery->execute();
//setup our blank DataObjectSet to push SQL result data into it.
$dataObject1 = new DataObjectSet();
foreach($result as $row) {
//move the SQL result data to the DataObjectSet
$dataObject1->push(new ArrayData($row));

return $dataObject1;

And I retrieve the distinct Year I selected above in my using the following snippet:

<% control NewsYear %>
<% end_control %>

Praise the Lord!


Community Member, 78 Posts

5 August 2010 at 7:32pm

On a side note, you do realise that MySQL has loads of date and time functions right?
Other then that, yeah use the SQLQuery class for DISCINCT and COUNT queries.