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.

Data Model Questions /

Help with SQLQuery


Reply


7 Posts   1058 Views

Avatar
redactuk

Community Member, 117 Posts

12 August 2011 at 7:51am

I need to quickly and easily retrieve a value, so I've followed:

http://doc.silverstripe.org/sapphire/en/reference/sqlquery

and written a function:

   public function ResourceName()
   {

      $sqlQuery = new SQLQuery();
      $sqlQuery->select = array("
         EventResource.Title AS ResourceName
      ");
      $sqlQuery->from = array ("
          CalendarDateTime
         LEFT JOIN CalendarDateTime_Resources ON CalendarDateTime_Resources.CalendarDateTimeID = CalendarDateTime.ID
         LEFT JOIN EventResource ON EventResource.ID = CalendarDateTime_Resources.EventResourceID
      ");
      $sqlQuery->where = array ("
          CalendarDateTime.EventID = " . $this->Event()->ID . "
      ");

      $result = $sqlQuery->execute();
      
      //Debug::show($result);
      return $result;
      
   }

The query returns the correct value via phpmyadmin, yet the Debug result above displays:

MySQLQuery::__set_state(array( 'database' => MySQLDatabase::__set_state(array( 'dbConn' => NULL, 'active' => true, 'database' => 'reddemo_cms', 'supportsTransactions' => false, 'supressOutput' => false, 'tableList' => NULL, 'fieldList' => NULL, 'indexList' => NULL, 'schemaUpdateTransaction' => NULL, )), 'handle' => NULL, 'currentRecord' => NULL, 'rowNum' => -1, 'queryHasBegun' => false, ))

What am I doing wrong?

Avatar
rob.s

Community Member, 78 Posts

12 August 2011 at 7:49pm

Edited: 12/08/2011 7:49pm

Hi redactuc,

try the following syntax:

public function ResourceName()
{

$sqlQuery = new SQLQuery();
$sqlQuery->select("EventResource.Title AS ResourceName");
$sqlQuery->from("CalendarDateTime");
    $sqlQuery->leftJoin("CalendarDateTime_Resources", "CalendarDateTime_Resources.CalendarDateTimeID = CalendarDateTime.ID");
    $sqlQuery->leftJoin("EventResource", "EventResource.ID = CalendarDateTime_Resources.EventResourceID");
$sqlQuery->where("CalendarDateTime.EventID = " . $this->Event()->ID );

$result = $sqlQuery->execute();

//Debug::show($result);
return $result;

}

Avatar
redactuk

Community Member, 117 Posts

13 August 2011 at 3:14am

Thanks for your reply, but that did not make any difference. Solution was to add:

   foreach($result as $row){
    $dataResult = $row['ResourceName'];
   }

I guess I don't quite understan how SQLQuery works and why that was necessary, but at least it worked.

Avatar
rob.s

Community Member, 78 Posts

13 August 2011 at 6:09am

OK, i didn's recognize, that the function should return the value of the sql-query.

Take a look at the documentation:
[url]http://api.silverstripe.org/2.4/sapphire/model/SQLQuery.html[/url]

In your case you could do as follows:

...
if( $result = $sqlQuery->execute() ) {
return $result->value(); //   Returns the first column of the first record
} else {
return false;
}

Greetings,
Robert
...

Avatar
xavsio4

Community Member, 2 Posts

4 October 2011 at 10:14pm

Hello, I've reached that point too. I'm using a table outside the CMS. That table is managed by a third party but I still need to display its values within a template.

So far the query execution is ok because the loop in the template (<% control table %>) iterates as it should. However, I can't find the way to display the values of each rows.

The function which makes the query ends with "return $result". I guess I should use an array or something to be able to use $field1, $field2, $field3 of my resulting sql into the template.

Right now, using the field names as is don't display anything.

Regards,

xavier

Avatar
rob.s

Community Member, 78 Posts

4 October 2011 at 10:30pm

Edited: 04/10/2011 10:38pm

Hi xavsio4,

the most simple way (not best performance) could be:
Iterate through the results an build a DataObjectSet.

(Untested)

function MyRecords() {
//.....
$results = $query->execute();

$dos = new DataObjectSet();

if( $results ) foreach( $results as $row ) {
$dos->push( new ArrayData($row) );
}

return $dos;
}

Because the return type is a DataObjectSet you can iterate inside your template

<% control MyRecords %>
$Field1<br />
$Field2<br />
<% end_control %>

Further information:

http://doc.silverstripe.org/sapphire/en/reference/sqlquery

Greets, RObert

Avatar
xavsio4

Community Member, 2 Posts

4 October 2011 at 11:58pm

Hi Roberts,

Thank you very much, that solved my problem. Now I can display the values. It works like a charm.

Thanks again. There should be a repository of such tips somewhere, like a playground or something.

Many thanks.

Best Regards,

xavier