Re: Two MySQL connections in one script not working as expected

On Sep 27, 2005, at 5:23 PM, M. Sokolewicz wrote:

Charles Kline wrote:

On Sep 27, 2005, at 3:42 PM, Andy Pieters wrote:

Hi Without you actually showing us these class files we can only guess but a common mistake is this: mysql_open(connection details) mysql_query(query) In those cases the last opened handle is used. To prevent this, use this syntax $db1=mysql_open(connection for db1); $db2=mysql_open(connection for db2); mysql_query($db1,$query_db_1); mysql_query($db2,$query_db_2); If you have used this syntax then check your class if it is using a global variable to hold the database handle and if it does make it a class variable instead Instead of $db=null class db {function db() {$GLOBALS['db']=mysql_open(... do instead class db {var $db=null; function db() {$this->db=mysql_open That way you can instanciate as many instances of the class as you like and each will have its own database handle. HTH Andy

What I have is more like this: class db { var $dbpath = "localhost"; var $dbname = "testdb"; var $dblogin = "test"; var $dbpass = "test"; function db() {

    $link = mysql_connect($this->dbpath, $this->dblogin, $this- 

dbpass) or die ('Not Connected: ' . mysql_error());

mysql_select_db($this->dbname, $link) or die ('Can\'t use this database: ' . mysql_error()); } function retrieveData( $sql ) {

    $rs = mysql_query( $sql ) or die("Invalid query: " . mysql_error ());
    // if no result, return null
    if (($rs == null) || (mysql_num_rows($rs) == 0)) {
      return null;
    } else {
      return ( $rs );
    }

} function insertData( $sql ) {

    mysql_query( $sql );
    // return new id if insert is successful
    if ( mysql_affected_rows() > 0 ) {
      return ( mysql_insert_id() );
    } else {
      return null;
    }

} function updateData( $sql ) {

    $rs = mysql_query( $sql );
    if (mysql_affected_rows() > 0) {
      return ( $rs );
    } else {
      return null;  // no changes were made
    }

} } I then have another class with a different name and I changed the names of the functions as well. I have another set of class files that contain my various queries etc. Thanks for any help. Charles

that's where it's going wrong. You're not linking db::db()'s link in the db::insertData() mysql_query() function. Thus, the mysql_query function defaults to the "last opened mysql connection" regardless of object. A way to fix this is to store the link in $this->link instead, and changing your mysql_query()'s to mysql_query($sql, $this->link); same goes for your mysql_affected_rows() and mysql_insert_id() functions -tul

Hmm... still getting the same results. Here is my modified class files: class db { // database setup. These should be changed accordingly. var $dbpath = "localhost"; var $dbname = "blah1"; var $dblogin = "test"; var $dbpass = "test"; function db() {

    $this->link = mysql_connect($this->dbpath, $this->dblogin, $this-
dbpass) or die ('Not Connected: ' . mysql_error());
    mysql_select_db($this->dbname, $this->link) or die ('Can\'t use this database: ' . mysql_error());

} function retrieveData( $sql ) {

    $rs = mysql_query( $sql,$this->link ) or die("Invalid query: " . mysql_error());
    // if no result, return null
    if (($rs == null) || (mysql_num_rows($rs) == 0)) {
      return null;
    } else {
      return ( $rs );
    }

} function insertData( $sql ) {

    mysql_query( $sql,$this->link );
    // return new complaint id if insert is successful
    if ( mysql_affected_rows($this->link) > 0 ) {
      return ( mysql_insert_id($this->link) );
    } else {
      return null;
    }

} function updateData( $sql ) {

    $rs = mysql_query( $sql,$this->link );
    if (mysql_affected_rows($this->link) > 0) {
      return ( $rs );
    } else {
      return null;  // no changes were made
    }

} } and stored in another file... class db2 { // database setup. These should be changed accordingly. var $dbpath = "localhost"; var $dbname = "blah2"; var $dblogin = "test"; var $dbpass = "test"; function dbzen() {

    $this->link = mysql_pconnect($this->dbpath, $this->dblogin, $this->dbpass) or die ('Not Connected: ' . mysql_error());
    mysql_select_db($this->dbname, $this->link) or die ('Can\'t use this database: ' . mysql_error());

} function retrieveZenData( $sql ) {

    $rs = mysql_query( $sql,$this->link ) or die("Invalid query: " . mysql_error());
    // if no result, return null
    if (($rs == null) || (mysql_num_rows($rs) == 0)) {
      return null;
    } else {
      return ( $rs );
    }

} function insertZenData( $sql ) {

    mysql_query( $sql,$this->link ) or die("Invalid query: " . mysql_error());
    // return new complaint id if insert is successful
    if ( mysql_affected_rows($this->link) > 0 ) {
      return ( mysql_insert_id($this->link) );
    } else {
      return null;
    }

} function updateZenData( $sql ) {

    $rs = mysql_query( $sql,$this->link );
    if (mysql_affected_rows($this->link) > 0) {
      return ( $rs );
    } else {
      return null;  // no changes were made
    }

} }