METHOD: new


First things first, create an iSQL object and connect to the database. Simple enough.

    $iSQL = new iSQL ( 
       "localhost", "mydatabase", "myuserid", "mypassword"
    );

METHOD: run


This method is used when you run an SQL Command that doesn't return usable data, such as an INSERT or a DELETE ...

 $SQL = "DELETE FROM sometable WHERE idfield = 1";
 $success = $iSQL->run($SQL,1);

       Returns a value on success or null on failure

       Arguments:
         1. SQL Statement
         2. Override Errors
            If you send a "1" for this value, the system will 
            ignore all errors.

METHOD: search

$iSQL->search( array("SQL"=>$SQL_Statement, "Return"=>"set") );

 This function executes an SQL Query and returns the results in
 the format you would like to work with. 

 The arguments, sent in array format, that it can understand are: 

 1. SQL (required)
    The SQL Statement that you wish to run

 2. Start (optional)
    The result number to start at, when sending a result set back

 3. Limit (optional)
    The max number of results to return
 
 4. Return
    The format to return results in.  Valid values are:
    
    "single"
        Returns a single value (string) from a query where you're 
        only asking for one field, from one record.
        "SELECT email FROM customers WHERE id = 15"

    "array"
        Returns a list of single depth values from a query where you
        are asking for multiple records, but only a single field ...
        "SELECT email FROM customers"
        This would return an array like this:

        array( "me@here.com", "you@there.com", "him@somewhere.com" )

    "hash"
        Returns data in a single record format (associative array).  
        Useful when you're pulling a single record from a table, ie:

        "SELECT * FROM accounts WHERE id = 15"
        This would give you data back like this:

            array( 
               "name" => "Fred",
               "email" => "fred@here.com"
               "phone" => "555-1212"
            )


    "fullhash"
        Returns a single associative array representing multiple
        records from the database but in a single record-like format.  
        As an example, to get a simple keyed list of accounts and 
        their id's, run a query like this and ask for it in fullhash 
        format:

        "SELECT id, full_name FROM accounts"

        You'll get back data like this:

            arraY (
               "1" => "John Smith",
               "2" => "Fred Jones",
               "42" => "Mary Jane"
            )

    "set"
        This is an array of hashes (a multi-dimensional associative 
        array).  Use this when you are asking for many records, i.e.
        "SELECT * FROM customers"
        array(
            array( 
               "name" => "Fred",
               "email" => "fred@here.com"
               "phone" => "555-1212"
            ),
            array( 
               "name" => "John",
               "email" => "john@there.com"
               "phone" => "555-1111"
            ),
            array( 
               "name" => "Mary",
               "email" => "mary@overthere.com"
               "phone" => "555-4444"
            ),
        )

METHOD: new Record


Here's where the really easy "OOP" stuff comes into play. Let's work with a single record out of one of your database tables. In this example, we're going to simply get the person from the accounts table whose ID is "7" ....

  $account = new Record ( 
         array (
            'Parent'=>$iSQL, 
            'Table'=>'accounts', 
            'Schema'=>'accounts',
            'Lookup' => 1,
            'rowid' => 7
  )

What can we do with this "record" ?

We can delete it.

  $account->delete();

  Whew.  That didn't hurt a bit.

We can create an html form out of it to put on a web page ...

Using our "schema" system, when you create a table in your database, you then define how to draw a web form from it: The prompt to use for each field, their input types, default values, etc...

   // GENERATE THE FORM ELEMENTS
       $formarray = $account->generate_form();

   // LETS PUT THOSE INTO HTML TABLE ROWS
       $entry_rows = '';
       while ( list ($prompt, $fieldinput) = each ($formarray) ) { 
          $entry_rows .= "
             <tr><td>$prompt</td><td>$fieldinput</td></tr>
          ";
       }

       echo "
           <center>
           <form action='/myprogram.php' method='post'>
             <table>
                <tr>
                  <th>FIELD</th>
                  <th>Value</th>
                </tr>
                $entry_rows
                <tr>
                  <td colspan='2' align='center'>
                    <input type='submit' value='Save Record'>
                  </td>
               </tr> 
             </table>
           </form>
           </center>
             ";

We can take that form submission and save it.

Forget about doing data validation, generatiing properly formatted INSERT statements all that other drudgery. The iSQL Record object takes care of that for you, through the use of Schemas, which allow to not only define how to draw the web form, but also to set rules for saving each field. You do all the "hard work" when you first create the table, which makes your programming tasks so much easier ....

  // Set the values in the table to the values
  // entered in on the web form

  $account->update_values( $_POST );

  // Save it to the DB

  $account->save();

Extending the Record Object


Since the Record is a PHP Object, you can create a subclass of the Record Object yourself, to both override built-in methods (like "delete()") to add additional logic, or add your own methods specific to your needs. In general, the built-in functions should suffice, but when you have a need to subclass, you ceartainly can.

    class Account extends Record {
        
        function delete() { 

           // Get our record ID
           $id = $this->data['id'];

           // Delete any records from other tables that associate
           // with is record
           $SQL = "DELETE FROM someothertable WHERE id = $id";
           $this->iSQL>run($SQL);

           // Delete ourselves
           parent::delete();
        }

    }

CODE INDEPENDENCE


The iSQL Object screens you from having to program specific functions in your programs. For example, to get the number of rows affected by a Query, you would call either mysql_affected_rows, mysqli_stmt_affected_rows, pg_affected_rows, etc ..

Lets assume that you write some killer application with PHP and mySQL and you get notification from your client (or your boss) that they no longer love mySQL and want to port the whole thing over to Oracle. Yikes. Now, you've got to go through the whole application and change every single one of your mysql function calls into their oracle equivalents. Ugh.

The better way? Install the iSQL Oracle driver (one file) in place of your mySQL Driver. Then go take a nap, because you don't have to change ONE LINE of your source code.

Yep ... instead of having to replace a thousand instances of mysql_fetch_array() with oci_fetch_array() ... your existing method calls do not change.

        $iSQL->search( ... )
        $iSQL->run( ... )
        $num_affected = $iSQL->rows_affected(); 

      

Those are the same to you as a programmer no matter what database you use, so long as you have the right iSQL database driver installed.

iSQL provides functional equivalents to all database specific methods, like rows_affected, num_rows_returned, last_insert_id, etc.