[RESOLVED] php defining a class from within a class method

[iceomnia]

class db
{

var $query = "";
var $db = "";

function db()
{
	global $g;

	$this->db = @mysql_connect($g['db_host'], $g['db_username'], $g['db_password']);	

	if (!$this->db)
		die ($this->debug(true));	

	$selectdb = @mysql_select_db($g['db_database']);

	if (!$selectdb)
		die ($this->debug());

} // end constructor


function /* c# [] Array */ select($query, $maxRows=0, $pageNum=0, $start=0)
{
	$this->query = $query;

	// start limit if $maxRows is greater than 0
	if ($maxRows>0)
	{
		$startRow = $pageNum * $maxRows;
		$query = sprintf("%s LIMIT %d, %d", $query, $startRow, $maxRows);
	}	

	$result = mysql_query($query, $this->db);

	if ($this->error())
		die ($this->debug());

	$output=false; // Set the output to be bool false.

	for ($n=0+$start; $n < mysql_num_rows($result); $n++)
	{
		$row = mysql_fetch_assoc($result);
		$output[$n] = $row;
	}

	return $output;

} // end select

function query($query)
{
	$this->query = $query;

	$result = mysql_query($query, $this->db);

	if ($this->error())
		die ($this->debug());

	return $result;
}
}

Here's class one

class products
{

// Constructor
function products()
{
	// Empty constructor
}

// Find the amount of products in a category (or for a search) 
// and it's children's categories
public function returnSearchProductCount($catId=0, $keywords='', $manId=0, $new=0, $special=0)
{
	global $glob;

	$db = new db();

	(int) $items = 0;

	$query = self::returnProductQuery($keywords, $catId, $manId, $new, $special);

	$thisRes = $db->query($query);

	$items	+=	mysql_num_rows($thisRes);

	$res = $db->query(
		sprintf("SELECT `catId` FROM %s WHERE `parentId` = %s ", $glob['table_categories'], $db->mySQLSafe($catId))
	);

	while ($row = mysql_fetch_assoc($res))
	{
		$qry = self::returnProductQuery($keywords, $row['catId'], $manId, $new, $special);
		//echo $qry;
		$item = $db->query($qry);

		//echo $item." Query Exectuted?<br />";

		$items	+=	mysql_num_rows($item);

		self::returnSearchProductCount($row['catId'], $keywords, $manId, $new, $special);
	}

	// Close
	$db->close();

	return $items;
}

So as you can see, I am trying to initiate an instance of the db class inside the public method of the products class.

This is the correct way to do it with c# (from what i've read on php.net) it should work, and I am using php5.

Is this the wrong way to do it.

Someone help me please?

[laserlight]

How does it not work? Did you include the db file?

[NogDog]

That should be one workable implementation. A couple common alternatives would be to (a) have the constructor method instantiate the object as a class variable which any member method could then access, or (2) instantiate it outside of the class and pass it as a reference to the class constructor.

(a)

require_once db.class.php;

class Example
{
   $private $db;

   public function __construct()
   {
      $this->db = new db();
   }

   public function test()
   {
      $query = "SELECT blah blah blah";
      $this->db->query($query);
   }
}

(b)

class Example
{
   private $db;

   public function __construct(db $db)  // first "db" is "type hinting"
   {
      $this->db = $db;
   }

   public function test()
   {
      $query = "SELECT blah blah blah";
      $this->db->query($query);
   }
}

// usage:
require_once 'db.class.php';
$db = new db();
$test = new Example($db);

[iceomnia]

Hi,

...and thank you for your quick response.

I require that I only open a db connection eg.

$db = new db();

and then close the connection when I am done with it. eg.

$db->close(); // Method I didn't include above.

However, with your solution, when I call a public method without initialising the products class it will either not call the constructor at all, or keep the database connection open.

I am calling products methods like this:

$dataArray = products::methodName(); // Outputs database info in an array.

I know this may seem a long winded excersize but I am creating a multi-layer shopping products' browse system made up of several multi-layer categories, keywords, manufacturers etc etc.

Much like this:

http://www.dabs.com/productlist.aspx?&CategorySelectedId=11018&NavigationKey=11018

The category levels are almost infinate and then there are product types too! - Also you can refine your search with keywords and specific manufacturers.

It displays the product of all sub-categories, no matter where you are in the matrix.

Not really that complicated with recursion etc, but still I am trying to write this in oop as I am going to use some of the code and modify it for a c# shop I am building for another client.

[NogDog]

Well, there's normally nothing "wrong" with leaving a DB connection open, as it is automatically closed when your script terminates. (Unless you are using something like mysqlp_connect(), which is a whole 'nother can of worms).

Personally, I like to create my DB class as a [man]singleton[/man] pattern, then have each class reference it as needed. This way the database class is only instantiated once, regardless of how many objects reference it. Also, I suppose you could add a __destruct method to it that includes a connection closure, to ensure that the connection is closed whenever all references to the DB object are terminated (though again, this is normally really not necessary).

[iceomnia]

Yes but since I am only referencing the specific method with

className :: MethodName();

does it still call the constructor? - as I am not creating an instance of the class it's self, but simply just the public Method?

[NogDog]

Not 100% I'm following what you want to do. If you want to have a statically called method in a class that will access your database class, you could still use the singleton pattern for your DB class. In that case, the static method would get an instance of the DB class much like you did in the original example, except you would call the DB singleton class's static method for getting an instance of it, e.g.:

DB.class.php:

class DB
{
   private static $instance;
   private function __construct()
   {
      // do db connection stuff here.
   }
   public static function getInstance()
   {
      if (!isset(self::$instance)) {
         $c = __CLASS__;
         self::$instance = new $c;
      }
      return self::$instance;
   }
   public function query($sql)
   {
      // do a query and return result...
   }
   // other DB methods...
}

Sample.class.php:

require_once 'DB.class.php';
class Sample
{
   public static function someQuery()
   {
      $db = DB::getInstance();
      return $db->query('some query here');
   }
}

Usage:

require_once 'Sample.class.php';
$test = new Sample();
$result = $test->someQuery();

[iceomnia]

Thanks for your help so far but I think you may not quite be getting what I am trying to do.

class db
{
	public db;

public function __constructor()
{
	$this->db = mysql_connect('host', 'user', 'pass');

	$db = mysql_select_db('db_name', $this->db);	
}

public function query($qry)
{
	return mysql_query($qry, $this->db);

}

public function close()
{
	mysql_close($this->db);
}

} // End db class


class products
{

public function returnProducts($catId)
{
	$output = array();

	$query = sprintf("SELECT [whatever] FROM [products table] WHERE catId = %s ", $catId);

	$db = new db();

	$items = $db->select($query);

	for ($n=0+$start; $n < mysql_num_rows($result); $n++)
	{
		$row = mysql_fetch_assoc($result);
		$output[$n] = $row;
	}

	$db->close();

	return $output;
}
} // End class products

// Then this is what I am trying to achieve....

$items = products::returnProducts($_GET['catId']);

$table = "<table>";

for ($i=0; $i<count($items); $++)
{

$table .= "<tr>
				<td>";

$table .= $items[$i]['productName'];

$table .= "</td>
	</tr>";

}

$table .= "</table>";

echo $table;

This way, when I call my product class methods, it's as if they are not in a class at all! - This is how I need it to work, as there isn't just catId, there are soooo many operators that the SELECT query will be made up from - depending on what options are sent to the returnProducts method (or function).

[iceomnia]

...in other words the products class initiates a db connection, retrieves info, processes it, then closes the connection, then outputs the data in an array.

...ready for manipulation...

But it doesn;t work at all, I get:

"12 is not a valid MySQL-Link resource"

as if it is not registering a valid resource in $this->db in the db class.

[laserlight]

It could be due to a typo:

public db;

should be:

public $db;

Actually, if feasible it should be:

private $db;

[iceomnia]

laserlight wrote:

It could be due to a typo:

Actually, if feasible it should be:

private $db;

Sorry, I just re-wrote the two classes to make what I'm doing understandable. I have it as:

private $db;

Sorry for the confusion.

[laserlight]

What is your current code?

[iceomnia]

class db
{
	private $db;

public function __construct()
{
	$this->db = mysql_connect('host', 'user', 'pass');

	$db = mysql_select_db('db_name', $this->db);	
}

public function query($qry)
{
	return mysql_query($qry, $this->db);

}

//////////////////////////////////
// Clean SQL Variables (Security Function)
////////
function mySQLSafe($value, $quote="'") { 

	// Stripslashes 

	if (get_magic_quotes_gpc())
	{ 
		$value = stripslashes($value); 
	} 
	// Quote value
	if (version_compare(phpversion(),"4.3.0")=="-1")
	{
		$value = mysql_escape_string($value);
	}
	else
	{
		$value = mysql_real_escape_string($value, $this->db);
	}

	$value = $quote . $value . $quote; 

	return $value; 
}

public function close()
{
	mysql_close($this->db);
}

} // End db class


class products
{

public function returnProductQueryFields() {

	$fields = "
	`p`.`sku` AS `sku`, 
	`p`.`productTitle` AS `name`, 
	`p`.`productDescTitle` AS `title`,  
	`p`.`productShortDescription` AS `desc`, 
	`p`.`productLongDescription` AS `html`, 
	`p`.`bullets` AS `bullets`, 
	`p`.`stock` AS `stock`, 
	`p`.`minPurchaseQty` AS `minQty`,
	`p`.`dimensions` AS `dims`,
	`p`.`weight` AS `weight`,
	`p`.`barcode` AS `barcode`,
	`p`.`productBrand` AS `productBrand`,
	`p`.`mpn` AS `mpn`,
	`p`.`colour` AS `colour`,
	`p`.`dateAdded` AS `dateAdded`,
	`p`.`dateDue` AS `dateDue`,
	`p`.`priceCost` AS `priceCost`,
	`p`.`priceCostSpecial` AS `priceCostSpecial`
	";

	return $fields;
}

/// This builds a product query based on the variables sent to it.
public function returnProductQuery($keywords, $catId = 0, $manId = 0, $new = 0, $special = 0)
{
	global $glob;

	$db = new db();

	$productQuery = sprintf("SELECT %s FROM %s AS `p`
		INNER JOIN %s AS `c` ON (`p`.`sku` = `c`.`sku`) WHERE ", 
			self::returnProductQueryFields(), $glob['table_products'], $glob['table_categories_idx']);

	if( isset($keywords) && !empty($keywords) )
	// using keywords.
	{	
		$productQuery .= "
		(`p`.`sku` 	= '%".$db->mySQLSafe($keywords, "")."%' OR 
		`p`.`productTitle` 	= '%".$db->mySQLSafe($keywords, "")."%' OR 
		`p`.`productDescTitle`	= '%".$db->mySQLSafe($keywords, "")."%' OR 
		`p`.`productShortDescription` 	= '%".$db->mySQLSafe($keywords, "")."%' OR 
		`p`.`productLongDescription` 	= '%".$db->mySQLSafe($keywords, "")."%' OR 
		`p`.`mpn`	= '%".$db->mySQLSafe($keywords, "")."%')   AND";
	}

	if ( isset($catId) && $catId != 0 && $catId != "" )
	{
		$productQuery .= "	`c`.`catId` = " . $db->mySQLSafe($catId) . "   AND";
	}

	if ( isset($manId) && $manId != 0 && $manId != "" )
	{
		$productQuery .= "	`p`.`manId` = " . $db->mySQLSafe($manId) . "   AND";
	}

	if ( isset($special) && $special != 0 && $special != "" )
	{
		$productQuery .= " `p`.`priceCostSpecial` != '0'   AND";
	}

	$productQuery = substr($productQuery,0,strlen($productQuery)-6);

	/*if ( isset($new) && $new != 0 )
	{
		$product_query = " `p`.`date_added` " . $db->mySQLSafe($_GET['man_id']) . " AND";
	}*/

	$productQuery .= " AND `web` = '1' ORDER BY `p`.`priceCost` ASC, `p`.`productTitle` ASC ";

	$db->close();

	//echo $product_query;
	return $productQuery;

}// end function.

public function returnProducts($catId=0, $keywords='', $manId=0, $new=0, $special=0)
{
	$output = array();

	$query = self::returnProductQuery($keywords, $catId, $manId, $new, $special);

	$db = new db();

	$items = $db->select($query);

	for ($n=0+$start; $n < mysql_num_rows($result); $n++)
	{
		$row = mysql_fetch_assoc($result);
		$output[$n] = $row;
	}

	$db->close();

	return $output;
}

} // End class products

// Then this is what I am trying to achieve....

$items = products::returnProducts($_GET['catId']);

$table = "<table>";

for ($i=0; $i<count($items); $++)
{

$table .= "<tr>
				<td>";

$table .= $items[$i]['productName'];

$table .= "</td>
	</tr>";

}

$table .= "</table>";

echo $table;

I think... I think, returnProductQuery is using the same link resource as returnProducts as returnProductQuery seems to $db->close() the db connection, then returnProducts opens it again with $db = new db();

However, when I look at the errors it seems that returnProducts is trying to use the same mysql resource id as the one returnProductQuery created.

Do classes inherit other classes' resources? - That is stupid, the whole concept of oop is that each class (regardless of whether it's initiaised or not) is it's own entity and has it's own internal resources.

In fact, it should never inherit anything unless you specifically send the resource or object to the respective class.

Does this make any sense to anyone?

[laserlight]

public function __constructor()
{
    $this->db = mysql_connect('host', 'user', 'pass');

$db = mysql_select_db('db_name', $this->db);
}

should be:

public function __construct()
{
    $this->db = mysql_connect('host', 'user', 'pass');

mysql_select_db('db_name', $this->db);
}

I think... I think, returnProductQuery is using the same link resource as returnProducts as returnProductQuery seems to $db->close() the db connection, then returnProducts opens it again with $db = new db();

They are different instances of the db class, so the link resource is different. Actually, since the link resource does not need to be different, this is rather inefficient. I would rather create the db object externally and then pass it to the functions that need it.

Do classes inherit other classes' resources?

No.

[iceomnia]

PS. The reason I am doing it this way it to use the products class to effectively open and close connections as and when I need them.

otherwise I would end up doing this all the time:

$product = new products();

$product->returnProducts($catId);

Whereas, the class is being used as a static class, almost as if it isn't a class, but a simple include file.

The benefits of doing it like this are as follows:

Every time I use a function, I know where it is eg.

className::MethodName(); - and name the class file the same as the class name and you know exactly where to find it to do updates.

I only open the DB when I need it, essential for the amount of connections to the database - almost 20,000 per hour.

I have also had to change the db to InnoDB to enable row level locking, instead of table locking due to the sheer amount of updates.

Just so many things and, I still need the program to be lightning qucik.

[laserlight]

Whereas, the class is being used as a static class, almost as if it isn't a class, but a simple include file.

If you want to use the products class as a kind of a namespace, making its member functions static may be appropriate.

[iceomnia]

OK, so....

$resource = mysql_connection(/* whatever */);

creates a "resource id" or an external connection resource id.

When you

mysql_close($resource);

you don't destroy the id the resource refers to, you've just closed the connection to db.

So therefore, php seems to keep it alive and the next time you initiate $db = new db();

the constructor, since it is using the same connection string picks up the old resource id, expecting the connection to still be there. However, because we have closed it, the resource id still exists, yet the connection doesn't.

Ooooooooohhhh, I'm cofusing myself now!

[laserlight]

you don't destroy the id the resource refers to, you've just closed the connection to db.

So therefore, php seems to keep it alive and the next time you initiate $db = new db();

the constructor, since it is using the same connection string picks up the old resource id, expecting the connection to still be there. However, because we have closed it, the resource id still exists, yet the connection doesn't.

I have never opened the same database twice in the run of a single script, so I was not sure of the effect.

The PHP Manual's entry on [man]mysql_connect/man states:

If a second call is made to mysql_connect() with the same arguments, no new link will be established, but instead, the link identifier of the already opened link will be returned. The new_link parameter modifies this behavior and makes mysql_connect() always open a new link, even if mysql_connect() was called before with the same parameters.

While we are on the topic, have you considered using the MySQLi extension instead? The PDO extension is more flexible, but it may be a little slower. Both of them provide an object based interface so you do not need to create your own db class.

[iceomnia]

Do you know the moral of this thread really is this:

mysql_connect - opens the connection

DON'T EVER USE

mysql_close() unless you never need to use that a db connection on that page again.

even mysql_ping() doesn't even re-open the connection as it says it does.

once mysql_close has been called, mysql is closed for the rest of the page.

Resolved.