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.

General Questions /

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

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

[SOLVED] Data from non silverstripe sources


Go to End
Reply


3 Posts   1328 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, 1830 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 %>