objSQL Database Connection Instance

To connect to a database, create an objSQL() instance.

• The database connection information can be entered as a comma delimited string or an array.
• Database types are case-insensitive but must be entered as: cubrid, mysql, pgsql, sqlite3, sqlsrv or pdo:cubrid, pdo:mysql, pdo:pgsql, pdo:sqlite3, pdo:sqlsrv for PDO drivers. Enter mysql or pdo:mysql as the driver for MariaDB.
• objSQL performs an internal validation before it attempts to connect to a database by verifying the database type. A failure triggers an error.

Returns: Connection resource/object or false on failure.

• Call obj_close() to close the current connection instance.

Returns: True on success or false on failure.

<?php

/**

Drivers

CUBRID: cubrid or pdo:cubrid
MySQL/MariaDB: mysql or pdo:mysql
Postgres: pgsql or pdo:pgsql
SQLite3: sqlite3 or pdo:sqlite3
SQL Server: sqlsrv or pdo:sqlsrv


usage: str - $connection = "dbtype,localhost,username,password,database,port";
usage: array - $connection = array( "dbtype","localhost","username","password","database","port" );

*/

try
{
    $dbh = new objSQL( "mysql,localhost,root,pass,mydb,3306" );
        
    if ( error_get_last() !== null )
        throw new Exception( error_get_last()['message'] );
        
    //Query block  
    
    $dbh->obj_close();    
}
catch ( Exception $e ) 
{
    echo $e->getMessage();
}


try
{
    $dbh = new objSQL( "pdo:mysql,localhost,root,pass,mydb,3306" );
        
    if ( error_get_last() !== null )
        throw new Exception( error_get_last()['message'] );
        
    //Query block  
    
    $dbh->obj_close();    
}
catch ( Exception $e ) 
{
    echo $e->getMessage();
}

?>   

objSQL Error Handling

Error handling is specific to each database type and is dependant on the current connection instance.

• Call the obj_error() method to test for an error.

Returns: False if no error or true on the last error.

• Call the obj_error_message() method to display or log an error message.

Returns: Verbose error message on the last error or null if no error.

<?php

try
{
    $dbh = new objSQL( "mysql,localhost,root,pass,mydb,3306" );
            
    if ( error_get_last() !== null )
        throw new Exception( error_get_last()['message'] );
            
    $rs = $dbh->obj_query( "delete from mytable where id=1" );
        
    if ( $dbh->obj_error() )
        throw new Exception( $dbh->obj_error_message() );
           
    echo $rs->obj_affected_rows();    
        
    $dbh->obj_close();  
}
catch ( Exception $e ) 
{
    //log error and/or redirect user to error page
}

?>   

objSQL General Queries

The obj_query() method executes general queries and is called from the objSQL class.

• The $query argument is required.

Returns: Result resource/object or false on failure.

<?php

//usage: $dbh->obj_query( $query )

try
{
    $rs = $dbh->obj_query( "select prod_id from mytable where color='blue' and product='balloon'" );
        
    if ( $dbh->obj_error() )
        throw new Exception( $dbh->obj_error_message() );
        
    echo $rs->obj_num_rows(); 
}
catch ( Exception $e ) 
{
    //log error and/or redirect user to error page
} 

?>

objSQL Prepared Statements

A prepared statement is a pre-compiled SQL query that returns a resource/object. This resource can then be used to execute an SQL statement multiple times and helps reduce some overhead on the server. Prepared statements use parameterized values which are executed after the prepared statement is registered and are a proven deterent against SQL injection as the query is not exposed repeatedly.

Prepared statements allow you to embed markers within the SQL statement that will be replaced with bound parameter data. The obj_prepare_statement() method utilizes a question mark (?) as its parameter binding marker and is called directly from the objSQL class. A prepared statement can be executed as a stand-alone SQL query without any binding parameters.

• Calling the obj_prepare_statement() registers the prepared statement. The $query argument is required.
• The obj_bind() method processes the binding parameter and requires a single value argument.
• The binding markers and methods are executed in the order they appear. You must sync the order of the binding markers (?) with its respective binding method.
• The obj_execute() method executes the prepared statement and take no arguments.

Returns: obj_prepare_statement() - Statement resource/object or false on failure.

Returns: obj_execute() - Result or resultset resource/object or false on failure.

See also: Freeing resources | Multiple prepared statements | SQL Server and prepared statements

<?php

//usage: $dbh->obj_prepare_statement( $query );

try
{
    $stmt = $dbh->obj_prepare_statement( "update mytable set dept=? where location=?" );
    
    $stmt->obj_bind( 'Sales' );
    $stmt->obj_bind( 'Chicago' );  
    
    $rs = $stmt->obj_execute();  
    
    if ( $dbh->obj_error() )
        throw new Exception( $dbh->obj_error_message() );
        
    echo $rs->obj_affected_rows();
}
catch ( Exception $e ) 
{
    //log error and/or redirect user to error page
} 

?>   

Freeing Prepared Statement Resources

A prepared statement can be executed multiple times, but you must free the statement resources between statement executions. Failure to do so will throw an exception.

• The obj_free_statement() method releases the previously executed prepared statement resource.

Returns: True on success or false on failure.

• The obj_close_statement() method closes the prepared statement and releases all resources. Attempting to execute a prepared statement after calling obj_close_statement() will throw an exception.

Returns: True on success or false on failure.

<?php

try
{
    $stmt = $dbh->obj_prepare_statement( "update mytable set dept=? where location=?" );
    
    if ( $dbh->obj_error() )
        throw new Exception( $dbh->obj_error_message() );
    
    $stmt->obj_bind( 'Sales' );
    $stmt->obj_bind( 'Chicago' );  
    
    $rs = $stmt->obj_execute();  
    
    if ( $dbh->obj_error() )
        throw new Exception( $dbh->obj_error_message() );
        
    echo $rs->obj_affected_rows();  
    
    $stmt->obj_free_statement();
    
    $stmt->obj_bind( 'Advertising' );
    $stmt->obj_bind( 'New York' );  
    
    $rs = $stmt->obj_execute();  
    
    if ( $dbh->obj_error() )
        throw new Exception( $dbh->obj_error_message() );
        
    echo $rs->obj_affected_rows();   
    
    $stmt->obj_free_statement();
    
    $stmt->obj_bind( 'Human Resources' );
    $stmt->obj_bind( 'Atlanta' );  
    
    $rs = $stmt->obj_execute();  
    
    if ( $dbh->obj_error() )
        throw new Exception( $dbh->obj_error_message() );
        
    echo $rs->obj_affected_rows();  
    
    $stmt->obj_close_statement();
    
}
catch ( Exception $e ) 
{
    //log error and/or redirect user to error page
} 

?>  

Back to top

Multiple Prepared Statements

A prepared statement can be executed multiple times and you can register as many statements as required.

<?php

try
{
    $stmt1->obj_prepare_statement( "update mytable set dept=? where location=?" );
    
    if ( $dbh->obj_error() )
        throw new Exception( $dbh->obj_error_message() );
    
    $stmt2->obj_prepare_statement( "select id from mytable" );
    
    if ( $dbh->obj_error() )
        throw new Exception( $dbh->obj_error_message() );

    $stmt1->obj_bind( 'Sales' );
    $stmt1->obj_bind( 'Chicago' );  
    
    $stmt1->obj_execute();  
    
    if ( $dbh->obj_error() )
        throw new Exception( $dbh->obj_error_message() );
        
    $stmt1->obj_free_statement();

    $rs = $stmt2->obj_execute();
    
    if ( $dbh->obj_error() )
        throw new Exception( $dbh->obj_error_message() );
    
    echo $rs->obj_num_rows(); 

    $stmt2->obj_free_statement();

    $stmt1->obj_bind( 'Advertising' );
    $stmt1->obj_bind( 'New York' );  

    $stmt1->obj_execute();
    
    if ( $dbh->obj_error() )
        throw new Exception( $dbh->obj_error_message() );

    $rs = $stmt2->obj_execute();
    
    if ( $dbh->obj_error() )
        throw new Exception( $dbh->obj_error_message() );
    
    echo $rs->obj_num_rows();   

    $stmt1->obj_close_statement();
    $stmt2->obj_close_statement();
}
catch ( Exception $e ) 
{
    //log error and/or redirect user to error page
} 

?>    

Back to top

SQL Server and Prepared Statements

Prepared statements on SQL Server do not use a binding method and instead require that you declare your parameter variables in an array with a reference before registering the prepared statement. The SQL Server PDO driver does not require declaring parameter variables.

• The obj_prepare_statement() method takes a second argument where you register your parameter variables. All other database types ignore this argument.
• Declare the parameter values in the obj_bind() method and execute the statement as you normally would.
• If you are planning to support SQL Server, the following example works with all supported database types.

<?php

//SQLSRV driver

try
{
    $params = array( &$var1, &$var2 );
    
    $stmt = $dbh->obj_prepare_statement( "update mytable set dept=? where location=?", $params );
    
    if ( $dbh->obj_error() )
        throw new Exception( $dbh->obj_error_message() );
    
    $stmt->obj_bind( $var1="Sales" );
    $stmt->obj_bind( $var2="Chicago" );  
    
    $rs = $stmt->obj_execute();  
    
    if ( $dbh->obj_error() )
        throw new Exception( $dbh->obj_error_message() );
        
    echo $rs->obj_affected_rows();   
    
    $stmt->obj_close_statement();
}
catch ( Exception $e ) 
{
    //log error and/or redirect user to error page
} 

//SQLSRV PDO driver

try
{
    $stmt = $dbh->obj_prepare_statement( "update mytable set dept=? where location=?" );
    
    $stmt->obj_bind( 'Sales' );
    $stmt->obj_bind( 'Chicago' );  
    
    $rs = $stmt->obj_execute();  
    
    if ( $dbh->obj_error() )
        throw new Exception( $dbh->obj_error_message() );
        
    echo $rs->obj_affected_rows();
}
catch ( Exception $e ) 
{
    //log error and/or redirect user to error page
} 

?>   

Back to top

objSQL Transactions

Transactions are a means to ensure a database's integrity when one or more queries fail. Queries are usually grouped together in logical blocks and are committed or rolled back when certain conditions are met. In general, any statement that alters a database record such as insert, delete and update is considered a transactional statement. Statements such as create database, create table, drop database, etc. should be executed outside of a transaction block.

• Call the obj_transaction() method to initiate a transaction and disable autocommit.

Returns: Transaction instance or false on failure.

• Call the obj_rollback() method to rollback a transaction block.

Returns: True on success or false on failure.

• Call the obj_commit() method to commit a transaction block. Failure to call obj_commit() cancels the entire transaction.

Returns: True on success or false on failure.

See also: Savepoints

<?php

try
{
    $trans = $dbh->obj_transaction();
    
    $data = array( "color" => "blue",
                   "type"  => "leather",
                   "price" => 36.95 );
                   
    $rs = $dbh->obj_update( "products", $data, "prod_id=21" );
    
    if ( $dbh->obj_error() )
    {
        $trans->obj_rollback();
        
        throw new Exception( $dbh->obj_error_message() );
    }
        
    $data = array( "color" => "red",
                   "type"  => "leather",
                   "price" => 32.95 );
                   
    $rs = $dbh->obj_update( "products", $data, "prod_id=49" );
    
    if ( $dbh->obj_error() )
    {
        $trans->obj_rollback();
        
        throw new Exception( $dbh->obj_error_message() );
    }
        
    $trans->obj_commit();    

}
catch ( Exception $e ) 
{
    //log error and/or redirect user to error page
}

?>

Savepoints

Savepoints allow you set a point within a transaction to rollback to if a failed condition is met without affecting any work done in the transaction before the savepoint was declared.

• Call the obj_savepoint() method to set a savepoint. The name argument is required.

Returns: True on success or false on failure.

• The obj_rollback() method takes an optional argument which is the named savepoint. Rolling back a transaction without a savepoint name cancels the entire transaction.

Returns: True on success or false on failure.

<?php

//usage: $trans->obj_savepoint( str $name );
//usage: $trans->obj_rollback( str $name );

try
{
    $trans = $dbh->obj_transaction();
    
    $data = array( "color" => "blue",
                   "type"  => "leather",
                   "price" => 36.95 );
                   
    $rs = $dbh->obj_update( "products", $data, "prod_id=21" );
    
    if ( $dbh->obj_error() )
    {
        $trans->obj_rollback();
        
        throw new Exception( $dbh->obj_error_message() );
    }
    
    $trans->obj_savepoint( "svp1" );
        
    $data = array( "color" => "red",
                   "type"  => "leather",
                   "price" => 32.95 );
                   
    $rs = $dbh->obj_update( "products", $data, "prod_id=49" );
    
    if ( $dbh->obj_error() )
    {
        $trans->obj_rollback( "svp1" );
        $trans->obj_commit();
        
        throw new Exception( $dbh->obj_error_message() );
    }
        
    $trans->obj_commit();    

}
catch ( Exception $e ) 
{
    //log error and/or redirect user to error page
}

?>

Back to top

objSQL Resultset Methods

Associative Resultsets

The obj_fetch_assoc() method returns a resultset as an associative array.

Returns: Array or null if no more rows

The obj_field() method returns the resultset record for the named column and is case-sensitive.

Returns: Mixed

See also: Numeric Resultsets | Object Resultsets | Result/Resultset Statement Methods

<?php

try
{
    $rs = $dbh->obj_select( "mytable", "color,size,price", "product='balloons'", "price" );
    
    if ( $dbh->obj_error() )
        throw new Exception( $dbh->obj_error_message() );
        
    while ( $rs->obj_fetch_assoc() ) 
    {
        printf ( "%s %s %s \n", $rs->obj_field( "color" ), 
                                $rs->obj_field( "size" ), 
                                $rs->obj_field( "price" );
    }
    
    echo $rs->obj_num_rows();
    
    $rs->obj_free_result();
}
catch ( Exception $e ) 
{
    //log error and/or redirect user to error page
} 

?>

Numeric Resultsets

The obj_fetch_num() method returns a resultset as a numeric array.

Returns: Array or null if no more rows

The obj_field() method returns the resultset record for the numeric column.

Returns: Mixed

<?php

try
{
    $rs = $dbh->obj_select( "mytable", "color,size,price", "product='balloons'", "price" );
    
    if ( $dbh->obj_error() )
        throw new Exception( $dbh->obj_error_message() );
        
    while ( $rs->obj_fetch_num() ) 
    {
        printf ( "%s %s %s \n", $rs->obj_field( 0 ), 
                                $rs->obj_field( 1 ), 
                                $rs->obj_field( 2 );
    }
    
    echo $rs->obj_num_rows();
    
    $rs->obj_free_result();
}
catch ( Exception $e ) 
{
    //log error and/or redirect user to error page
} 

?>

Back to top

Object Resultsets

The obj_fetch_object() method returns a resultset as an object. The fields ( column names ) are case-sensitive.

Returns: Object or null if no more rows

<?php

try
{
    $rs = $dbh->obj_select( "mytable", "color,size,price", "product='balloons'", "price" );
    
    if ( $dbh->obj_error() )
        throw new Exception( $dbh->obj_error_message() );
        
    while ( $obj = $rs->obj_fetch_object() ) 
    {
        printf ( "%s %s %s \n", $obj->color, 
                                $obj->size, 
                                $obj->price );
    }
    
    echo $rs->obj_num_rows();
    
    $rs->obj_free_result();
}
catch ( Exception $e ) 
{
    //log error and/or redirect user to error page
} 

?>

Back to top

Result/Resultset Statement Methods

The obj_num_rows() method returns the number of rows from a select statement. The SQLite3 driver does not have a native PHP num rows method and uses a simulated num rows query, but it is very inefficient and can time out even on small recordsets. The SQLite3 PDO driver returns 0 and the SQLSRV PDO driver return -1 from the PDO rowCount() method. The obj_row_count() helper method is recommended for all situations requiring an accurate and efficient num rows count.

Returns: Unsigned integer or -1 on failure

The obj_num_fields() method returns the number of fields from a select statement.

Returns: Unsigned integer or -1 on failure

The obj_affected_rows() method returns the number of affected rows from an insert/update/delete statement.

Returns: Unsigned integer or -1 on failure

The obj_free_result() method frees the statement and resultset resources from a select statement.

Returns: True or False on failure

<?php

try
{
    $rs = $dbh->obj_select( "mytable", "color,size,price", "product='balloons'", "price" );
    
    if ( $dbh->obj_error() )
        throw new Exception( $dbh->obj_error_message() );
        
    echo $rs->obj_num_rows();
    echo $rs->obj_num_fields();
    
    $rs->obj_free_result();
    
    $rs = $dbh->obj_update( "mytable", array( "color" => "blue" ), "id=6" );
    
    if ( $dbh->obj_error() )
        throw new Exception( $dbh->obj_error_message() );
        
    echo $rs->obj_affected_rows();
    
}
catch ( Exception $e ) 
{
    //log error and/or redirect user to error page
} 

?>

Back to top

objSQL Helper Method: Select

The obj_select() helper method executes a select statement with minimal SQL markup and is called from the objSQL class.

• The $table name argument is required. Supplying only this argument will return all rows from the named table.
• The optional $cols argument specifies which columns to return and is entered as a comma delimited string. Entering an empty value is equivilent to an asterisk (*).
• The optional $where argument allows you to filter your query and only requires the column and its value. You can use any of the normal operators used in SQL statements:
  • "location IN ('Athens','London')"
  • "location='Valdosta' AND dept='IT'"
  • "location LIKE 's%'"
  • "salary BETWEEN 20000 AND 40000"
• The optional $order_by argument is used to sort the resultset and only requires the column name.
• The optional $sort_order argument is used to sort the $order_by argument in descending (desc) or ascending (asc) order.

Returns: Result resource/object or false on failure.

<?php

//usage: $dbh->obj_select( $table, $cols, $where, $order_by, $sort_order )

try
{
    //Return all rows from table
    $rs = $dbh->obj_select( "mytable" );
    
    if ( $dbh->obj_error() )
        throw new Exception( $dbh->obj_error_message() );
        
    echo $rs->obj_num_rows();
    
    $rs->obj_free_result();
}
catch ( Exception $e ) 
{
    //log error and/or redirect user to error page
} 


try
{
    //Return selected cols from table with filter
    $rs = $dbh->obj_select( "mytable","color,size,price","product='balloons'","price" );
    
    if ( $dbh->obj_error() )
        throw new Exception( $dbh->obj_error_message() );
        
    while ( $obj = $rs->obj_fetch_object() ) 
    {
        printf ( "%s %s %s \n", $obj->color, 
                                $obj->size, 
                                $obj->price );
    }
    
    $rs->obj_free_result();
}
catch ( Exception $e ) 
{
    //log error and/or redirect user to error page
} 

?>

objSQL Helper Method: Update

The obj_update() helper method executes an update statement with minimal SQL markup and is called from the objSQL class.

• The $table argument is required.
• The $data argument is required and MUST be a key value pair array with the table column as the key.
• The optional $where argument allows you to limit which rows are updated and only requires the column and its value. You can use any of the normal operators used in SQL statements:
  • "location IN ('Athens','London')"
  • "location='Valdosta' AND dept='IT'"
  • "salary BETWEEN 20000 AND 40000"
• obj_update() will update all records in a database table if used without a where clause and steps should be taken in any script or program to utilize a confirmation before executing this method unless the intention is to update the entire table.

Returns: Result resource/object or false on failure.

<?php

//usage: $dbh->obj_update( $table, $data, $where )

try
{
    $data = array( "color" => "blue",
                   "type"  => "leather",
                   "price" => 36.95 );
    
    $rs = $dbh->obj_update( "products", $data, "prod_id=21" );
    
    if ( $dbh->obj_error() )
        throw new Exception( $dbh->obj_error_message() );
        
    echo $rs->obj_affected_rows();
    
}
catch ( Exception $e ) 
{
    //log error and/or redirect user to error page
} 

?>     

objSQL Helper Method: Update

The obj_update() helper method executes an update statement with minimal SQL markup and is called from the objSQL class.

• The $table argument is required.
• The $data argument is required and MUST be a key value pair array with the table column as the key.
• The optional $where argument allows you to limit which rows are updated and only requires the column and its value. You can use any of the normal operators used in SQL statements:
  • "location IN ('Athens','London')"
  • "location='Valdosta' AND dept='IT'"
  • "salary BETWEEN 20000 AND 40000"
• obj_update() will update all records in a database table if used without a where clause and steps should be taken in any script or program to utilize a confirmation before executing this method unless the intention is to update the entire table.

Returns: Result resource/object or false on failure.

<?php

//usage: $dbh->obj_update( $table, $data, $where )

try
{
    $data = array( "color" => "blue",
                   "type"  => "leather",
                   "price" => 36.95 );
    
    $rs = $dbh->obj_update( "products", $data, "prod_id=21" );
    
    if ( $dbh->obj_error() )
        throw new Exception( $dbh->obj_error_message() );
        
    echo $rs->obj_affected_rows();
    
}
catch ( Exception $e ) 
{
    //log error and/or redirect user to error page
} 

?>     

 

objSQL Helper Method: Insert

The obj_insert() helper method executes an insert statement which requires no SQL markup and is called from the objSQL class.

• The table name argument is required.
• The $data argument is required and MUST be a key value pair array with the table column as the key.

Returns: Result resource/object or false on failure.

See also: Inserting multiple rows

<?php

//usage: $dbh->obj_insert( $table, $data )

try
{
    $data = array( "product" => "balloon",
                   "color"   => "red",
                   "size"    => "small",
                   "price"   => 2.50 );
    
    $rs = $dbh->obj_insert( "mytable", $data );
    
    if ( $dbh->obj_error() )
        throw new Exception( $dbh->obj_error_message() );
        
    echo $rs->obj_affected_rows();
    
}
catch ( Exception $e ) 
{
    //log error and/or redirect user to error page
} 

?>  

Inserting Multiple Rows

The obj_insert() helper method additionally allows you to easily insert multiple rows.

• The $data value argument can be a comma delimited string or an array.
• The latest version of PHP uses SQLite3 3.7.7.1 which doesn't support inserting multiple rows in a single statement.

<?php

//usage: $dbh->obj_insert( $table, array( "column" => "val1,val2,val3,val4" )

try
{
    $data = array( "product" => "balloon,balloon,balloon,balloon",
                   "color"   => "red,green,blue,pink",
                   "size"    => "small,small,small,small",
                   "price"   => 2.50,2.50,2.50,2.50 );
    
    $rs = $dbh->obj_insert( "mytable", $data );
    
    if ( $dbh->obj_error() )
        throw new Exception( $dbh->obj_error_message() );
        
    echo $rs->obj_affected_rows();
    
}
catch ( Exception $e ) 
{
    //log error and/or redirect user to error page
} 

//usage: $dbh->obj_insert( $table, array( "column" => array("val1","val2","val3","val4") )

try
{
    $data = array( "product" => array( "balloon","balloon","balloon","balloon" ),
                   "color"   => array( "red","green","blue","pink" ),
                   "size"    => array( "small","small","small","small" ),
                   "price"   => array( 2.50,2.50,2.50,2.50 ) );
    
    $rs = $dbh->obj_insert( "mytable", $data );
    
    if ( $dbh->obj_error() )
        throw new Exception( $dbh->obj_error_message() );
        
    echo $rs->obj_affected_rows();
    
}
catch ( Exception $e ) 
{
    //log error and/or redirect user to error page
} 

?>  

Back to top

objSQL Helper Method: Delete

The obj_delete() helper method executes a delete statement with minimal SQL markup and is called from the objSQL class.

• The $table name is the only required argument.
• The optional $where argument allows you to limit which rows are deleted and only requires the column and its value. You can use any of the normal operators used in SQL statements:
  • "location IN ('Athens','London')"
  • "location='Valdosta' AND dept='IT'"
  • "salary BETWEEN 20000 AND 40000"
• obj_delete() will delete all records from a database table if used without a where clause and steps should be taken in any script or program to utilize a confirmation before executing this method unless the intention is to delete all records.

Returns: Result resource/object or false on failure.

<?php

//usage: $dbh->obj_delete( $table, $where )

try
{
    $rs = $dbh->obj_delete( "mytable", "dept='IT'" );
    
    if ( $dbh->obj_error() )
        throw new Exception( $dbh->obj_error_message() );
        
    echo $rs->obj_affected_rows();
    
}
catch ( Exception $e ) 
{
    //log error and/or redirect user to error page
} 

?>      

objSQL Helper Method: Paging

The obj_paging() helper method allows you to facilitate recordset paging queries with minimal SQL markup and is called from the objSQL class.

Common uses include online catalogs where the user can view multi-page results by specifying a price range, color, material, etc., as well as display the results by ordering by price or availability.

• The $table name argument is required.
• The optional $cols argument specifies which columns to return and is entered as a comma delimited string. Entering an empty value is equivilent to an asterisk (*).
• The optional $where argument allows you to limit which rows are selected and only requires the column and its value. You can use any of the normal operators used in SQL statements:
  • "color IN ('blue','red')"
  • "color='red' AND material='leather'"
  • "brand LIKE 's%'"
  • "price BETWEEN 200 AND 400"
• The optional $order_by argument is used to sort the resultset. SQL Server requires an order by argument and will throw an exception if not supplied.
• The $limit and $offset arguments default to 1 if not supplied. $limit is the number of records to display per page. The $offset argument is normally supplied by the global $_GET variable.

Returns: Array

• element[0]: Result resource/object or false on failure
• element[1]: Unsigned integer - Number of last page

<?php

//usage: $rs = $dbh->obj_paging( $table, $cols, $where, $order_by, $limit, $offset )
//usage: $result = $rs[0]
//usage: $last_page = $rs[1] 

try
{
    $output = '';
    $limit  = ( isset( $_GET["limit"] ) ) ? $_GET["limit"] : 20;
    $offset = ( isset( $_GET["page"] ) && $_GET["page"] > 0 ) ? $_GET["page"] : 1;
        
    $rs = $dbh->obj_paging( "mytable", "id,first_name,last_name", "", "id", $limit, $offset );
    $result = $rs[0];
    $last_page = $rs[1];
    
    if ( $dbh->obj_error() )
        throw new Exception( $dbh->obj_error_message() );
    
    while( $row = $result->obj_fetch_object() ) 
        echo "{$row->id}: {$row->first_name} - {$row->last_name}<br />";

    if ( $offset == 1 ) 
    {
        $output .= "&lt;&lt;&nbsp;&nbsp;&lt;&nbsp;";
    } 
    else 
    {
        $output .= "<a href=test_paging.php?page=1>&lt;&lt;&nbsp;&nbsp;</a>";
        $prev    = $offset - 1;
        $output .= "<a href=test_paging.php?page=$prev>&lt;&nbsp;</a>";
    }

    $output .= " [ Page $offset of $last_page ] ";

    if ( $offset == $last_page ) 
    {
        $output .= "&nbsp;&gt;&nbsp;&nbsp;&gt;&gt;";
    } 
    else 
    {
        $next    = $offset + 1;
        $output .=  "<a href=test_paging.php?page=$next>&nbsp;&gt;</a>";
        $output .=  "<a href=test_paging.php?page=$last_page>&nbsp;&nbsp;&gt;&gt;</a>";
    }

    echo "<p>$output</p>";
    
    $result->obj_free_result();
    
    
    /** Displays:
    
    1: Stephanie - Parker
    2: Estaban - White
    3: Xavier - Nichols
    4: Betty - Jefferies
    5: Stephanie - Clark
    6: Greg - Pipes
    9: Ezra - Yontz
    11: Marcia - Raymer
    13: Andrew - Jordan
    14: Ian - Jenkins
    15: Howard - Dixon
    16: Bonnie - Ward
    17: Louis - Pipes
    18: Orson - Schroeder
    19: Tiffany - Darden
    20: Xavier - Clark
    21: George - Raymer
    22: Cheryl - Yontz
    24: Percy - Cavenaugh
    25: David - Ingram
    
    <<  <  [ Page 1 of 23 ]  >  >>
    
    */
    
}
catch ( Exception $e ) 
{
    //log error and/or redirect user to error page
} 

?> 

objSQL Helper Method: Row Count

The obj_row_count() helper method returns the number of rows from a select count() query and is called from the objSQL class. This helper method is dependant only on the connection instance and can be called anywhere within a query block.

The SQLite3 driver does not have a native PHP num rows method and uses a simulated num rows query, but it is very inefficient and can time out even on small recordsets. The SQLite3 PDO driver returns 0 and the SQLSRV PDO driver returns -1 from the PDO rowCount() method. The obj_row_count() helper method is recommended for all situations requiring an accurate and efficient num rows count.

• The $table name argument is required.
• The optional $cols argument specifies which column(s) to count and is entered as a comma delimited string. Entering an empty value is equivilent to an asterisk (*).
• The optional $where argument allows you to limit which rows are selected and only requires the column and its value. You can use any of the normal operators used in SQL statements:
  • "color IN ('blue','red')"
  • "color='red' AND material='leather'"
  • "brand LIKE 's%'"
  • "price BETWEEN 200 AND 400"

Returns: Unsigned integer or -1 if undetermined or failure

<?php

//usage: $num_rows = $dbh->obj_row_count( $table, $cols, $where )

try
{
    $num_rows = $dbh->obj_row_count( "mytable", "id", "l_name='Jones'" );
        
    if ( $dbh->obj_error() )
        throw new Exception( $dbh->obj_error_message() );
    
    echo "<p>Your query returned $num_rows results</p>";
   
}
catch ( Exception $e ) 
{
    //log error and/or redirect user to error page
} 

?>

objSQL Utility Methods

The obj_escape() method escapes string data per each database type extension and is called from the objSQL class.

Returns: Escaped string

<?php

try
{
    //Escape string data for database insertion or update

    $desc = $dbh->obj_escape( "Lorem 'ipsum' dolor" );

    $rs = $dbh->update( "mytable", array( "prod_desc" => $desc ), "id=56" );

    if ( $dbh->obj_error() )
        throw new Exception( $dbh->obj_error_message() );
        
    echo $rs->obj_affected_rows();
    
}
catch ( Exception $e ) 
{
    //log error and/or redirect user to error page
} 

?>   

The obj_info() method returns information about objSQL, the current database server and PHP version and is called from the objSQL class.

Returns: Array

<?php

print_r( $dbh->obj_info() );

/** Displays:

Array ( [OBJSQL_VERSION] => 3.2.0 
        [DATABASE_NAME] => mydb 
        [DATABASE_TYPE] => sqlite3 
        [DATABASE_VERSION] => 3.7.7.1 
        [DATABASE_CHARSET] => UTF8 
        [PHP_VERSION] => 5.5.1 )

*/

?>