Mega Code Archive
A set of functions sitting on top of the abstraction layer that makes it a little
<![CDATA[
easier to do SQL stuff
<?php /* -*- C++ -*- */
/*
* $Id: sql.phl,v 1.5 1998/07/02 09:52:13 ssb Exp $
*/
/*
* ABOUT:
*
* CONFIGURATION:
*
* You must set some global variables prior to including this library:
* $db_type which database abstraction layer to use.
* If your layer is defined in "db-odbc.phl",
* set this variable to "odbc".
* $db_dsn Which database to connect to (dsn=data source name)
* $db_user Which user to connect as
* $db_pass Password used when connecting
*
* Some optional global variables can be set:
* $sql_debug true/false, toggles debug information
* $sql_log_facility how to log debug information
* $sql_log_dest debug information destination
*
* $sql_log_facility and $sql_log_dest are passed as the second
* and third args to PHP's error_log() function, respectively.
* The default is debugging through the PHP TCP debugger to
* port 1400 on localhost. Debug information is also disabled
* by default.
*
* The following global variables will be defined:
* $db_connection The opened connection.
*
*
* USAGE:
*
* All you have to do is require("sql.phl"), either explicitly in the
* pages where you want to use it, or through auto_prepend_file somehow.
* Being a lazy programmer, I always include everything I need with
* auto-prepend from a file I call "config.phl" looking something like
* this:
*
* <?php
* $db_type = "odbc";
* $db_user = "myuser";
* $db_pass = "mypassword";
* $db_dsn = "SVT=Solid; DSN=MyDB"
* require("sql.phl");
* ?>
*
* Once you have included this file, a database connection will
* be opened for you once it is needed, and this connection will
* be used for all queries. Memory is freed automatically for
* all functions except sql().
*
* Example use of all functions:
*
* // returns a result identifier
* $res = sql("SELECT * FROM table");
*
* // returns one column:
* $name = sql_one("SELECT name FROM table WHERE id = $id");
*
* // returns one row in an array
* $row = sql_onerow("SELECT * FROM table WHERE id = $id");
*
* // returns an array of arrays with all the result data
* $data = sql_all("SELECT * FROM table");
*
* // returns an array with data from column 0 only:
* $names = sql_col("SELECT name FROM table", 0);
*
* // returns an associative array with the first column as the
* // key and the second column as the value:
* $name_by_id = sql_assoc("SELECT id, name FROM table");
*
* // enable auto-commit
* sql_autocommit(true);
*
* // disable auto-commit
* sql_autocommit(false);
*
* // commit transaction
* sql_commit();
*
* // roll back transaction
* sql_rollback();
*
*/
require( "db-$db_type.phl");
$db_connection = 0;
/* debugging defaults */
if (empty($sql_debug)) {
$sql_debug = false;
}
if (empty($sql_log_facility)) {
$sql_log_facility = 2; /* debug through TCP */
}
if (empty($sql_log_dest)) {
$sql_log_dest = "127.0.0.1:1400"; /* destination */
}
/*
* Function: sql_debug
* Description: sends debug information somewhere if
* the global variable $sql_debug is true.
*/
function sql_debug($message) {
global $sql_debug, $sql_log_facility, $sql_log_dest;
if ($sql_debug) {
error_log( "[SQL] $message", $sql_log_facility, $sql_log_dest);
}
}
/*
* Function: assert_db_connection
* Description: makes sure we have a database connection
*/
function assert_db_connection()
{
global $db_connection;
if ($db_connection) {
return;
}
global $db_dsn, $db_user, $db_pass;
$db_connection = db_connect($db_dsn, $db_user, $db_pass);
if (!$db_connection) {
die( "Failed to connect to database.");
}
}
/*
* Function: sql
* Arguments: $query (string) - SQL statement
* Description: executes an SQL statement
* Returns: (int) result identifier
* returns 0 upon error
*/
function sql($query) {
global $db_connection, $PHP_SELF;
assert_db_connection();
sql_debug($query);
return db_query($db_connection, $query);
}
/*
* Function: sql_onerow
* Arguments: $query (string) - SQL statement
* Description: executes an SQL statement and returns the first
* row of the result
* Returns: (array) the first row
* returns 0 upon error
*/
function sql_onerow($query) {
$res = sql($query);
$row = db_fetch_row($res);
db_free_result($res);
return $row;
}
/*
* Function: sql_one
* Arguments: $query (string) - SQL statement
* Description: executes an SQL statement and returns the first
* column of the first row of the result
* Returns: (mixed) the first column of the first row
* returns false upon error
*/
function sql_one($query) {
$row = sql_onerow($query);
if (gettype($row) == "array") {
return $row[0];
} else {
return false;
}
}
/*
* Function: sql_all
* Arguments: $query (string) - SQL statement
* Description: executes an SQL statement and returns an array
* of arrays with the rows and columns of all the
* result data from the query.
* Returns: array of arrays or false on error
*/
function sql_all($query) {
$res = sql($query);
if ($res) {
$all = array();
while ($row = db_fetch_row($res)) {
$all[] = $row;
}
db_free_result($res);
return $all;
}
return false;
}
/*
* Function: sql_col
* Arguments: $query (string) - SQL statement
* $column (int) - returned result column number
* Description: executes an SQL statement and returns an array
* with the results from a specific column in the result.
* Returns: array or false on error
*/
function sql_col($query, $column) {
$res = sql($query);
if ($res) {
$all = array();
while ($row = db_fetch_row($res)) {
$all[] = $row[$column];
}
db_free_result($res);
return $all;
}
return false;
}
/*
* Function: sql_assoc
* Arguments: $query (string) - SQL statement
* Description: executes an SQL statement and returns an associative
* array. The indices of this array are taken from the
* first column of the result, while the values are taken
* from the second column. If there are more than two
* columns the remaining ones are ignored.
* Returns: associative array or false on error
*/
function sql_assoc($query) {
$res = sql($query);
if ($res) {
$row = db_fetch_row($res);
if (!is_array($row) || count($row) < 2) {
db_free_result($res);
return false;
}
while ($row) {
$assoc[$row[0]] = $row[1];
$row = db_fetch_row($res);
}
db_free_result($res);
return $assoc;
}
return false;
}
function sql_autocommit($enabled)
{
global $db_connection;
if (!$enabled) {
sql_debug( "transaction starting");
}
return db_autocommit($db_connection, $enabled);
}
function sql_commit()
{
global $db_connection;
sql_debug( "transaction complete");
return db_commit($db_connection);
}
function sql_rollback()
{
global $db_connection;
sql_debug( "transaction aborted");
return db_rollback($db_connection);
}
?>
]]>