PHPBuilder - CMysql - MySQL class with nested query functionality



RSS Twitter
Snippets Databases

CMysql - MySQL class with nested query functionality

by: Mahapela
|
June 29, 2005

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

Comment and Contribute

Your comment has been submitted and is pending approval.

Author:
Mahapela

Comment:



Comment:

(Maximum characters: 1200). You have characters left.