Database

Raxan uses PDO (PHP Data Objects) to establish connections to databases such as (MySQL, Oracle, Postregress, etc). The correct PDO driver must be loaded before you can make a connection to the database.

Connecting to a Database

To connect to a database use the Raxan::connect() method by passing in the dsn, user id and password or a predefined database connection name:

<?php

    require_once('raxan/pdi/autostart.php');

    class NewPage extends RaxanWebPage {

        protected function _config() {
            $this->masterTemplate = 'views/master.html';
        }

        protected function _init($e) {
            try {
                // connect to MySQL Server
                $dsn = 'mysql:host=localhost;dbname=employees';
                $this->db = $this->Raxan->connect($dsn,'user','password',true);

                // connect to SQL Server using driver pdo_sqlserv 2.0+
                $dsn = 'sqlserv: server=localhost; database=employees';
                $this->db = $this->Raxan->connect($dsn,'user','password',true);

                // or use the db.default config parameter name
                // $this->db = Raxan::connect('default');
            }
            catch (Exception $e) {
                $this->halt('Error while connecting to Database Server.');
            }
        }
    }

?>

Note: Predefined database connection values can be stored within the configuration file. See Loading A Custom Configuration File. For more information on Microsoft SQL Server driver for PHP visit http://blogs.msdn.com/b/sqlphp/.

The Raxan::connect() method will establish a connection with the database and returns an instance of the RaxanPDO class. You can use the connection object in the same way you would use a normal PDO object.

Retrieve records from the Database

The example below shows how to use the RaxanPDO::table() helper method to retrieve data from a table:

<?php

    protected function _load($e) {

        // retrieve all records from employees table
        $rows = $this->db->table('employees');

        // retrieve columns first_name and last_name from employees table
        $rows = $this->db->table('employees first_name, last_name');

        // retrieve records from the employees table where last_name= john or williams
        $rows = $this->db->table('employees','last_name = ? or last_name = ?','james','williams');

        // use names parameters to filter records
        $params = array(':first' => 'John', ':last'=> 'Brown');
        $rows = $this->db->table('employees','first_name = :first or last_name = :last, $params);

        // once you have retrieved the records from the database you bind it to any element
        $this->list1->bind($rows);

    }

?>

In addition to using the table() method you can also use the execQuery() helper method to execute parameterized queries as shown below:

<?php

    protected function _load($e) {

        $sql = 'select o.*, c.fname, c.lname from order o '.
               'inner join customer c on c.id = o.customer_id '.
               'where o.date between ? and ?';
        $rows = $this->db->execQuery($sql,'2010-03-01', '2010-03-31');

    }

?>

Adding Records to a Table

To add a record to a table, use the RaxanPDO::tableInsert() helper method as shown below:

<?php

    protected function _load($e) {

        $data = array(
            'first_name' => 'Mary',
            'last_name' => 'Jane'
            'email' => 'maryj@somewhere.domain.com'
        );

        // add record to the employees table
        $rows = $this->db->tableInsert('employees',$data);

        // get new employee id
        $id = $this->db->lastInsertId();

    }

?>

Updating Records within a Table

To update a record within a table, use the RaxanPDO::tableUpdate() helper method as shown below:

<?php

    protected function _load($e) {

        $data = array(
            'first_name' => 'Mary',
            'last_name' => 'Jane'
            'email' => 'maryj@somewhere.domain.com'
        );

        // update a record inside the employees table
        $rows = $this->db->tableUpdate('employees',$data,'id=?',23);

    }

?>

Deleting Records from a Table

To delete a record from a table, use the RaxanPDO::tableDelete() helper method as shown below:

<?php

    protected function _load($e) {

        // delete record from the employees table
        $rows = $this->db->tableDelete('employees','id=?',23);

    }

?>

Calling a Store Procedure

To call a store procedure you can use the RaxanPDO::execProc

<?php

    protected function _load() {
        $param1 = 'First message';
        $param2 = 'Second message';
        // setup an input/output parameter -  array(value, datatype, length)
        $param3 = $out = array(null,PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT,2048);
        $rt = $this->db->execProc('sp_name',$param1, $param2, $param3); // $param1...$paramN

        // display message column value from first row
        $this->div3->text('Rowset: '.$rt->rowset[0]['message']);

        // get next rowset if multiple rowsets where returned by the SP
        $next = $rt->nextRowset;

        // display returned value from SP
        $this->div1->text('The returned value is '.$rt->returnValue);

        // display output value for input/output parameter
        $this->div2->text('Output value is '.$rt->parameters[2]);  // zero base array

    }

?>

Microsoft SQL Server Tip - When working with Stored Procedures, SQL Server requires that you either consume or skip the rowset in order to get the output parameters.

See also RaxanPDO class reference for more information.


Up Next: Global Functions