3066 Posts in 866 Topics by 648 members
|
Page:
1
|
Go to End | |
| Author | Topic: | 764 Views |
-
Help with SQLQuery
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?
-
Re: Help with SQLQuery
12 August 2011 at 7:49pm Last edited: 12 August 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;}
-
Re: Help with SQLQuery
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.
-
Re: Help with SQLQuery
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:
http://api.silverstripe.org/2.4/sapphire/model/SQLQuery.htmlIn 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
... -
Re: Help with SQLQuery
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
-
Re: Help with SQLQuery
4 October 2011 at 10:30pm Last edited: 4 October 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
-
Re: Help with SQLQuery
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
| 764 Views | ||
|
Page:
1
|
Go to Top |
