SQL_AL.PHP™ - Documentation
 

Sections:

1.1   What is SQL_AL.PHP™?
1.2   Features/Functions
1.3   History
1.4   Future Plans
1.5   Contacting the Developers
1.6   Credits, Copyrights, and Trademarks

1.1  What is SQL_AL.PHP™?

SQL_AL.PHP™ is an PHP library providing a single, unified interface to multiple types of Relational DataBase Management Systems (RDBMS).  SQL_AL.PHP is a free, open-source API with direct support available for the following databases:

  • Firebird SQL™
  • Borland Interbase® (versions 6.5 and above)
  • Microsoft SQL Server™
  • MySQL™
  • Oracle® (both version 7 & 8 of Oracle Call Interface)
  • PostgreSQL™
  • Sybase®

SQL_AL.PHP provides an abstraction layer for all database functions.  The beauty of this approach allows the developer to concentrate on the development of the application without regard to the specifics of certain implementations of SQL databases.

Back to Top

1.2  Features/Functions

To use SQL_AL.PHP, some global variables need to be set beforehand.  A more automated procedure will take place in the next revision:

  • $GLOBALS['dbtype'] - The specified database type.  Can currently contain the following values for supported databases:
    • 'fbird' -  For Firebird
    • 'ibase' -  For Borland Interbase.
    • 'mssql' -  For Microsoft SQL Server.
    • 'mysql' -  For MySQL.
    • 'oracle' -  For Oracle OCI version 7.
    • 'oci8' -   For Oracle OCI version 8.
    • 'postgres' -  For PostgreSQL.
    • 'sybase' -  For Sybase.
  • $GLOBALS['db_id'] - The database identifier returned by the sql_connect() function.

To begin an SQL connection, simply use the following two lines of PHP code:

include_once('sql_al.php');
$GLOBALS['dbtype'] = 'mysql';  //  Can be any valid, supported DBTYPE string listed above.
$GLOBALS['db_id'] = sql_connect('localhost','root','password','test');

//  Use the values for the configured RDBMS you chose on line 2.

SQL_AL.PHP includes a majority of the features implemented in PHP 4.x for the various database types.  However, to keep a server-agnostic approach to each database system, several functions are not available.  The following list of functions are available for use with all supported databases:

  • sql_connect($host,$user,$password,$db)
    • Used to connect to a database system and set the selected database.
    • Requires four parameters:  a hostname ($host) that could be a hostname or an IP address, a username ($user), the password ($password) for the username specified, and the database name ($db).  With only the exception of Oracle, all four parameters are significant.
    • For Oracle databases, the hostname is configured with the database name in the TNSNAMES.ORA configuration file.
    • For Interbase and Firebird databases, the database name should reflect the full path to the file on that host, i.e. /home/user/databases/wps.gdb (*nix) OR C:/Program Files/Firebird/bin/wps.gdb (Windows).  Note that Windows users should always use the forward slash ('/') and not the backslash ('\') when denoting paths with SQL_AL.PHP.
    • sql_connect() returns a database identifier, an integer that signifies which connection has been opened for use.  Oracle is the exception to this rule, since Oracle does not use connection identifiers.  Instead, Oracle will return a cursor, which is treated by SQL_AL.PHP as a connection identifier behind the scenes.
       
  • sql_free_result($resid)
    • Used to free result resources from memory.
    • Requires one parameter:  a result identifier ($resid) signifying the particular result set returned.
    • The returned result is either TRUE or FALSE.
       
  • sql_logout($db_identifier)
    • Used to close the connection opened to a database system (and its selected database).
    • Requires one parameter:  a database identifier ($db_identifier) signifying the particular connected database.
    • The returned result is either TRUE or FALSE.
       
  • sql_query($query, $db_identifier)
    • Used to submit an SQL query to an connected database, returning an identifier for the result set.
    • Requires two parameters:  the query string ($query) to be executed and a database identifier ($resid) signifying the particular connected database.
    • The returned result is a result identifier ($resid).
    • Both Oracle OCI7 & OCI8 do not directly support the execution of queries in a single step.  A workaround simplifies the query process and returns a statement identifier, which is the equivalent of a result identifier.
    • A global setting, $GLOBALS['usetrans'], determines whether all queries are automatically committed after execution.  By default, Firebird, Interbase, and Oracle auto-commit queries.  The default setting of FALSE will allow these RDBMS to auto-commit.  The other RDBMS do not auto commit.  Changing the value of $GLOBALS['usetrans'] to TRUE will force these RDBMS to wait for a call to FUNCTION sql_commit().
       
  • sql_num_rows($resid,$subquery = '')
    • Used to determine the number of affected rows in a result set.
    • Requires one parameter:  a result identifier ($resid) signifying the particular result set to determine the total row affected.
    • The optional $subquery parameter consists of the portion of the original query after the FROM keyword.  This parameter is required by Oracle OCI7 & OCI8, Interbase and Firebird, which do not natively support a "rows returned" function in PHP.  The $subquery allows sql_num_rows() to select the Count() of the rows in the query, as a work around.
    • The returned result is an integer value ($num_rows).
       
  • sql_fetch_row($resid)
    • Used to return the next available row of data from a result set as a numbered array.
    • Requires one parameter:  a database identifier ($resid) signifying the particular result set from which to retrieve a row.
    • The returned result is an array of the data returned from the various columns requested, with numbers as the array keys starting from zero.
       
  • sql_fetch_array($resid)
    • Used to return the next available row of data from a result set as an associative array.
    • Requires one parameter:  a database identifier ($resid) signifying the particular result set from which to retrieve a row.
    • The returned result is an array of the data returned from the various columns requested, with the column names as the array keys.
    • As of version 1.1, the column names are not forced to always return in lowercase.
       
  • sql_fetch_object($resid)
    • Used to return the next available row of data from a result set as an associative object.
    • Requires one parameter:  a database identifier ($resid) signifying the particular result set from which to retrieve a row.
    • The returned result is an object with the data returned from the various columns requested as the object properties, named by the column headings.
    • As of version 1.1, the column names are not forced to always return in lowercase.
       
  • sql_result($resid,$rowid,$field,$table,$where)
    • Used to return a specific column from a row in a result set.
    • Requires three regular parameters:  a result identifier ($resid) signifying the particular result set from which to retrieve the field, a row identifier ($rowid) identifying the row from which to pull the field, and a column name ($field).
    • Also requires two special parameters, a TABLE clause ($table) identifying which table the data came from in the original query, and a WHERE clause ($where) that contains the conditions from the original query.
    • The special parameters are required for Oracle OCI versions 7 & 8, Interbase and Firebird, which do not support a "result" function in PHP.
    • The returned result is the value of $field in $rowid from $resid.
       
  • sql_tbegin($db_identifier) **NEW FUNCTION**
    • Used to begin a transaction.
    • Returns a transaction identifier ($tid), which represents a results identifier for most applications.  For Oracle, no $tid is returned (just the value TRUE), since all queries are automatically part of a transaction.
    • The $tid directly support Interbase/Firebird, which can use the $tid with the FUNCTIONS sql_commit() and sql_trollback() instead of the connection identifier.
       
  • sql_tcommit($db_identifier) **NEW FUNCTION**
    • Used to commit a transaction.
    • Returns only TRUE or FALSE.
       
  • sql_trollback($db_identifier) **NEW FUNCTION**
    • Used to rollback a transaction.
    • Returns only TRUE or FALSE.
       
  • sql_operator($operator_name,$text1 = '',$text2 = '')
    • Used to return an SQL specific operator for the configured RBDMS.
    • Requires one parameter:  an operator identifier ($operator_name) signifying the desired operator.
    • Takes two optional parameters required when retrieving the "binaryand" function.  Additional uses in the future may exist as well.
    • The returned result depends completely on the desired operator.  Currently, six supported operators or functions are available.
    • The "like" operator, used as a wildcard with the LIKE keyword.
    • The "notequal" operator, used in comparison of two values, entities, or column names.
    • The "binaryand" function, where a Bitwise AND is performed on $text1 and $text2.
    • The "min" function, which returns the minimum value for a set of values.**NEW KEYWORD**
    • The "max" function, which returns the maximum value for a set of values.**NEW KEYWORD**
    • The "sum" function, which returns the total value for a set of values.**NEW KEYWORD**
    • The usage and purpose for these functions may vary from database to database, but the keyword is all that this function will return.
       
  • sql_supported($sql_type)
    • Used to determine whether PHP has support loaded for a particular SQL database type.
    • Requires one parameter:  a string ($sql_type) identifying which SQL database for which to determine the loaded support.
    • The returned result is either TRUE or FALSE.
       
  • sql_limitquery($fieldnames,$tablename,$whereclause,$orderclause,$limitstart,$limittotal)
    • Used to query with appropriate SQL language with the intent of returning a subset of results from the query based on a beginning and ending limit.
    • Requires six parameters: a database identifier ($resid) signifying the particular connected database.
    • The returned result is either TRUE or FALSE.
    • Interbase does not have a "limit query" method available for versions 6.0 or below, so use this function with caution.  See the discussion about potential fixes in the article titled, Retrieving the first n records from a result set from the Borland Developer Network website.  Even this solution does not provide the ability to get the next n records, so IB6.0 and below have been left out of support.

Back to Top

1.3  History

2003-03-11 - Version 1.1

  • Added support for Interbase v6.5 and above and Firebird SQL.
  • Added support for transactions.  Firebird, Interbase, and Oracle auto-commit queries.  To enable true transaction support, a global variable was created, $GLOBALS['usetrans'], to disable auto-commits and force manual commits, rollbacks, etc.
  • Added support for additional SQL operators ("min", "max", "sum").
  • Documentation updated to reflect additions and bug fixes.

2003-02-04 - Version 1.01

  • Documentation completed with all functions and usage.
  • Fixed problem with OCI8 in functions sql_free_result(), sql_logoff();
  • Streamlined the returning of results in most functions (removed a total of 13 lines from length of SQL_AL.PHP).  Now, one statement included for the returning of the result of the function.  Some exceptions exist:
    • sql_operator - Current implementation of the function will not allow a common result.  It appears to be more practical to keep each operator separately returning a result.
    • Additionally, some functions have included statements to return FALSE under certain circumstances.
  • Enhanced the readability of SQL_AL.PHP by placing the functions in order as they appear above.

2003-01-27 - Version 1.0

  • Initial public release.  Support included for MSSQL, MySQL, Oracle, Postgres, and Sybase.

Back to Top

1.4  Future Plans

SQL_AL.PHP has the following goals for the near future:

  • Implement support for other RDBMS, including DB2®, Informix®, and Frontbase™.
  • Include additional operators and functions in sql_operator().
  • Include additional specialized functions similar to FUNCTION sql_limitquery(), where additional query language must be customized to the specific SQL implementation.

Back to Top

1.5  Contacting the Developers

To reach the SQL_AL.PHP development team, visit the SQL_AL.PHP website on SourceForge and click on one of the developers' names to get information or send a message.  You may also email the team at sqlalphp@ytztech.com.

Back to Top

1.6  Credits, Copyrights, and Trademarks

Credits:

  • Our families, for understanding the time we invest in SQL_AL.PHP and loving us anyway.
  • All the Free and Open-Source Software developers, for their dedication and skill without expectation of repayment.
  • The developers of PHP, for creating such a feature-rich language with which to run FreeWPS.
  • The developers of the SQL databases supported, for developing such rich and powerful data storage systems with which to work.

Copyrights:

  • All SQL_AL.PHP components and documentation:
    • Copyright © 2003, YTZ Technical Services, LLC.  All Rights Reserved.

Trademarks:

NOTE:  The following disclaimers of intellectual property apply to the information contained in this series of documentation for SQL_AL.PHP.
  • SQL_AL.PHP™ is a trademark of YTZ Technical Services, LLC.
  • MySQL™ is a trademark of MySQL AB in the United States and other countries.
  • PostgreSQL™ is a trademark of PostgreSQL, Inc. and Regents of the University of California.
  • Microsoft SQL Server™ is a trademark of Microsoft Corporation in the United States and/or other countries.
  • Oracle® is a registered trademark of Oracle Corporation.
  • Sybase® is a registered trademark of Sybase, Inc. or its affiliates.
  • DB2® and Informix® are registered trademarks of IBM Corporation.
  • Interbase® is a registered trademark of Borland Software Corporation in the United States and other countries.
  • Frontbase™ is a trademark of FrontBase Inc. and FrontBase A/S.
  • All other companies, product names, service names, marks, logos, or symbols mentioned herein may be registered trademarks, trademarks, or service marks of their respective owners.

Back to Top



Copyright © 2003, YTZ Technical Services, LLC.  All Rights Reserved.
All other companies, product names, service names, marks,
logos, or symbols mentioned herein may be registered trademarks,
trademarks, or service marks of their respective owners.