Jump to:

3373 Posts in 998 Topics by 712 members

Data Model Questions

SilverStripe Forums » Data Model Questions » Running a basic MySQL query with DataObject::get

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

Page: 1
Go to End
Author Topic: 2751 Views
  • Herries
    Avatar
    Community Member
    12 Posts

    Running a basic MySQL query with DataObject::get Link to this post

    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 NewsPage.ss

    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;
    //SELECT * FROM
    }

    /*EXAMPLE CODE For NewsPage.ss*/
    <% control NewsYear %>
    $Year
    <% end_control %>

    God bless!

  • Devlin
    Avatar
    Community Member
    186 Posts

    Re: Running a basic MySQL query with DataObject::get Link to this post

    Well, you could do:

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

    Bad idea?

    edit:
    Actually, what you want to do is:

    /*EXAMPLE CODE For NewsPage.ss*/
    <% control NewsYear %>
    $Date.Year
    <% end_control %>

    http://api.silverstripe.org/2.4/sapphire/model/SS_Datetime.html

  • Herries
    Avatar
    Community Member
    12 Posts

    Re: Running a basic MySQL query with DataObject::get Link to this post

    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!

  • Herries
    Avatar
    Community Member
    12 Posts

    Re: Running a basic MySQL query with DataObject::get Link to this post

    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 NewsPage.ss using the following snippet:

    <% control NewsYear %>
    $Year
    <% end_control %>

    Praise the Lord!

  • Mad_Clog
    Avatar
    Community Member
    78 Posts

    Re: Running a basic MySQL query with DataObject::get Link to this post

    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.

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