PHP Databases

This content assummes that you have already learned the basics about Databases and SQL. This shows you how to use PHP to access databases from a PHP program. In fact, the ease of doing this is one reason for PHP's popularity.

To show you how to do this, I have created an HTML form (see link below on try it) that uses PHP to get information from a database to poulate it. The PHP script the form invokes is shown below.

See http://us3.php.net/manual/en/intro.oci8.php AND http://us3.php.net/manual/en/ref.oci8.php for api and some additional examples:

 

 

<?php
    $user="login_CHANGE_THIS";
    $password= "password_CHANGE THIS";
    $host = "mcsdb2.sci.csueastbay.edu:1521/MCSDB2"; //CHANGE THIS

    //setup sql query
    $query = "SELECT * FROM DOGS WHERE AGE > " . @$_POST['age'];

    echo "Query is = " .$query;
    echo "<br>";
    echo "Host is = " .$host;
    echo "<br>";
    echo "<br>";

    //connect to database, where tsnames.ora is setup
    $connect_obj = oci_connect($user, $password, $host);
    if($connect_obj)
        { echo "connected okay"; }
    else
        { $err = oci_error();
           echo "Oracle connection error " . $err['message'];
          return;
        }

    //create sql statement
    $sql_statement = oci_parse($connect_obj, $query) ;

    //execute statement
    try{
        $r = oci_execute($sql_statement, OCI_COMMIT_ON_SUCCESS);
        if(!$r)
        { $p = oci_error($sql_statement);
            echo " <br> error in execution " . oci_error($p); }
        }
    catch(Exception $e) {
        echo "<br>Failed to get database info" . $e->getMessage();
    }

    //retrieve results
    echo "<br> results";

    print '<table border="1">';
    while ($row = oci_fetch_array($sql_statement, OCI_RETURN_NULLS+OCI_ASSOC)) {

            echo "row ";
            print_r($row);
            echo "<br>";

            print '<tr>';
            foreach ($row as $key=>$item) {
                print '<td>'.($item?htmlentities($item):'&nbsp;').'</td>';
            }
            print '</tr>';
    }
    print '</table>';

 

    //if you where inserting a new entry in the database table
    //you would need to commit with the following code
    // Commit transaction
    //$committed = oci_commit($connect_obj);

    // Test whether commit was successful. If error occurred, return error message
    //if (!$committed) {
    // $error = oci_error($conn);
    // echo 'Commit failed. Oracle reports: ' . $error['message'];
    //}

    //close the connection
    oci_free_statement($sql_statement); //need to free so close can work
    //without this it will not close
    //until this script ends

    if(!oci_close($connect_obj))
        {echo " oci connection not closed!!!"; }

?>

Try it out now

SPECIAL NOTE: I have the statement in the code above $r = oci_execute($sql_statement, OCI_COMMIT_ON_SUCCESS); Sometimes you will see in older code examples $r = oci_execute($sql_statement, OCI_DEFAULT); SEE the php manual (http://us2.php.net/manual/es/function.oci-execute.php) for a discussion of the oci_execute statement.