Jump to:

23367 Posts in 18143 Topics by 2863 members

General Questions

SilverStripe Forums » General Questions » [SOLVED] Data from non silverstripe sources

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

Page: 1
Go to End
Author Topic: 1071 Views
  • Chris J
    Avatar
    Community Member
    10 Posts

    [SOLVED] Data from non silverstripe sources Link to this post

    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?

  • swaiba
    Avatar
    Forum Moderator
    1784 Posts

    Re: [SOLVED] Data from non silverstripe sources Link to this post

    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!

  • Chris J
    Avatar
    Community Member
    10 Posts

    Re: [SOLVED] Data from non silverstripe sources Link to this post

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

    1071 Views
Page: 1
Go to Top

Want to know more about the company that brought you SilverStripe? Then check out SilverStripe.com

Comments on this website? Please give feedback.