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?

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;
      
   }

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.

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

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

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

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

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

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