The Query and the ResultSet

To run a query against a database object, you will use the Database::Query method. This method accepts a database query string, and after running it against the database, returns a ResultSet object. We can then use this ResultSet to inspect the data, and iterate it if multiple rows are returned.

Executing a Query

Executing a query is simple, and works the same for all query types (INSERT, SELECT, UPDATE and DELETE):

<?php
    $sSQL = "SELECT * FROM cars WHERE make = 'Chevrolet'";
 
    $oCars = $oDatabase->Query( $sSQL );
?>

The returned $oCars object is of ResultSet type. Each database column is accessible as a member variable in the instance of the ResultSet. You can access this data like you would any other member variable:

<table>
    <tr>
        <td>Make</td>
        <td>Model</td>
        <td>Year</td>
        <td>Color</td>
    </tr>
    <tr>
        <td><?php echo $oCars->make; ?></td>
        <td><?php echo $oCars->model; ?></td>
        <td><?php echo $oCars->year; ?></td>
        <td><?php echo $oCars->color; ?></td>
    </tr>
</table>

Iterating Across Multiple Rows

The ResultSet implements the PHP Iterator interface, which allows you to use the PHP loop structures (while, foreach, etc) on the returned data from the database. Modifying the example above, we can display all returned records using a foreach() construct:

<table>
    <tr>
        <td>Make</td>
        <td>Model</td>
        <td>Year</td>
        <td>Color</td>
    </tr>
    <?php foreach( $oCars as $oCar ): ?>
    <tr>
        <td><?php echo $oCar->make; ?></td>
        <td><?php echo $oCar->model; ?></td>
        <td><?php echo $oCar->year; ?></td>
        <td><?php echo $oCar->color; ?></td>
    </tr>
    <?php endforeach; ?>
</table>

Inserting and Updating

As mentioned above, inserting and updating are done the same way a select query is executed:

<?php
    $sSQL = "INSERT INTO cars( make, model, year, color ) " .
        "VALUES( 'Chevrolet', 'Equinox', 2008, 'red' )";
 
    if( $oDatabase->Query( $sSQL ) === null )
    {
        echo "Query Failed:" . $oDatabase->GetLastError();
    }
 
    $sSQL = "UPDATE cars SET color = 'black' WHERE car_id = 14";
 
    if( $oDatabase->Query( $sSQL ) === null )
    {
        echo "Query Failed:" . $oDatabase->GetLastError();
    }
?>

Note in the above example we checked to see if the result was null. Null is returned when a query fails. The Database::GetLastError() method returns the last error received by the database server.

That’s the basics of database interaction. There are a few more tidbits worth mentioning. These will be covered in the next sections of the database user guide.

For a much better way to interact with your data, checkout the section on database Models.

The Database