Tidying up my sql queries

[Teach]

Hello... I'm going through a bit of a complete overhaul of my website at the moment trying to tidy up all the code, and making it more streamlined.

One of my halts at the moment is with the way I process mysql queries... currently, I use the following almost everywhere:

$flag=mysql_fetch_array(mysql_query("Select * from flag where id_flag=".$user['location']));

I know this is a bad idea to use mysql_fetch_array before mysql_query, but I was wondering the following...

1) I am wanting to show in the footer of my page how many DB queries there were per page. I've seen some examples where instead of using mysql_query, I can use a custom function like "mysql_go" which would do the query, and bump up the queries to display. How would I go about doing this? I assume in my footer it would be as simple as an:

echo "DB Queries: ".$dbcount."";

2) In this my "mysql_go" function too, instead of having a die function if the mysql query fails, could it be integrated into the function so I don't have to put it everywhere?

3) When I use this new function, would it be acceptable to "mysql_fetch_array(mysql_go(x));" or would you suggest doing it another way?

Thanks for any assistance 🙂

[bpat1434]

I'd suggest writing a DB class and every time a specific method is used, then you increment a property of the class.

Since you're cleaning up code, I'd suggest you add to your code to check for bad queries. If the query fails, and you don't check for it, you'll have errors and won't know why. At least by checking the result before you use mysql_fetch_array, you can be sure that the result is an actual result and not an error of some kind.

Hope that helps.

[Teach]

Hi there, thanks for your help

Ok that makes sense

How would I make that simple class (I think a simple function like that function mysql_do() ) would be adequate for our use, but to integrate the amount of DB queries for us to echo out later?

EDIT: Just to add to this, all our queries use the mysql_real_escape_string so it doesn't need to incorporate that 🙂

Thanks!

[johanafm]

A simple db class using PEAR MDB2
Just put the ++$queryCount; statement in each function where you want to increase the query count

require_once(PEAR_DIR.'MDB2.php');


class DbManager
{
	protected $db;
	protected $queryCount;

function __construct($connectionString)
{
	queryCount = 0;
	$options = array(	'persistent' => true,
						'portability' => MDB2_PORTABILITY_ALL ^ MDB2_PORTABILITY_FIX_CASE);
	$this->db =& MDB2::connect($connectionString, $options);
	if (PEAR::isError($this->db))
		//die($this->db->getMessage());
		trigger_error($this->db->getMessage() . ", " .
						$this->db->GetDebugInfo(), E_USER_ERROR);
	$this->db->setFetchMode(MDB2_FETCHMODE_ASSOC);
}

public function DbDisconnect()
{
	$this->db->disconnect();
}

public function DbGetAll($queryString)
{
	$result = $this->db->queryAll($queryString);

	if (PEAR::isError($result))
		trigger_error($result->getMessage() . "<br>" . $result->getDebugInfo(), E_USER_ERROR);
	else
		++$queryCount;

	return $result;
}

public function Get_db() {
	return $db;
}

public function Get_queryCount() {
	return queryCount();
}
}

And then to create an instance of the db class. The $querycount property will be set to 0 when you create an instance.

// global instance of DbManager
$gDbManager = new DbManager((MYSQL_CONNECTION_STRING));

And to use the class to perform queries

$qry = "SELECT * FROM SomeTable";
$result = $gDbManager->DbGetAll($qry);

[bpat1434]

Almost.... but not quite 100% right johan. The function "Get_querycount" needs to return "$this->queryCount".

[Teach]

Thanks, I'll try and work with that 😃

Much appreciated