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   1128 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, 1799 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 %>