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.

We've moved the forum!

Please use forum.silverstripe.org for any new questions (announcement).
The forum archive will stick around, but will be read only.

You can also use our Slack channel or StackOverflow to ask for help.
Check out our community overview for more options to contribute.

General Questions /

General questions about getting started with SilverStripe that don't fit in any of the categories above.

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

[SOLVED] Data from non silverstripe sources


Go to End


3 Posts   1798 Views

Avatar
Chris J

Community Member, 10 Posts

28 January 2011 at 2:43pm

On one page type I need to populate an HTML table using PHP and MySQL onto the page.
The data is not from a Silverstripe database.

in static HTML/PHP I'm doing something like this:

<table>
<?php
	while ($row = mysql_fetch_array($query)) {
		echo "<tr>";
		echo "<td>".$row[data]."</td>";
		echo "<td>".$row[otherdata]."</td>";
		echo "<td>".$row[etc]."</td>";
		echo "</tr>";
	}
?>
</table>

This data is created and frequently updated from another application on the same server

How could I do this in a template (.ss) file so it can be displayed for the page type?
Do I need to create a silverstripe data object for this data?

Avatar
swaiba

Forum Moderator, 1899 Posts

29 January 2011 at 12:41am

this is from banal...

//In your _config.php you create two db connection settings, something along these lines: 

global $databaseConfig;
$databaseConfig = array( 
   'type' => 'MySQLDatabase', 
   'server' => 'localhost', 
   'username' => 'usernameA', 
   'password' => 'passwordA', 
   'database' => 'databaseA' 
);

global $databaseConfig2;
$databaseConfig2 = array( 
   'type' => 'MySQLDatabase', 
   'server' => 'localhost', 
   'username' => 'usernameB', 
   'password' => 'passwordB', 
   'database' => 'databaseB' 
);

//Then in a function where you need both DBs, you do something like this: 

public function doStuff() { 
   global $databaseConfig, $databaseConfig2; 
    
   // connect to DB 2 
   DB::connect($databaseConfig2); 
    
   // create a query.. this will go to "databaseB" 
   $query = new SQLQuery("*", "MyTable");

   $result = $query->execute();

   // do something with the result... 
    
   // when done, switch back to the regular DB Config 
   DB::connect($databaseConfig); 
}

this will convert the returned data into a DataObjectSet - that is easily parsed on in the template...

$records = DB::query($strSQL);
$dos = new DataObjectSet();
$rec = $records->next();
while ($rec) {
	$do = new DataObject($rec);
	$dos->push($do);
	$rec = $records->next();
}

hope this helps!

Avatar
Chris J

Community Member, 10 Posts

2 February 2011 at 1:05pm

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