< Back to Code Samples
<?php
/****************************************************************
* mysql.class (Derived from a class listed in "Beginning PHP & MySQL".
* I've hacked it some, but for the most part it's intact. I
* just never saw a reason to write another, as this one does
* what I need it to.)
*
* PURPOSE: Low-level DBC class for connecting to MySQL Databases
* USAGE:
* // Establish the DBC object
* $db = new mysql("HOST:PORT","USER", "PASSWORD","SCHEMA");
* $db->connect();
* $db->select();
*
* // Running a query
* $db->query("SELECT * FROM table");
*
* // Getting the results (see comments below for further options)
* while ($obj = $db->fetch_object())
* {
* echo 'Column' . $obj->column . '<br />';
* }
*
******************************************************************/
class mysql {
private $linkid; // MySQL link identifier
private $host; // MySQL Host
private $user; // MySQL User
private $pass; // Mysql Password
private $db; // Mysql Database
private $result; // Query Result
private $querycount; // Total queries executed
/* Constructor */
function __construct($host, $user, $pass, $db){
$this->host = $host;
$this->user = $user;
$this->pass = $pass;
$this->db = $db;
}
/**
* Connects to the Database Server. Throws an exception on failure.
*/
function connect(){
try {
$this->linkid = @mysql_connect($this->host,$this->user,$this->pass);
if (! $this->linkid)
throw new Exception("Server is unavailable, or is undergoing maintenance.");
} // try
catch (Exception $e) {
die($e->getMessage());
} // catch
} // connect()
/**
* Select your Database. Throws an exception on failure.
*/
function select(){
try {
if (! @mysql_select_db($this->db, $this->linkid))
throw new Exception ("Could not connect to the MySQL Database!");
}
catch (Exception $e) {
die ($e->getMessage());
} // catch
} // select()
/**
* Plain-jane vanilla SQL query. Throws an exception on failure. Increments query count.
* Votes "yes" on proposition 187. It is advisable that this is only used for static queries.
* @param $query The literal query, all parameters already included.
* @return Technically, the result set -- but you'll want to use one
* of the shnazzy fetch_* functions below to grab the results.
*/
function query ($query) {
try {
$this->result = @mysql_query($query, $this->linkid);
if(! $this->result)
throw new Exception("The database query failed: " . $query);
}
catch (Exception $e) {
echo ($e->getMessage());
}
$this->querycount++;
return $this->result;
} // query ()
/**
* safe_query should be used for any instance where users have the ability to
* enter parametrical data. Use this to prevent SQL Injection attacks!
* @param $query The query (as C-style formatted string)
* @param /varaible/ Any number of arguments, provided the number matches the formatted string
* @return Boolean success status.
*/
function safe_query() {
$args = func_get_args();
$query = array_shift($args);
$args = @array_map("mysql_real_escape_string",(is_array($args[0]) ? $args[0] : $args));
$squery = vsprintf($query,$args);
return $this->query($squery);
} // safe_query()
/**
* Determine the total rows affected by the last query
* @return The number of rows affected.
*/
function num_rows(){
$count = @mysql_num_rows($this->result);
return $count;
} // num_rows()
/**
* Grab a row as an object.
* @return The next row in a $row->column format
*/
function fetch_object() {
$row = @mysql_fetch_object($this->result);
return $row;
} // fetch_object()
/**
* Grab a row as an indexed array.
* @return The next row in a $row[0] format
*/
function fetch_row() {
$row = @mysql_fetch_row($this->result);
return $row;
} // fetch_row()
/**
* Grab a row as an associated array.
* @return The next row in a $row => $column format
*/
function fetch_array() {
$row = @mysql_fetch_array($this->result);
return $row;
} // fetch_array
/**
* Shows the number of queries executed during the lifetime of this object.
* @return The number of queries excecuted.
*/
function num_queries() {
return $this->querycount;
} // num_queries
/**
* Returns the last ID by auto_increment
* @return The last ID inserted by "auto_increment".
*/
function last_id(){
return mysql_insert_id($this->linkid);
} // last_id()
/**
* Change the charset -- Informally useful when strange characters appearing on query results
* @param $which : the charset to use. Not validated.
*/
function charset($which = "utf8"){
mysql_query("SET NAMES '" . $which . "'", $this->linkid);
}
/**
* Print Rows - Returns each row of the result using the formatted string specified.
* @param $output_string A C-style formatted string.
* @see The formatted string with the appropriate data inserted
*/
function printf($output_string) {
$aryRow = array();
while ($aryRow = $this->fetch_array()):
vprintf($output_string, $aryRow);
endwhile;
}
/**
* S-Print Rows - send a formatted string, returns an array of strings */
* @param $output_string A C-style formatted string.
* @return An array, where each element is a row formatted by the string
*/
function sprintf($output_string) {
$aryRow = array();
$aryOutput = array();
while ($aryRow = $this->fetch_row()):
array_push($aryOutput, vsprintf($output_string, $aryRow));
endwhile;
return $aryOutput;
}
} // mysql class
?>