Version: 1.2
Type: Class
Category: Databases
License: GNU General Public License
Description: A utility class I’ve written and refined over a few years to simply MySQL database access in PHP.
Most notable feature is the two sub-classes that make it easy for one to do 2 and 3 nested queries on a MySQL database.
Code snippets provided within the class.
Hope it’s usefull to you as it’s been to me 🙂
<?php
///////////////////////////////////////////////////////////////////
// Revision 1.2 June 2005 //
// Developer: Mahapela Lebona <mahapela.lebona(at)malcosh.co.za> //
// License : Freeware //
// History : June 2002 - Creation //
// June 2005 - Added Subclass Cmysql2Query and //
// Cmysql3Query for double and triple //
// nested MySQL queries //
// //
///////////////////////////////////////////////////////////////////
/*
Class Cmysql
This class is designed to simplify the use of MySQL by incorportating many
common tasks and best practices into a neat class.
Example uses:
[database.php]
include("mysqlclass.php");
$sql = new Cmysql("localhost","mahapela","password","mytest");
[index.php]
include("database.php");
$userquery = "SELECT * from users where username = '" . $_POST['username'] . "' and password = '" . $_POST['password'] . "'";
if($sql->query($userquery)
{
if($sql->totalrows > 0)
{
//user can log in
}
else
{
//user not welcome
}
}
.....
if($sql->query("select * from posts where username = '$_POST['username']'"))
{
if ($sql->totalrows > 0)
{
do
{
echo $sql->row["POST_ID"] . ": " . $sql->row[2] . "<br>";
}while($sql->next_row());
}
else
{
//No posts
}
}
.....
$insertquery = "INSERT into posts VALUES ('$_POST['POST']') where userid = 1";
$sql->query($insertquery);
if ($sql->affected_rows > 0)
{
//Post insert successful
}
.....
$deletequery = "DELETE from posts";
if($sql->query($deletequery))
{
echo $sql->affected_rows;
}
*/
class Cmysql
{
var $totalrows = '';
var $result = '';
var $row = '';
var $error = false;
var $affected_rows = '';
var $resource = '';
/*
Name : Cmysql
Arguments: $server,$user,$password,$database
Returns : <none>
Purpose : Constructor
Notes : Used to quickly log into a database server and
select database
*/
function Cmysql($server,$user,$password,$database)
{
$this->connect($server,$user,$password,$database);
}
/*
Name : connect
Arguments: $sql_server,$sql_user,$sql_password,$sql_database
Returns : <none>
Purpose : Connect to MySQL database and select database
Notes : This function is seperated from the constructor to make
the class' logic easier to read.
This function is optimised for HTML. If the HTML is not requried, remove the HTML tags
*/
function connect($sql_server,$sql_user,$sql_password,$sql_database)
{
$this->resource = @mysql_connect($sql_server,$sql_user,$sql_password) or die("<b>Fatal MySQL error in Cmysql class [function :connect()] </b>:<br><br><b>[Problem]</b><br>Failed to connect to mysql server <i>(".$sql_user."@".$sql_server. ")</i><br><br><b>[Possbile Causes]</b><br>a) Server name, username or password incorrect<br>b) Server offline <br><br><b>[Suggested Remedies]</b><br>a) Check server name, username and password used to instantiate class<br>b) Check to see that server is online with another Mysql client");
mysql_select_db($sql_database);
}
/*
Name : is_mysql_transaction
Arguments: $sqlquery
Returns : true = transaction or false = not transaction
Purpose : Check if query is a transaction that alters row or not
Notes : Called from 'query' function as a helper funtion
*/
function is_mysql_transaction($sqlquery)
{
$sqlquery = strtoupper($sqlquery);
if (substr($sqlquery,0,6) == "DELETE" ||
substr($sqlquery,0,6) == "INSERT" ||
substr($sqlquery,0,6) == "UPDATE" ||
substr($sqlquery,0,6) == "REVOKE" ||
substr($sqlquery,0,6) == "DELETE" ||
substr($sqlquery,0,6) == "CREATE" ||
substr($sqlquery,0,5) == "INDEX" ||
substr($sqlquery,0,5) == "ALTER" ||
substr($sqlquery,0,5) == "GRANT" ||
substr($sqlquery,0,4) == "LOCK" ||
substr($sqlquery,0,4) == "DROP")
{
return true;
}
else
{
return false;
}
}
/*
Name : query
Arguments: $sqlquery
Returns : true = no error or false = error found
Purpose : Send query to server
Notes : Most commonly used function in class in conjunction with next_row()
*/
function query($sqlquery)
{
//If the query is a transaction that alters rows, it will not return rows therefore disable
//flag to get results
if ($this->is_mysql_transaction($sqlquery))
{
$getresult = false;
}
else
{
$getresult = true;
}
//big daddy
$this->result = @mysql_query($sqlquery,$this->resource);
if (mysql_error() != "")
{
$this->showerror(mysql_error(),mysql_errno(),$sqlquery);
$this->error = true;
return false;
}
else
{
$this->error = false;
if ($getresult)
{
$this->row = mysql_fetch_array($this->result,MYSQL_BOTH);
$this->totalrows = mysql_num_rows($this->result);
return true;
}
else
{
$this->affected_rows = mysql_affected_rows();
return true;
}
}
}
/*
Name : next_row()
Arguments: <none>
Returns : $this->row
Purpose : Moves internal pointer of results to next row. Makes it easier to access the next row in a loop.
Notes : <none>
*/
function next_row()
{
$this->row = mysql_fetch_array($this->result,MYSQL_BOTH);
return $this->row;
}
/*
Name : showerror()
Arguments: $errormsg (error message), $errornum (the internal mysql error number), $qry (query)
Returns : <none>
Purpose : Displays mysql error.
Notes : This function is optimised for HTML. If the HTML is not requried, remove the HTML tags
*/
function showerror($errormsg,$errornum,$qry)
{
echo "<br><b>Class Cmysql - SQL Error in Query:</b><br> <i>" . $qry . "</i><br><br><b>[MySQL Error]</b><br>" . $errornum . ": " . $errormsg . "<br/><br/>";
}
}//class Cmysql
/*
Class Cmysql2Query
This class is a subclass of Cmysql. It adds a second set of query functions (with suffix of 2) to the class.
It's useful when doing nested MySQL queries on one database within the confines of a single class. Retains all
the functionality of Cmysql.
Example use:
[database.php]
include("mysqlclass.php");
$sql = new Cmysql2Query("localhost","mahapela","password","mytest");
[index.php]
include("database.php");
$query_1 = "select friend_id from people where person_id = 1";
if($sql->query($query_1))
{
do{
$query_2 = "select * from people where person_id = $sql->row['friend_id']";
if($sql->query2($query_2))
{
do {
echo "Friends name: " . $sql->row2["NAME"] . "<br>";
}while($sql->next_row2());
}
}while($sql->next_row());
}
*/
class Cmysql2Query extends Cmysql {
var $totalrows2 = '';
var $result2 = '';
var $row2 = '';
var $error2 = false;
var $affected_rows2 = '';
/*
Name : Cmysql2Query
Arguments: $server,$user,$password,$database
Returns : <none>
Purpose : Constructor
Notes : Calls constructor of parent class
*/
function Cmysql2Query($server,$user,$password,$database)
{
$this->Cmysql($server,$user,$password,$database);
}
/*
Name : query2
Arguments: $sqlquery
Returns : true = no error or false = error found
Purpose : Send query to server
Notes : Most commonly used function in class in conjunction with next_row()
*/
function query2($sqlquery)
{
//If the query is a transaction that alters rows, it will not return rows therefore disable
//flag to get results
if ($this->is_mysql_transaction($sqlquery))
{
$getresult = false;
}
else
{
$getresult = true;
}
//big daddy
$this->result2 = @mysql_query($sqlquery,$this->resource);
if (mysql_error() != "")
{
$this->showerror(mysql_error(),mysql_errno(),$sqlquery);
$this->error2 = true;
return false;
}
else
{
$this->error2 = false;
if ($getresult)
{
$this->row2 = mysql_fetch_array($this->result2,MYSQL_BOTH);
$this->totalrows2 = mysql_num_rows($this->result2);
return true;
}
else
{
$this->affected_rows2 = mysql_affected_rows();
return true;
}
}
}
/*
Name : next_row2()
Arguments: <none>
Returns : $this->row2
Purpose : Moves internal pointer of results to next row. Makes it easier to access the next row in a loop.
Notes : <none>
*/
function next_row2()
{
$this->row2 = mysql_fetch_array($this->result2,MYSQL_BOTH);
return $this->row2;
}
} //class Cmysql2Query
/*
Class Cmysql3Query
This class is a subclass of Cmysql2Query. It adds a third set of query functions (with suffix of 3) to the class.
It's useful when doing triple nested *shudder* MySQL queries on one database within the confines of a single class. Retains all
the functionality of Cmysql and Cmysql2Query.
*Warning: It gets ugly when you start nesting this deep...
Example use:
[database.php]
include("mysqlclass.php");
$sql = new Cmysql3Query("localhost","mahapela","password","mytest");
[index.php]
include("database.php");
$query_1 = "select friend_id from people where person_id = 1";
if($sql->query($query_1))
{
do{
$query_2 = "select best_friend_id from people where person_id = $sql->row['friend_id']";
if($sql->query2($query_2))
{
do{
$query_3 = "select * from people where person_id = $sql->row2['best_friend_id']";
if($sql->query3($query_3))
{
do{
echo "Friends Best Friend name: " . $sql->row3['NAME'] . "<br>";
}while($sql->next_row3());
}
}while($sql->next_row2());
}
}while($sql->next_row());
}
*/
class Cmysql3Query extends Cmysql2Query {
var $totalrows3 = '';
var $result3 = '';
var $row3 = '';
var $error3 = false;
var $affected_rows3 = '';
/*
Name : Cmysql3Query
Arguments: $server,$user,$password,$database
Returns : <none>
Purpose : Constructor
Notes : Calls constructor of parent class
*/
function Cmysql3Query($server,$user,$password,$database)
{
$this->Cmysql2Query($server,$user,$password,$database);
}
/*
Name : query3
Arguments: $sqlquery
Returns : true = no error or false = error found
Purpose : Send query to server
Notes : Most commonly used function in class in conjunction with next_row()
*/
function query3($sqlquery)
{
//If the query is a transaction that alters rows, it will not return rows therefore disable
//flag to get results
if ($this->is_mysql_transaction($sqlquery))
{
$getresult = false;
}
else
{
$getresult = true;
}
//big daddy
$this->result3 = @mysql_query($sqlquery,$this->resource);
if (mysql_error() != "")
{
$this->showerror(mysql_error(),mysql_errno(),$sqlquery);
$this->error3 = true;
return false;
}
else
{
$this->error3 = false;
if ($getresult)
{
$this->row3 = mysql_fetch_array($this->result3,MYSQL_BOTH);
$this->totalrows3 = mysql_num_rows($this->result3);
return true;
}
else
{
$this->affected_rows3 = mysql_affected_rows();
return true;
}
}
}
/*
Name : next_row3()
Arguments: <none>
Returns : $this->row3
Purpose : Moves internal pointer of results to next row. Makes it easier to access the next row in a loop.
Notes : <none>
*/
function next_row3()
{
$this->row3 = mysql_fetch_array($this->result3,MYSQL_BOTH);
return $this->row3;
}
}//class Cmysql3Query
