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, simon_w

[SOLVED] Data from non silverstripe sources


Reply

3 Posts   1101 Views

Avatar
Chris J

28 January 2011 at 2:43pm Community Member, 10 Posts

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

29 January 2011 at 12:41am Forum Moderator, 1796 Posts

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

2 February 2011 at 1:05pm Community Member, 10 Posts

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