Connect to a db, change databases and then reconnect.

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Connect to a db, change databases and then reconnect.

Post by hawleyjr »

I'm trying to store any query errors in a seperate table. I have a class the creates a mysql connection. The issue I'm having is the error table is in a seperate database and I need to connect back to the original database after I insert the error. I'll try to explain better below.

What I'm trying to do:

Code: Select all

<?php
/* CONNECT TO DB*/
$MSC = new mysqlConnection(DB1,LEVEL_READ_INSERT_DELETE);
	
	/* RUN QUERY */
        $qry = "INSERT INTO TABLENAME(VAL1,VAL2) VALUES('$someVar',$somevar2) WHERE ID=$xyz"; 

	$result = @mysql_query($qry); 
    
      	if(!$result){ //AN ERROR OCCURED, INSERT LOG INTO ERROR TABLE
      		insertErrorIn_Error_Log(ERROR_ID_1,$qry);
      	}
      	
      	$qry = "SELECT * FROM TABLE2 WHERE ID=$xyz";
      	...
      	..
      	.
?>
Insert Error Function:

Code: Select all

<?php

function insertErrorIn_Error_Log($error_id,$qry){
	
	$MSC = new mysqlConnection(DB2,LEVEL_INSERT);
	
	$qry = "INSERT INTO ERROR_TABLE($error_id,'$qry')";
	@mysql_query($qry);
	
	//SET CONNECTION BACK TO ORIGINAL CONNECTION
	??????????
                /* In order for the code to keep working, I need to re-connect
 back to the original database. Below are some example I’ve tried; 
I'm just not satisfied with the functionality. */
}
?>
DB connection Class:

Code: Select all

<?php

/* The database connection class accepts two predefined constants. The first parameter identifies the database to connect to. The second parameter determines the user permissions level. */

class mysqlConnection{ 

var $user_pass;
var $dbh;
var $db_identifier;
var $access_level;

	function mysqlConnection($db_identifier,$access_level){

		$this->user_pass = array(
			LEVEL_READ_INSERT_DELETE =>array('UNAME'=>'xyz','PASS'=>'xyz'),
			LEVEL_INSERT =>array('UNAME'=>'xyz3','PASS'=>'xyz'),
			LEVEL_READ =>array('UNAME'=>'xyz4','PASS'=>'xyz '));
	
		//CLOSE ANY OPEN CONNECTIONS
		$this->dbclose();
		
		$this->db_identifier = $db_identifier;
		$this->access_level = $access_level;
		
		$db_identifier = lookupTableName($db_identifier);//THIS FUNCTION WILL RETURN THE REQUIRED DATABASE NAME
		
		$this->dbh=@mysql_connect ('localhost', $this->user_pass[$access_level]['UNAME'], $this->user_pass[$access_level]['PASS'])
			or ('Unable to connect to the database ');
		@mysql_select_db ($db_identifier) 
			or ('Unable to connect to database');	
		
	}	

	function dbclose(){
		@mysql_close();
	}

	function getDBIdentifier(){ return $this->db_identifier;}
	function getAccessLevel(){ return $this->access_level;}


}
?>
What I've tried; It works I'm just not happy with how it works.

Code: Select all

<?php
/* CONNECT TO DB*/
$MSC = new mysqlConnection(DB1,LEVEL_READ_INSERT_DELETE);
	
	/* RUN QUERY */
       $qry = "INSERT INTO TABLENAME(VAL1,VAL2) VALUES('$someVar',$somevar2) WHERE ID=$xyz"; 

	$result = @mysql_query($qry); 
    
      	if(!$result){ 
      		insertErrorIn_Error_Log($MSC,ERROR_ID_1,$qry);	
      	}
      	
      	$qry = "SELECT * FROM TABLE2 WHERE ID=$xyz";
      	...
      	..
      	.


function insertErrorIn_Error_Log($dbConn,$error_id,$qry){
	
	$orig_dbIdent = $dbConn->getDBIdentifier();
	$orig_accessLev =  $dbConn->getAccessLevel();
	
	$MSC = new mysqlConnection(DB2,LEVEL_INSERT);
	
	$qry = "INSERT INTO ERROR_TABLE($error_id,'$qry')";
	@mysql_query($qry);
	
	//SET CONNECTION BACK TO ORIGINAL CONNECTION
	$MSC = new mysqlConnection($orig_dbIdent,$orig_accessLev);
}
?>
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

tried:

Code: Select all

insert into db_name.table_name....
?? :)

that's of course, if the user of this database has insert access to the other database and table ;)
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Post by hawleyjr »

I know you can select databases by using database.table I just thought you had to use [php_man]mysql_select_db[/php_man]. See what I get for not reading the manual back to front! Thanks again Feyd.
d3ad1ysp0rk
Forum Donator
Posts: 1661
Joined: Mon Oct 20, 2003 8:31 pm
Location: Maine, USA

Post by d3ad1ysp0rk »

feyd wrote:tried:

Code: Select all

insert into db_name.table_name....
?? :)

that's of course, if the user of this database has insert access to the other database and table ;)
The only problem is, if the user can't connect at all (db down, etc..), it wont be able to run that query either.

Anyways, it's always nice to have a .txt log ;)
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

LiLpunkSkateR wrote:The only problem is, if the user can't connect at all (db down, etc..), it wont be able to run that query either.
true.. but that's a bigger problem than an invalid query ;)
Post Reply