PHP Classes

File: include/cbmysql.inc.php

Recommend this page to a friend!
  Classes of christoph becker   cbmysql   include/cbmysql.inc.php   Download  
File: include/cbmysql.inc.php
Role: Class source
Content type: text/plain
Description: the class itself
Class: cbmysql
Create, query, alter and drop MySQL data, tables
Author: By
Last change: complete code refresh to make the class mysql(i) compatible. a few bugs were fixed.
Date: 17 years ago
Size: 48,968 bytes
 

Contents

Class file image Download
<?php /** * MySQL - MySQL PHP Object Class / mysql(i) Version * uses mysqli_* AND mysql_ functions * it has been tested with php 5.0.5 and mysqli (mysql4.1) * * MySQL PHP Class - provides MySQL 3.x-5.0 connectivity to PHP Copyright (C) 2002-2005 Christoph Becker <cbecker@nachtwach.de> This library is free software; you can redistribute it and/or modify it under the terms of the GNU Lesser General Public License as published by the Free Software Foundation; either version 2.1 of the License, or (at your option) any later version. This library is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more details. You should have received a copy of the GNU Lesser General Public License along with this library; if not, write to the Free Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA * **/ /* o changes to 4.0.0 2005-10-07 preload test, if mysql_ or mysqli_ is avaible... if avaible, mysqli_ is the better choice o changes to 3.5.0 2005-10-06 added: set_DBG / get_DBG 2005-09-25 all functions were transoformed to mysqli_* precedural model and it is not recommented to use this class in new projects o changes to 3.4.0 2005-09-08 bug fixed: if update has no affected rows, return true + get_affected_rows() - returns affected rows as int ? interfaces fpr sql-logging implemented ?! 2005-08-12 code cleared: unreachable code removed (breaks after return) 2005-06-08 bug fixed: update returns false on error bug fixed: change_db o changes to 3.3.0 2005-05-30 time measuring algorith implemented - right way of catching update errors - set_select_type returns selected "SELECT" type o changes to 3.2.5 2005-03-22 ::close now exists, could close mysql connection, set also $TRY_persistent = false ! o changes to 3.2.4 2004-12-03 ::query not you could give a string OR an array as argument to it o changes to 3.2.3 2004-11-06 debug: init $data in ::query o changes to 3.2.2 2004-11-01 debug: notices because select2csv: filename could be set in $this->csv_filename, $this->csv_extension o changes to 3.2.1 2004-09-29 ::delete returns affected rows or false o changes to 3.2.0 2004-09-27 bugfix, ::query() - first line in array had no name, only empty string now it' zero 2004-09-26 ::select() with true as 2n arg, returnes smarty compatible arrays */ /** * Class Loader */ check_mysql_interface(); /** * PreTest * if mysqli_ is avaible, choose that, otherwise mysql_ * */ function check_mysql_interface($preference="LOAD_ MYSQLI"){ $mysqli = function_exists("mysqli_connect"); $mysql = function_exists("mysql_connect"); if($mysqli && $mysql) { define($preference, true); } elseif($mysqli) { define('LOAD_MYSQLI', true); } elseif($mysql) { define('LOAD_MYSQL', true); } else { die("MySQL-Class Load Fatal Error. Unable to find mysql or mysqli Extension. Stop!"); } } /** * MySQL * * @author cbecker@nachtwach.de * @link http://www.ch-becker.de/?php more PHP Resources * @copyright Copyright (c) 2002-2005 * @version 4.0.0 * @access public */ if (!defined('CLASS_MYSQLI') && defined('LOAD_MYSQLI')) { class MySQL { VAR $VERSION = "4.0.0"; VAR $SELECT_TYPE = MYSQLI_BOTH; VAR $affected_rows = 0; /** * MySQL::measure_time - should the script measure sql times? * * int * * @since 2005-05-30 * @access public */ VAR $measure_time = 1; // 0 - none; 1 - milli secs // protected VAR $temptime = 0; //temp timememory /** * MySQL::csv_filename - name without extension of future csv export file * * String * * @since 2004-11-01 * @access public */ VAR $csv_filename = "data"; /** * MySQL::csv_extension - extension of future csv export file * * String * * @since 2004-11-01 * @access public */ VAR $csv_extension = "csv"; /** * MySQL::save2file - should i try to save exported csv file? * * int * * @since 2003-11-25 * @access public */ VAR $save2file = 0; /** * MySQL::prompt4file - prompt with "save file" dialog when exporting csv file (1-yes/0-no) * * (0) - select2csv only returns csv-string-data * (1) - select2csv also return csv-string-data but add. header-info to get save file dialog in your browser * * @see testsuite_csv.php int * @since 2003-11-23 * @access public */ VAR $prompt4file = 1; /** * MySQL::error_no - last Error No * * int * * @access public */ VAR $error_no; /** * MySQL::error_msg - last Error Msg * * string * * @access public */ VAR $error_msg; /** * MySQL::error_cmsg - last Custom Error Msg * * string * * @access public */ VAR $error_cmsg; /** * MySQL::fatalerros - list of errornumbers, we should stop, regardless of which * errorlevel is defined * * string * * @access public */ VAR $fatalerrors = array (1045, // Access denied for user ); /** * MySQL::akt_DBASE - actuall database * * @access public */ VAR $akt_DBASE; /** * MySQL::commands array counts all method-calls in an associative array * * @access private */ // VAR $commands = array(); VAR $commands = array ("alter" => 0, "select" => 0, "insert" => 0, "delete" => 0, "update" => 0, "query" => 0, "sum" => 0, "alter_time" => 0, "select_time" => 0, "insert_time" => 0, "delete_time" => 0, "update_time" => 0, "query_time" => 0, "sum_time" => 0); /** * MySQL::$CONN connection handle * * @access private */ VAR $CONN; /** * MySQL::DBG debuglevel, set by config file * * @access private */ VAR $DBG = 1; // if you have some startup errors, it's fine to see them. // 0-liefert "nur" false zur�ck; Fehlerbehandlung in der aufrufenden Funktion // 1-zeigt Fehler per echo an, liefert false, l��t das Script jedoch weiterlaufen // 2-"stirbt" mir Fehlermeldung ... die("Fehler) // 3-bricht ab ohne Fehleraus- oder R�ckgabe (stiller Tod) /** * MySQL::last_DBASE database used bevore actual * * @access private */ VAR $last_DBASE; /** * MySQL::field_dimension dimension of the returning array from select * * @access private */ VAR $field_dimension = 2; // 2 - always two dimensions // 1 - automatic one/two-dim /** * MySQL::TRY_persistent try to connect with persistent connection * * @access private */ VAR $TRY_persistent = true; // Soll versucht werden persistente Verbindungen aufzubauen? // Bei Transaktionen oder Locking nicht sinnvoll! VAR $STATUS_persistent = false; // Status, ob eine persistente Verbindung aufgebaut wurde oder oder nicht /** * MySql::error() - Error Handler * * @access protected * @param string $text Custom Error Text * @return Boolean false (always) */ function error($text = "-") { $this->error_no = mysqli_errno($this->CONN); $this->error_msg = mysqli_error($this->CONN); $this->error_cmsg = $text; $msg = "<pre style=\"color:red;background-color:white;font-size:12px;\"><b>Custom Error Message: $text "; if ($this->error_no > 0) $msg .= "\nMySQL ErrorNo: ".$this->error_no."\r\nMySQL ErrorMsg: ".$this->error_msg."\r\nMySQL State: ".mysqli_sqlstate($this->CONN); $msg .= "\r\n </b></pre>\r\n "; // override MAIN debug config // halt on connection errors if (in_array($this->error_no, $this->fatalerrors)) { $this->DBG = 2; $msg = "<div style=\"font-size:20px;color:red;backgorund-color:white;\"><strong>FATAL!</strong><br> ".$msg."</div>"; } switch ($this->DBG) { case 0 : // errorlog? //f_write("tmp/mysql/mysql_error_".$_SERVER["REMOTE_ADDR"]."_".microtime().".txt",$text."\r\n".$this->error_no."\r\n".$this->error_msg); return false; // do not need break, because of return case 1 : echo $msg; return false; default : case 2 : debug_print_backtrace2(); die($msg); return false; case 3 : die(); } // switch return false; // no path will reach this } /** * MySQL::MySQL() * * Contructor * * @param string $SERVER servername (e.g. localhost) OR array containing ['user|pass|dbase|server'] * @param string $USER username * @param string $PASS password * @param string $DBASE database * @return bool */ // real constructor function MySQL($SERVER, $USER = "", $PASS = "", $DBASE = "", $DBG = 1) { // check if configarray is given or not if (is_array($SERVER)) { if (!array_key_exists("server", $SERVER)) { $this->error("given config array does not contain \"server\"!"); return false; } if (!array_key_exists("user", $SERVER)) { $this->error("given config array does not contain \"user\"!"); return false; } if (!array_key_exists("pass", $SERVER)) { $this->error("given config array does not contain \"pass\"!"); return false; } if (!array_key_exists("dbase", $SERVER)) { $this->error("given config array does not contain \"dbase\"!"); return false; } if (array_key_exists("dbg", $SERVER)) { $DBG = $SERVER['dbg']; } $USER = $SERVER['user']; $PASS = $SERVER['pass']; $DBASE = $SERVER['dbase']; $SERVER = $SERVER['server']; if ($DBG < 0 || $DBG > 3) { $DBG = 1; } } // set debug mode for class $this->DBG = $DBG; // set mysql_fetch_array to associative arrays, look at php manual "mysql_fetch_array()" $this->set_select_type(MYSQLI_BOTH); // lookup for persistent connections $conn = mysqli_connect($SERVER, $USER, $PASS); if (!$conn) { echo mysqli_connect_error(); //return $this->error( "no connection!" ); } if (!mysqli_select_db($conn, $DBASE)) { $this->CONN = $conn; //return $this->error( "no database!" ); } $this->akt_DBASE = $DBASE; $this->CONN = $conn; return true; } /** * MySQL::set_DBG() - sets Debug level * * <br> if you have some startup errors, it's fine to see them. * <br>0-liefert "nur" false zurck; Fehlerbehandlung in der aufrufenden Funktion * <br>1-zeigt Fehler per echo an, liefert false, lt das Script jedoch weiterlaufen * <br>2-"stirbt" mir Fehlermeldung ... die("Fehler) * <br>3-bricht ab ohne Fehleraus- oder Rckgabe (stiller Tod)<br> * * @param int $newdbg - 0->4 * @return boolean * @access public * @since 2005-10-06 */ function set_DBG($newdbg) { if ($newdbg >= 0 and $newdbg <= 4) { $this->DBG = $newdbg; return true; } else { return false; } } /** * MySQL::get_DBG() - sets Debug level * * <br> if you have some startup errors, it's fine to see them. * <br>0-liefert "nur" false zurck; Fehlerbehandlung in der aufrufenden Funktion * <br>1-zeigt Fehler per echo an, liefert false, lt das Script jedoch weiterlaufen * <br>2-"stirbt" mir Fehlermeldung ... die("Fehler) * <br>3-bricht ab ohne Fehleraus- oder Rckgabe (stiller Tod)<br> * * * @return int * @access public * @since 2005-10-06 */ function get_DBG() { return $this->DBG; } /** * MySQL::checkup() - checks sql-query is not empty, connection is still alive and query is right * * @param string $method method (insert, select etc) * @param string $sql sql query * @return boolean * @access protected * @since 2003-11-25 */ function checkup($method = "", & $sql) { $sql = trim($sql); $method = trim($method); if (empty ($sql)) { $temp = $method == "" ? "Name" : "Query"; return $this->error($temp." is empty: $sql "); } if ($method != "" && !eregi("^".$method, $sql)) { return $this->error("Error, its not a ".$method."-query: $sql "); } if (empty ($this->CONN)) { return $this->error("No connection or connection lost"); } if ($method != "" && !eregi("^CREATE DATABASE", $sql) && $this->akt_DBASE == "") { return $this->error("No database! Create database first, then change actiove DB!"); } return true; } /** * MySql::new_db() * * creates new database * * @param string $name Datenbankname * @return bool */ function new_db($name) { $sql = "CREATE DATABASE IF NOT EXISTS ".$name; if ($this->checkup("", $sql) != true) { return $this->error("an error occured!"); } # if ( mysqli_query ($this->CONN, $sql ) ) { if ($this->one_query($sql)) { return true; } else { return $this->error("error while creating database '$name'!"); } } /** * MySql::change_db() * * changes active database * * @param string $name database * @return bool */ function change_db($name) { $name = trim($name); if (empty ($name)) { return $this->error("DB-Name is empty: $name "); } if (empty ($this->CONN)) { return $this->error("No connection or connection lost"); } if (mysqli_select_db($this->CONN, $name)) { $this->last_DBASE = $this->akt_DBASE; $this->akt_DBASE = $name; return true; } else { return $this->error("error changing database!"); } } /** * MySql::drop_db() * * drops a db * * @param string $name database * @return bool */ function drop_db($name) { if ($this->checkup("", $name) != true) { return $this->error("no database name given"); } #if ( $this->one_query ( "DROP DATABASE IF EXISTS " . $name ) ){ // seems to be buggy if (!mysqli_query($this->CONN, "DROP DATABASE IF EXISTS ".$name)) { return $this->error("could not drop database!"); } // if db dropped, change to last database, if exists if ($name == $this->akt_DBASE && $this->last_DBASE != "") { $this->change_db($this->last_DBASE); } return true; } /** * MySql::create() * * sends every query to db whitsch begins with "create" - it is not case sensitive * * @param string $sql SQL-create-Query * @return bool */ function create($sql) { $result = $this->checkup("create", $sql); if ($result == false) { return $this->error("creation fails."); } if (!$this->one_query($sql)) { $this->error("could not create anything [ ".$sql." ]"); return false; } return true; } /** * MySql::empty_table() * * truncates a table * * @param string $name tablename * @return bool */ function empty_table($name) { $result = $this->checkup("", $name); if ($result == false) { return $this->error("could not truncate."); } #if ( mysql_query ( "TRUNCATE TABLE " . $name, $this->CONN ) ) { if ($this->one_query("TRUNCATE TABLE ".$name)) { return true; } else { $this->error("error truncating table '$name' !"); return false; } } /** * MySql::optimize() * * optimizes table * * @param string $name tablename * @return bool */ function optimize($name) { $result = $this->checkup("", $name); if ($result == false) { return $this->error("could not optimize."); } if ($this->one_query("OPTIMIZE TABLE ".$name)) { return true; } else { $this->error("error optimizing table '$name' !"); return false; } } /** * MySql::drop_table() * * drops a table * * @param string $name table * @return bool */ function drop_table($name) { $name = trim($name); if (empty ($name)) { return false; } if (empty ($this->CONN)) { return false; } #if ( mysql_query ( "DROP TABLE IF EXISTS " . $name, $this->CONN ) ) { if ($this->one_query("DROP TABLE IF EXISTS ".$name)) { return true; } else { $this->error("error dropping table!"); return false; } } /** * MySQL::set_select_type() * * sets the resulttype of mysql_fetch_array used in sql->select() * * @param $type one out of "MYSQL_ASSOC", "MYSQL_NUM", "MYSQL_BOTH" * @return */ function set_select_type($type) { $allowed = array (MYSQLI_NUM, MYSQLI_BOTH, MYSQLI_ASSOC); if (in_array($type, $allowed)) { $this->SELECT_TYPE = $type; } return $this->SELECT_TYPE; } // select field return // 2 - always two dimensions // 1 - automatic one/two-dim function set_field_dimension($type = "0") { $allowed = array (1, 2); if (in_array($type, $allowed)) { $this->field_dimension = $type; } return $this->field_dimension; } function get_field_dimension() { return $this->field_dimension; } /** * MySQL::select() * * queries db with select and returns an array with one or two dimension, dependion on entities * if a entity is named INDEX_ASSOCIATION you'll have an associative array with this entity als key * SELECT id AS INDEX ASSOCIATION, name, land FROM xy ... * the array will be $result[id-entry][name|land] = value... * id-entry is in ( id1, id2 ...) * <br> * 07-26-2004 3.1.0<br> * + via MySQL->fiel_dimension, you could say, you want optional 1-d arrays or not * <br> * 01-30-2004 3.0.0pre3 <br> * + choose via constant SELECT_TYPE if you want to have NUM, ASSOC or BOTH Types in ARRAY * look at set_select_type() * <br> * 09-28-2004 3.1.0 <br> * + 2nd argument = True says, that output shout be smarty array * * @param string $sql * @param int $csvdata * @return array */ function select($sql = "", $smarty = false) { #echo "<br>".$sql."<br>"; $result = 0; $this->time_start(); $r = $this->checkup("select", $sql); if (!$r) { $this->error("error queriing, its not a select-query: ".$sql); return false; } $this->affected_rows = 0; // if there is an SHOW query, there is MYSQL_BOTH needed as mysql_fetch_array result type if (eregi("^show", $sql)) { $temp_type = $this->SELECT_TYPE; $this->SELECT_TYPE = MYSQLI_BOTH; } $conn = $this->CONN; $results = mysqli_query($conn, $sql) or $this->error($sql); if ((!$results) or (empty ($results))) { //mysqli_free_result($conn); // nothing to free return false; } $count = 0; $data = array (); $this->affected_rows = mysqli_affected_rows($conn); // create a smarty readable array if ($smarty == true) { while ($row = mysqli_fetch_array($results, $this->SELECT_TYPE)) { // if there's only one column and the script is allow to create, // create a 1-d array array_push($data, $row); } // normal int associated array } elseif (!ereg("INDEX_ASSOCIATION", $sql)) { // normal results return (1-d, 2-d arrays) $anz_rows = mysqli_num_fields($results); while ($row = mysqli_fetch_array($results, $this->SELECT_TYPE)) { // if there's only one column and the script is allowed to create, // create a 1-d array if ($anz_rows < 2 && $this->field_dimension == 1) { $data[$count] = $row[0]; } else { $data[$count] = $row; } $count ++; } } else { // build index-assozitive 1-d/2-d array // entity named INDEX_ASSOCIATION would be the array-key while ($row = mysqli_fetch_array($results)) { $count = $row['INDEX_ASSOCIATION']; // if theres only one entity left, build a 1-d array if (count($row) > 4) { // remove ASSOCIATIONs, not needed $data[$count] = $row; } else { if ($row[1] == $row['INDEX_ASSOCIATION']) { $data[$count] = $row[0]; } else { $data[$count] = $row[1]; } } } } if (eregi("^show", $sql)) { $this->SELECT_TYPE = $temp_type; } mysqli_free_result($results); $this->commands["select"]++; $this->commands["sum"]++; $this->time_stop("select"); return $data; } /** * MySQL::get_affected_rows() * * returns number of rows that were affected by last query * * * @return int affected rows */ // FIXME: seams to have a bug; does not return right value function get_affected_rows() { return $this->affected_rows; } /** * MySQL::insert() * * inserts a "insert"-query * * @param string $sql * @return int inserted id | affected rows */ function insert($sql = "") { $this->time_start(); if (empty ($sql)) { return false; } if (!eregi("^insert", $sql)) { return $this->error("error, its not a insert-query: ".$sql); } if (empty ($this->CONN)) { return false; } $conn = $this->CONN; $results = mysqli_query($conn, $sql) or $this->error($sql); $results = mysqli_insert_id($conn); $results == 0 ? $results = mysqli_affected_rows($conn) : 1; $this->time_stop("insert"); return $results; } /** * MySQL::update() * * updates db * * @param string $sql * @return boolean true if result | false if no result */ function update($sql = "") { $this->time_start(); if (empty ($sql)) { return false; } if (!eregi("^update", $sql)) { $this->error("error, its not a update-query: ".$sql); return false; } if (empty ($this->CONN)) { return false; } $conn = $this->CONN; // cdhack for errors $results = $this->one_query($sql); /*mysql_query($sql, $conn); //old: mysql_query( $sql, $conn ); if ($results == false) { $this->error("could not exec update(): ".$sql); return false; }*/ $this->time_stop("update"); return $results; } /** * MySQL::delete() * * send delete query * * @param string $sql * @return int affected rows (since 3.2.1) */ function delete($sql = "") { $this->time_start(); if ($this->checkup("delete", $sql) != true) { return $this->error("no delete query: ".$sql); } $conn = $this->CONN; $results = $this->one_query($sql); $results = $this->affected_rows;// mysql_affected_rows($conn); $this->time_stop("delete"); return $results; } /** * MySQL::alter() * * send alter query * * @param string $sql * @return */ //TODO: remove function alter($sql = "") { $this->time_start(); if (empty ($sql)) { return false; } if (!eregi("^alter", $sql)) { $this->error("error, it's not a alter-query: ".$sql); return false; } if (empty ($this->CONN)) { return false; } $conn = $this->CONN; $results = mysqli_query($conn, $sql); if (!$results) { return false; } $this->time_stop("alter"); return true; } /** * MySQL::select2csv() * * sends an array as csv-file / with save/open dialog * <br>(since 2004-11-01) In $this->csv_filename, $this->csv_extension the filename and * extension could be set. * * @since 2003-11-23 * @see array2csv * @see data2file * @param string $sql select query * @param int $append2file if 2, append to existing file with seperator * @return csv file / csv data */ function select2csv($sql = "", $append2file = "0") { $add_file = "cbadditionals.inc.php"; require_once $add_file; // manualy load the extensions $result = $this->select($sql); $data = array2csv($result); if ($this->save2file != 0) { /* some temp calcs */ $t = explode("FROM ", $sql); $t = explode(" ", $t[1]); $table = $t[0]; $result = data2file($data, $this->csv_filename, $this->csv_extension, 2, $table); // write; if ($result == 0) { return $this->error("Could not write csv-data file to disc"); } } switch ($this->prompt4file) { default : case 1 : // prompt for save/open data2file($data); return $data; case 0 : // do not prompt, return csv-string return $data; } // switch } /** * MySQL::query() * * @since 2004-07-26 * @param string $ / array - $sql query * @return false /true // resultarray */ function query($sql = "") { // is array? more than one query if (is_array($sql)) { // more than 1 Query while (list (, $val) = each($sql)) { $this->one_query($val); } // while return true; } return $this->one_query($sql); } //# end query() /** * MySQL::one_query() * * sends any sql query to database and, if possible, returns the result * it's dangerous to use this with user edited data (e.g. login forms) * * @access private * @param string $sql * @return */ function one_query($sql = "") { $this->time_start(); $sql = trim($sql); if ($this->checkup("", $sql) != true) { $this->error("Queryfehler..."); } $data = array (); $count = 0; $conn = $this->CONN; $result = mysqli_query($conn, $sql); $rows = mysqli_affected_rows($conn); $this->affected_rows = $rows; if (!is_bool($result)) { while ($row = mysqli_fetch_array($result, $this->SELECT_TYPE)) { $data[$count ++] = $row; } //# while() } else { $data = $result; } $this->time_stop("query"); if ($data == false) { $this->error($sql); } return $data; } // starts time m, function time_start() { if ($this->measure_time = 0) { return true; } $tmp = explode(" ", microtime()); $this->temptime = $tmp[0] + $tmp[1]; unset ($tmp); // Zeitmessung Start } // stops time measuring function time_stop($func) { //how often was the function called $this->commands[$func]++; $this->commands["sum"]++; // stop here if ($this->measure_time = 0) { return true; } $tmp = explode(" ", microtime()); $microtime = $tmp[0] + $tmp[1] - $this->temptime; unset ($tmp); // Zeitmessung Ende $this->commands[$func."_time"] += $microtime; $this->commands["sum_time"] += $microtime; } // destructor /* * @access public * @return bool */ function close() { return mysqli_close($this->CONN); } } //# end class if (!function_exists("debug_print_backtrace2")) { // PHP4 workaround... not needed 4 PHP5 function debug_print_backtrace2() { echo "<pre>"; $ar = debug_backtrace(); //array_pop($ar); var_dump($ar); echo "</pre>"; } } define("CLASS_MYSQLI", true); } // define /* ************************************************************************* */ if (!defined('CLASS_MYSQL') && defined('LOAD_MYSQL')) { // PHP 4 - MySQL 4 Version of Class... //FIXME: bad style class MySQL { VAR $VERSION = "3.4.0"; VAR $SELECT_TYPE = MYSQL_BOTH; /** * MySQL::measure_time - should the script measure sql times? * * int * * @since 2005-05-30 * @access public */ VAR $measure_time = 1; // 0 - none; 1 - milli secs // protected VAR $temptime =0;//temp timememory /** * MySQL::csv_filename - name without extension of future csv export file * * String * * @since 2004-11-01 * @access public */ VAR $csv_filename = "data"; /** * MySQL::csv_extension - extension of future csv export file * * String * * @since 2004-11-01 * @access public */ VAR $csv_extension = "csv"; /** * MySQL::save2file - should i try to save exported csv file? * * int * * @since 2003-11-25 * @access public */ VAR $save2file = 0; /** * MySQL::prompt4file - prompt with "save file" dialog when exporting csv file (1-yes/0-no) * * (0) - select2csv only returns csv-string-data * (1) - select2csv also return csv-string-data but add. header-info to get save file dialog in your browser * * @see testsuite_csv.php int * @since 2003-11-23 * @access public */ VAR $prompt4file = 1; /** * MySQL::error_no - last Error No * * int * * @access public */ VAR $error_no; /** * MySQL::error_msg - last Error Msg * * string * * @access public */ VAR $error_msg; /** * MySQL::error_cmsg - last Custom Error Msg * * string * * @access public */ VAR $error_cmsg; /** * MySQL::fatalerros - list of errornumbers, we should stop, regardless of which * errorlevel is defined * * string * * @access public */ VAR $fatalerrors = array(104//5, // Access denied for user ); /** * MySQL::akt_DBASE - actuall database * * @access public */ VAR $akt_DBASE; /** * MySQL::commands array counts all method-calls in an associative array * * @access private */ // VAR $commands = array(); VAR $commands = array( "alter"=>0, "select"=>0, "insert"=>0, "delete"=>0, "update"=>0, "query"=>0, "sum"=>0, "alter_time"=>0, "select_time"=>0, "insert_time"=>0, "delete_time"=>0, "update_time"=>0, "query_time"=>0, "sum_time"=>0 ); /** * MySQL::$CONN connection handle * * @access private */ VAR $CONN; /** * MySQL::DBG debuglevel, set by config file * * @access private */ VAR $DBG = 1; // if you have some startup errors, it's fine to see them. // 0-liefert "nur" false zur�ck; Fehlerbehandlung in der aufrufenden Funktion // 1-zeigt Fehler per echo an, liefert false, l��t das Script jedoch weiterlaufen // 2-"stirbt" mir Fehlermeldung ... die("Fehler) // 3-bricht ab ohne Fehleraus- oder R�ckgabe (stiller Tod) /** * MySQL::last_DBASE database used bevore actual * * @access private */ VAR $last_DBASE; /** * MySQL::field_dimension dimension of the returning array from select * * @access private */ VAR $field_dimension = 2; // 2 - always two dimensions // 1 - automatic one/two-dim /** * MySQL::TRY_persistent try to connect with persistent connection * * @access private */ VAR $TRY_persistent = true; // Soll versucht werden persistente Verbindungen aufzubauen? // Bei Transaktionen oder Locking nicht sinnvoll! VAR $STATUS_persistent = false; // Status, ob eine persistente Verbindung aufgebaut wurde oder oder nicht /** * MySql::error() - Error Handler * * @access protected * @param string $text Custom Error Text * @return Boolean false (always) */ function error( $text = "-" ) { $this->error_no = mysql_errno(); $this->error_msg = mysql_error(); $this->error_cmsg = $text; $msg = "<pre style=\"color:red;background-color:white;font-size:12px;\"><b>Custom Error Message: $text "; if ($this->error_no>0) $msg .= "\nMySQL ErrorNo: " . $this->error_no . "\r\nMySQL ErrorMsg: " . $this->error_msg; $msg .= "\r\n </b></pre>\r\n "; // override MAIN debug config // halt on connection errors if (in_array($this->error_no, $this->fatalerrors )) { $this->DBG = 2; $msg = "<div style=\"font-size:20px;color:red;backgorund-color:white;\"><strong>FATAL!</strong><br> " . $msg . "</div>"; } switch ( $this->DBG ) { case 0: // errorlog? //f_write("tmp/mysql/mysql_error_".$_SERVER["REMOTE_ADDR"]."_".microtime().".txt",$text."\r\n".$this->error_no."\r\n".$this->error_msg); return false; // do not need break, because of return case 1: echo $msg; return false; default: case 2: debug_print_backtrace2(); die ( $msg ); return false; case 3: die(); } // switch return false; // no path will reach this } /** * MySQL::MySQL() / MySQL::init() * * Contructor * * @param string $SERVER servername (e.g. localhost) OR array containing ['user|pass|dbase|server'] * @param string $USER username * @param string $PASS password * @param string $DBASE database * @param string $CONFIGFILE configfile.. since 3.1.0 no longer needed * @return bool */ // real?! constructor function init ( $SERVER , $USER = "", $PASS = "", $DBASE = "" , $DBG = 1) { return $this->MySQL ( $SERVER , $USER , $PASS , $DBASE , $DBG ); } // old constructor function MySQL( $SERVER , $USER = "", $PASS = "", $DBASE = "", $DBG = 1 ) { // check if configarray is given or not if (is_array($SERVER)) { if (!array_key_exists("server", $SERVER)) { $this->error( "given config array does not contain \"server\"!"); return false; } if (!array_key_exists("user", $SERVER)) { $this->error( "given config array does not contain \"user\"!"); return false; } if (!array_key_exists("pass", $SERVER)) { $this->error( "given config array does not contain \"pass\"!"); return false; } if (!array_key_exists("dbase", $SERVER)) { $this->error( "given config array does not contain \"dbase\"!"); return false; } if (array_key_exists("dbg", $SERVER)) { $DBG = $SERVER['dbg']; } $USER = $SERVER['user']; $PASS = $SERVER['pass']; $DBASE= $SERVER['dbase']; $SERVER=$SERVER['server']; if ($DBG<0 || $DBG>3) { $DBG = 1; } } // set debug mode for class $this->DBG = $DBG; // set mysql_fetch_array to associative arrays, look at php manual "mysql_fetch_array()" $this->set_select_type( MYSQL_BOTH ); // lookup for persistent connections if ( $this->TRY_persistent == true && ini_get( "mysql.allow_persistent" ) == true ) { $conn = mysql_pconnect( $SERVER, $USER, $PASS ); $this->STATUS_persistent = true; // persistente Verbindung wurde aufgebaut } else { $conn = mysql_connect( $SERVER, $USER, $PASS ); $this->STATUS_persistent = false; // es wurde keine persistente Verbindung aufgebaut } if ( !$conn ) { return $this->error( "no connection!" ); } // new in 3.0.0pre2 // if no database, connection is regardless bounded to $this->CONN. // That means, theres no database, but a connection. Now you have the chance to create one if ( !@mysql_select_db( $DBASE, $conn ) ) { $this->CONN = $conn; return $this->error( "no database!" ); } $this->akt_DBASE = $DBASE; $this->CONN = $conn; return true; } /** * MySQL::set_DBG() - sets Debug level * * <br> if you have some startup errors, it's fine to see them. * <br>0-liefert "nur" false zurck; Fehlerbehandlung in der aufrufenden Funktion * <br>1-zeigt Fehler per echo an, liefert false, lt das Script jedoch weiterlaufen * <br>2-"stirbt" mir Fehlermeldung ... die("Fehler) * <br>3-bricht ab ohne Fehleraus- oder Rckgabe (stiller Tod)<br> * * @param int $newdbg - 0->4 * @return boolean * @access public * @since 2005-10-06 */ function set_DBG($newdbg) { if ($newdbg >= 0 and $newdbg <= 4) { $this->DBG = $newdbg; return true; } else { return false; } } /** * MySQL::get_DBG() - sets Debug level * * <br> if you have some startup errors, it's fine to see them. * <br>0-liefert "nur" false zurck; Fehlerbehandlung in der aufrufenden Funktion * <br>1-zeigt Fehler per echo an, liefert false, lt das Script jedoch weiterlaufen * <br>2-"stirbt" mir Fehlermeldung ... die("Fehler) * <br>3-bricht ab ohne Fehleraus- oder Rckgabe (stiller Tod)<br> * * * @return int * @access public * @since 2005-10-06 */ function get_DBG() { return $this->DBG; } /** * MySQL::checkup() - checks sql-query is not empty, connection is still alive and query is right * * @param string $method method (insert, select etc) * @param string $sql sql query * @return boolean * @access protected * @since 2003-11-25 */ function checkup( $method="", &$sql ) { $sql = trim ( $sql ); $method = trim ( $method ); if ( empty( $sql ) ) { $temp = $method=="" ? "Name" : "Query"; return $this->error( $temp . " is empty: $sql " ); } if ( $method != "" && !eregi( "^" . $method, $sql ) ) { return $this->error( "Error, its not a " . $method . "-query: $sql " ); } if ( empty( $this->CONN ) ) { return $this->error( "No connection or connection lost" ); } if ( $method != "" && !eregi("^CREATE DATABASE", $sql) && $this->akt_DBASE=="") { return $this->error( "No database! Create database first, then change actiove DB!" ); } return true; } /** * MySql::new_db() * * creates new database * * @param string $name Datenbankname * @return bool */ function new_db( $name ) { $sql = "CREATE DATABASE " . $name; if ( $this->checkup( "", $sql ) != true ) { return $this->error( "an error occured!" ); } if ( mysql_query ( $sql , $this->CONN ) ) { return true; } else { return $this->error ( "error while creating database '$name'!" ); } } /** * MySql::change_db() * * changes active database * * @param string $name database * @return bool */ function change_db( $name ) { $name = trim ( $name ); if ( empty( $name ) ) { return $this->error( "DB-Name is empty: $name " ); } if ( empty( $this->CONN ) ) { return $this->error( "No connection or connection lost" ); } if ( mysql_select_db( $name ) ) { $this->last_DBASE = $this->akt_DBASE; $this->akt_DBASE = $name; return true; } else { return $this->error ( "error changing database!" ); } } /** * MySql::drop_db() * * drops a db * * @param string $name database * @return bool */ function drop_db( $name ) { if ( $this->checkup( "", $name ) != true ) { return $this->error( "no database name given" ); } if ( !mysql_query( "DROP DATABASE " . $name, $this->CONN ) ) { return $this->error ( "could not drop database!" ); } // if db dropped, change to last database, if exists if ( $name == $this->akt_DBASE && $this->last_DBASE != "" ) { $this->change_db( $this->last_DBASE ); } return true; } /** * MySql::create() * * sends every query to db whitsch begins with "create" - it is not case sensitive * * @param string $sql SQL-create-Query * @return bool */ function create( $sql ) { $result = $this->checkup( "create", $sql ); if ($result==false) { return $this->error( "creation fails." ); } if ( !mysql_query( $sql ) ) { $this->error( "could not create anything [ " . $sql . " ]" ); return false; } return true; } /** * MySql::empty_table() * * truncates a table * * @param string $name tablename * @return bool */ function empty_table( $name ) { $result = $this->checkup( "", $name ); if ($result==false) { return $this->error( "could not truncate." ); } if ( mysql_query ( "TRUNCATE TABLE " . $name, $this->CONN ) ) { return true; } else { $this->error ( "error truncating table '$name' !" ); return false; } } /** * MySql::optimize() * * optimizes table * * @param string $name tablename * @return bool */ function optimize( $name ) { $result = $this->checkup( "", $name ); if ($result==false) { return $this->error( "could not optimize." ); } if ( mysql_query ( "OPTIMIZE TABLE " . $name, $this->CONN ) ) { return true; } else { $this->error ( "error optimizing table '$name' !" ); return false; } } /** * MySql::drop_table() * * drops a table * * @param string $name table * @return bool */ function drop_table( $name ) { $name = trim( $name ); if ( empty( $name ) ) { return false; } if ( empty( $this->CONN ) ) { return false; } if ( mysql_query ( "DROP TABLE IF EXISTS " . $name, $this->CONN ) ) { return true; } else { $this->error ( "error dropping table!" ); return false; } } /** * MySQL::set_select_type() * * sets the resulttype of mysql_fetch_array used in sql->select() * * @param $type one out of "MYSQL_ASSOC", "MYSQL_NUM", "MYSQL_BOTH" * @return */ function set_select_type( $type ) { $allowed = array( MYSQL_NUM, MYSQL_BOTH, MYSQL_ASSOC ); if ( in_array( $type, $allowed ) ) { $this->SELECT_TYPE = $type; } return $this->SELECT_TYPE; } // select field return // 2 - always two dimensions // 1 - automatic one/two-dim function set_field_dimension($type = "0") { $allowed = array (1, 2); if (in_array($type, $allowed)) { $this->field_dimension = $type; } return $this->field_dimension; } function get_field_dimension() { return $this->field_dimension; } /** * MySQL::select() * * queries db with select and returns an array with one or two dimension, dependion on entities * if a entity is named INDEX_ASSOCIATION you'll have an associative array with this entity als key * SELECT id AS INDEX ASSOCIATION, name, land FROM xy ... * the array will be $result[id-entry][name|land] = value... * id-entry is in ( id1, id2 ...) * <br> * 07-26-2004 3.1.0<br> * + via MySQL->fiel_dimension, you could say, you want optional 1-d arrays or not * <br> * 01-30-2004 3.0.0pre3 <br> * + choose via constant SELECT_TYPE if you want to have NUM, ASSOC or BOTH Types in ARRAY * look at set_select_type() * <br> * 09-28-2004 3.1.0 <br> * + 2nd argument = True says, that output shout be smarty array * * @param string $sql * @param int $csvdata * @return array */ function select( $sql = "", $smarty = false ) { #echo "<br>".$sql."<br>"; $result=0; $this->time_start(); $this->checkup( "select", $sql ) or $this->error( "error queriing, its not a select-query: " . $sql ); $this->affected_rows = 0; // if there is an SHOW query, there is MYSQL_BOTH needed as mysql_fetch_array result type if ( eregi( "^show", $sql ) ) { $temp_type = $this->SELECT_TYPE; $this->SELECT_TYPE = MYSQL_BOTH; } $conn = $this->CONN; $results = mysql_query( $sql, $conn ) or $this->error( $sql ); if ( ( !$results ) or ( empty( $results ) ) ) { @mysql_free_result( $results ); return false; } $count = 0; $data = array(); $this->affected_rows = mysql_affected_rows ( $conn ); // create a smarty readable array if ( $smarty == true ) { while ( $row = mysql_fetch_array( $results, $this->SELECT_TYPE ) ) { // if there's only one column and the script is allow to create, // create a 1-d array array_push( $data, $row ); } // normal int associated array } elseif ( !ereg( "INDEX_ASSOCIATION", $sql ) ) { // normal results return (1-d, 2-d arrays) $anz_rows = mysql_num_fields( $results ); while ( $row = mysql_fetch_array( $results, $this->SELECT_TYPE ) ) { // if there's only one column and the script is allowed to create, // create a 1-d array if ( $anz_rows < 2 && $this->field_dimension == 1 ) { $data[$count] = $row[0]; } else { $data[$count] = $row; } $count++; } } else { // build index-assozitive 1-d/2-d array // entity named INDEX_ASSOCIATION would be the array-key while ( $row = @mysql_fetch_array( $results ) ) { $count = $row['INDEX_ASSOCIATION']; // if theres only one entity left, build a 1-d array if ( count( $row ) > 4 ) { // remove ASSOCIATIONs, not needed $data[$count] = $row; } else { if ( $row[1] == $row['INDEX_ASSOCIATION'] ) { $data[$count] = $row[0]; } else { $data[$count] = $row[1]; } } } } if ( eregi( "^show", $sql ) ) { $this->SELECT_TYPE = $temp_type; } @mysql_free_result( $results ); $this->commands["select"]++; $this->commands["sum"]++; $this->time_stop("select"); return $data; } /** * MySQL::get_affected_rows() * * returns number of rows that were affected by last query * * * @return int affected rows */ // FIXME: seams to have a bug; does not return right value function get_affected_rows(){ return $this->affected_rows; } /** * MySQL::insert() * * inserts a "insert"-query * * @param string $sql * @return int inserted id | affected rows */ function insert( $sql = "" ) { $this->time_start(); if ( empty( $sql ) ) { return false; } if ( !eregi( "^insert", $sql ) ) { return $this->error( "error, its not a insert-query: " . $sql ); } if ( empty( $this->CONN ) ) { return false; } $conn = $this->CONN; $results = mysql_query( $sql, $conn ) or $this->error( $sql ); $results = mysql_insert_id( $conn ); $results == 0 ? $results = mysql_affected_rows( $conn ): 1; $this->time_stop("insert"); return $results; } /** * MySQL::update() * * updates db * * @param string $sql * @return boolean true if result | false if no result */ function update ( $sql = "" ) { $this->time_start(); if ( empty( $sql ) ) { return false; } if ( !eregi( "^update", $sql ) ) { $this->error( "error, its not a update-query: " . $sql ); return false; } if ( empty( $this->CONN ) ) { return false; } $conn = $this->CONN; // cdhack for errors $results = mysql_query( $sql, $conn );//old: mysql_query( $sql, $conn ); if ($results==false) { $this->error( "could not exec update(): " . $sql ); return false; } $results = mysql_affected_rows( $conn ); $this->affected_rows = $results; if ($results==0) $results=true; $this->time_stop("update"); return $results; } /** * MySQL::delete() * * send delete query * * @param string $sql * @return int affected rows (since 3.2.1) */ function delete( $sql = "" ) { $this->time_start(); if ( $this->checkup( "delete", $sql ) != true ) { return $this->error( "no delete query: " . $sql); } $conn = $this->CONN; $results = mysql_query( $sql, $conn ); $results = mysql_affected_rows( $conn ); $this->time_stop("delete"); return $results; } /** * MySQL::alter() * * send alter query * * @param string $sql * @return */ function alter ( $sql = "" ) { $this->time_start(); if ( empty( $sql ) ) { return false; } if ( !eregi( "^alter", $sql ) ) { $this->error( "error, it's not a alter-query: " . $sql ); return false; } if ( empty( $this->CONN ) ) { return false; } $conn = $this->CONN; $results = mysql_query( $sql, $conn ); if ( !$results ) { return false; } $this->time_stop("alter"); return true; } /** * MySQL::select2csv() * * sends an array as csv-file / with save/open dialog * <br>(since 2004-11-01) In $this->csv_filename, $this->csv_extension the filename and * extension could be set. * * @since 2003-11-23 * @see array2csv * @see data2file * @param string $sql select query * @param int $append2file if 2, append to existing file with seperator * @return csv file / csv data */ function select2csv( $sql = "" , $append2file = "0" ) { $add_file = "cbadditionals.inc.php"; require_once $add_file; // manualy load the extensions $result = $this->select( $sql ); $data = array2csv( $result ); if ( $this->save2file != 0 ) { /* some temp calcs */$t = explode( "FROM ", $sql ); $t = explode( " ", $t[1] ); $table = $t[0]; $result = data2file( $data, $this->csv_filename, $this->csv_extension, 2, $table ); // write; if ( $result == 0 ) { return $this->error( "Could not write csv-data file to disc" ); } } switch ( $this->prompt4file ) { default: case 1: // prompt for save/open data2file( $data ); return $data; case 0: // do not prompt, return csv-string return $data; } // switch } /** * MySQL::query() * * @since 2004-07-26 * @param string $ / array - $sql query * @return false /true // resultarray */ function query( $sql = "" ) { // is array? more than one query if ( is_array( $sql ) ) { // more than 1 Query while ( list( , $val ) = each( $sql ) ) { $this->one_query( $val ); } // while return true; } return $this->one_query( $sql ); } //# end query() /** * MySQL::one_query() * * sends any sql query to database and, if possible, returns the result * it's dangerous to use this with user edited data (e.g. login forms) * * @access private * @param string $sql * @return */ function one_query( $sql = "" ) { $this->time_start(); $sql = trim( $sql ); if ( $this->checkup( "", $sql ) != true ) { $this->error( "Queryfehler..." ); } $data = array(); $count = 0; $conn = $this->CONN; $result = mysql_query( $sql, $conn ); if ( !eregi( "create|load|alter|outfile", $sql ) ) { while ( $row = @mysql_fetch_array( $result ) ) { $data[$count++] = $row; } //# while() } else { $data = $result; } @mysql_free_result( $result ); $this->time_stop("query"); if($data==false) { $this->error($sql); } return $data; } // starts time m, function time_start() { if ($this->measure_time=0) { return true; } $tmp=explode(" ",microtime()); $this->temptime=$tmp[0]+$tmp[1]; unset($tmp); // Zeitmessung Start } // stops time measuring function time_stop($func) { //how often was the function called $this->commands[$func]++; $this->commands["sum"]++; // stop here if ($this->measure_time=0) { return true; } $tmp = explode(" ",microtime()); $microtime = $tmp[0]+$tmp[1]-$this->temptime; unset($tmp); // Zeitmessung Ende $this->commands[$func."_time"] += $microtime; $this->commands["sum_time"] += $microtime; } // destructor /* * @access public * @return bool */ function close() { return mysql_close($this->CONN); } } //# end class if (!function_exists("debug_print_backtrace2")) { // PHP4 workaround... not needed 4 PHP5 function debug_print_backtrace2() { echo "<pre>"; $ar = debug_backtrace(); //array_pop($ar); var_dump($ar); echo "</pre>"; } } define("CLASS_MYSQL", true); } // define ?>