db connections

[jaymc]

I have made a database class to handle mysql queries etc

 

One function I have made is to log mysql errors to a database table.

 

The issue is that the error is logged into a primary database although the error may be generated while querying another database

 

Primary Database = Master

Querying Database = JohnSmiths

 

 

Obviously to query JohnSmiths I must be currently selected into that database, however, if there is an error and an error needs to be inserted into Master I will have to select that database to insert

 

The problem arrises when the next part of my script needs to query JohnSmiths, it cant as its current selected into Master due to logging the error

 

Here is snipped of the code

 

mysql_select_db("JohnSMiths");
mysql_query("selecttt * from table"); this is a bad query which will call sqlError();

################
# LOG AN ERROR #
################

function sqlError($error) { 

mysql_select_db("Master"); //  All errors are written to the Master database

	$enviroment = print_r($_SERVER, true);
	$session = print_r($_SESSION, true);

	$insert = "INSERT INTO mysql_errors values(
		NULL, '".$this->sqlSafe($error)."',
		'".$_SERVER['SCRIPT_FILENAME']."',
		'".$this->sqlSafe($enviroment)."',
		'".$this->sqlSafe($session)."',
		NOW())";

	$this->sqlQuery($insert);

} 

// Query JohnSmiths table
mysql_query("Select * from table"); // This will be trying to look into MASTER table as I last selected that whilst logging the error. It needs to be querying the JohnSmiths table and obviously I cant call mysql_select_db() before every single query incase the previous one caused an error

 

Any ideas what I can do here?

[PFMaBiSmAd]

You can specify a database_name.table_name in your query -

 

INSERT into yourdatabase.yourtable the_rest_of_your_normal_query_here

[jaymc]

Great idea!

 

Overlooked that one

[jaymc]

Is there also a way to do it in php though?

 

For future refference as I have a few other things where your example is not relivent e.g.

 

$q = "SHOW CREATE TABLE $dbName.$tableName"
$doq = mysql_query($q);
$data = mysql_fetch_assoc($doq);

mysql_query($data[Create Table]);
// This needs to be run in a different database to $dbName and do not want to regex or str_replace the create view of a table to inject a database name as $data[Create Table] returns the below

 

CREATE TABLE `clients` (
  `id` int(6) unsigned NOT NULL auto_increment,
  `companyName` varchar(50) NOT NULL,
  `credits` int(7) unsigned NOT NULL,
  `registered` datetime NOT NULL default '0000-00-00 00:00:00',
  `active` enum('0','1') NOT NULL default '1',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8