Php Programming Code Examples

A set of functions sitting on top of the abstraction layer that makes it a little easier to do SQL stuff <?php /* -*- C++ -*- */ /* * $Id: sql.phl,v 1.5 1998/07/02 09:52:13 ssb Exp $ */ /* * 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); } ?>