Hey thanks I've figured it out from yours and other peoples help.
Here's a basic, complete example if anyone else needs to do the same or similar.
Set up your other database connection first:
// mysite/_config.php
global $nonSSDatabaseConfig;
$nonSSDatabaseConfig = array(
'type' => 'MySQLDatabase',
'server' => 'localhost',
'username' => 'usernameB',
'password' => 'passwordB',
'database' => 'databaseB'
);
Create a function to output the required data
// mysite/code/Example_Controller.php
class Example_Controller extends Page_Controller {
function BuildDataFromNonSSDB() {
global $databaseConfig;
global $nonSSDatabaseConfig;
// connect to non-ss DB
DB::connect($nonSSDatabaseConfig);
// create a query... this will query the non-ss database
// for more on SQLQuery() uses see: http://doc.silverstripe.org/sapphire/en/reference/sqlquery
$query = new SQLQuery();
$query->select = array(
"data1",
"data2",
"data3"
);
$query->from("table_name");
$query->where("data_live='1'");
$query->orderby("data1 ASC");
// execute the query to use the data ;-)
$result = $query->execute();
//move the SQL result data into the DataObjectSet
$dos = new DataObjectSet();
foreach($result as $DataRow) {
$dos->push(new ArrayData($DataRow));
}
// when done with database, switch back to the regular DB Config
DB::connect($databaseConfig);
// return the DataObjectSet to use in the template
return $dos;
}
}
In the template the database table field names are used as control variables (alias names can also be used.)
// themes/themeName/templates/LayoutExample_Controller.ss
<% if BuildDataFromNonSSDB%>
<table>
<% control BuildDataFromNonSSDB%>
<tr>
<td>$data1</td>
<td>$data2</td>
<td>$data3</td>
</tr>
<% end_control %>
</table>
<% end_if %>