Is this good coding technique? - OOP with MYSQL

[aaron118]

I'm wanting to learn more about good coding technique, so I have started trying to use OOP as well. Please take a look at this example, it is an order form I made, you'll notice I have to create two instances of the CPDatabase class, even though they both do the same job, but I have to create two otherwise it won't work:

 

<?php
require_once("./config.php");
    // Select all available product categories
$CDB = new CPDatabase;
$CDB->QueryRow("SELECT cid, cname FROM product_cats");

// Start the session varibles
session_start();
$uid = $_SESSION['uid'];
$username = $_SESSION['username'];
$userpass = $_SESSION['userpass'];
$firstname = $_SESSION['firstname'];
$lastname = $_SESSION['lastname'];
    
    $smarty->assign('username',$username); 
    $smarty->assign('firstname',$firstname);
    $smarty->assign('lastname',$lastname);	
$smarty->assign('page_title','Order Form');
$smarty->display('header.tpl');

// Check to see if the session varibles are empty
if(empty($uid) & empty($username) & empty($userpass) & empty($firstname) & empty($lastname))
	$smarty->display('order_content1.tpl');
else
	$smarty->display('order_content2.tpl');

    // Display each product category
$ci = 0;
while ($ci < $CDB->query_num_rows) {

	$cid = mysql_result($CDB->query_result,$ci,"cid");
	$cname = mysql_result($CDB->query_result,$ci,"cname");
	$smarty->assign('cname',$cname);
	$smarty->display('order_cat_top.tpl');

        // Select all available products from this category
	$PDB = new CPDatabase;
	$PDB->QueryRow("SELECT pid, pname, pprice FROM products WHERE cid = '$cid'");

	if($PDB->query_num_rows == 0)
		$smarty->display('order_no_products.tpl');

        // Display each product
	$pi = 0;
	while ($pi < $PDB->query_num_rows) {
		$pid = mysql_result($PDB->query_result,$pi,"pid");
		$pname = mysql_result($PDB->query_result,$pi,"pname");
		$pprice = mysql_result($PDB->query_result,$pi,"pprice");
		$smarty->assign('pid',$pid);
		$smarty->assign('pname',$pname);
		$smarty->assign('pprice',$pprice);
		$smarty->display('order_product_row.tpl');
		$pi++;
	}

	$ci++;
}
$CDB->Disconnect();

$smarty->display('order_bottom.tpl');
$smarty->display('footer.tpl');
?>

 

Here is the class code:

<?php
class CPDatabase
    {
        function CPDatabase()
        {
            global $config;
            require("".$config['includes']."db.php");
        }
        
        function QueryRow($query)
        {
            $this->query_result = mysql_query($query);
            $this->query_num_rows = mysql_num_rows($this->query_result);
            $this->query_data = mysql_fetch_array($this->query_result);
        }
        
        function InsertRow($query)
        {
            $this->query_result = mysql_query($query);
        }
        
        function UpdateRow($query)
        {
            $this->query_result = mysql_query($query);
        }
        
        // Disconnect from the MYSQL database
        function Disconnect()
        {
            mysql_close() or die("MYSQL
            ERROR: ".mysql_error());
        }
    }
?>

 

Any tips?

 

Thanks

[emehrkay]

so if you do

 

$CDB->QueryRow("SELECT pid, pname, pprice FROM products WHERE cid = '$cid'");

 

it wont work?

[aaron118]

You should notice that I have to create two instances of the CPDatabase class; DB and CDB. I want the program to work with only one instance of the class, thats what I need help on.

[aaron118]

I want it so I only have to create one instance of the class, but still be able to run multiple querys and use the query data. How is this achieved?

 

Thanks

[robbyc]

I usually create a static class which I call DBAccess which will perform my DB operations and returns the result.

 

e.g.

 

class DBAccess
{
private static $user = 'username';
private static $password = 'password';
private static $db = 'database';

private static function dbConnect()
{
	/*connect to DB*/
}
        
        private static function dbDC()
        {
              //disconnect from database
        }

private static function dbQuery($query)
{
	//Connect to DB
                self::dbConnect();
                //Perform Query
	$result = mysql_query($query) or die ('MySQL Error: ' .mysql_error());
                //disconnect from DB
                self::dbDC();
                //Return the result 
	return $result;
}
}

 

This can then be called statically

 

require_once[class.DBAccess.php];

$result = DBAccess::dbQuery('select * from yourtable');

 

 

[aaron118]

Say I want to do something like count the amount of rows selected from the query, how would I do this with your way?

 

Also can someone please show me the point of creating a class becasue :

$result = DBAccess::dbQuery('select * from yourtable');

could just be:

$result = mysql_query('select * from yourtable');

 

and there would be no need to create a class, am I right? I just don't understand the point of creating a class for this, can someone please make it a little clearer.

 

Thanks

[robbyc]

Ok i usually don't want to know how many rows are returned, I check mysql_num_rows and if it is 0 I return false.

 

If you wanted you could return an array instead of just the result.

 

public static function dbQuery($query)
{

//Connect to DB
self::dbConnect();
//Perform Query
$result = mysql_query($query) or die ('MySQL Error: ' .mysql_error());
//disconnect from DB
self::dbDC();

$num = mysql_num_rows($result);		
return array($num, $result);
}

 

Then access it with the following.

$results = DBAccess::dbQuery();
$numRows = $results[0];
$queryResult = $results[1];

 

You are right we could do it simply as you say without the need for a class, it is hard to see why with quite simple code.

The made ideas behind object oriented design are that of abstraction and encapsulation.

Each class should perform a specific function and the other classes may use it but they are not concerned with how it is achieved. (I apologies as I am not so good at explaining things)

 

Lets take for example a DBAccess class I usually put all my queries in here and make those methods public such as

 

public static function insertIntoRandom($value1, $value2)
{
	$mysqlQuery = 'insert into random (column1, column2) values (\''.$value1.'\', \''.$value2l.'\')';
	self::dbQuery($mysqlQuery);
}

 

This improves security as we keep the dbQuery method private not allowing other classes to directly query the database.

 

If this class is the only part of the application that knows about the database then if we change the database to say Oracle we do not need to search through the entire code for every single database access and change the code. We simple change a few methods in DBAccess and we're done.

 

Hope that kind of makes sense.

 

[aaron118]

Im not sure on what the array($num, $result); does? Also I have different mysql functions I like to use like mysql_num_rows and mysql_result, Im still not sure how I use these separatly with a class, I want to be able to return the class with both; how many rows (mysql_num_rows) and the result (mysql_result). Perhaps looking at my code above gain, it might give you an idea of what I mean, sorry if i'm not explaining very clear.

 

Thanks again