[SOLVED] Connection Class Instances

[244863]

Hi,

 

I have a php page that calls two connection classes to MSSQL databases. One class connects to a MSSQL 2000 instance and another to a MSSQL 2005 instance. These are both held in the same datalib file and both referenced in the same functions file.

When running the page it grabs data from which ever class is called first and won't grab anything from the second.

 

Code, Datalib.php:

 

$connectionPersonnel['server'] = 'server,port';

$connectionPersonnel['user'] = 'user';

$connectionPersonnel['pass'] = 'pass';

$connectionPersonnel['db'] = 'db';

 

 

#class file for MSSQL Database.

class mssqlClass {

 

#class file for MSSQL Database.

 

function connect($dbhost = NULL){

#This connects to the database, will let you know if you use the wrong credentials

global $connectionPersonnel;

if(! ISSET ($dbconnect)){

$dbconnect = mssql_Connect($connectionPersonnel['server'], $connectionPersonnel['user'], $connectionPersonnel['pass'], true);

}

if(! $dbconnect){

return 'Failed to Connect to Host';

}else{

$select = mssql_select_db($connectionPersonnel['db'], $dbconnect);

if(! $select){

return 'Failed to select Database';

}else{

return $dbconnect;

}

}

}

 

function getData ($query){

#returns the query as an array

$this->data_array = array();

$result = mssql_query($query);

while ($row = mssql_fetch_assoc($result)) {

$this->data_array[] = $row;                                                   

} // while

$m = $this->data_array;                   

return $m;

} // getData   

function query($query){         

#UPDATE, INSERT and DELETE function

$result = mssql_query($query) or die("query didn't work");

}

}

 

Code, functions.php:

 

include_once('datalib.php');

$db = new mssqlClass();

$conn = $db->connect();

global $conn;

 

Thanks.

[trq]

I can only see one class defined and used in your post.

[244863]

they are both identical, apart from one says

 

server, port = gc-hr01, 1400

and the other

server, port = gc-hr01/SQLserver2005, 1358

 

and one has functions of (connect) and the other (connect05)

[trq]

Can we at least see where you try and use both these classes? more importantly, why have you got two classes if the only difference is what server they connect too?

[244863]

They are both on the same server but they are two different instances (installs) of MSSQL

All I do is grab data from one

 

$sql = "SELECT * FROM Behaviours WHERE BehaviourID = '$ToBeAddressed'";

$ToBeAddressed = $db->getData ( $sql );

 

And then try and grab from another

 

$sql = "SELECT * FROM Employee WHERE Employee LIKE $staffNo";

$name = $db->getData ( $sql );

 

It gets the first one but wont then get the second

[trq]

You have assigned both your instances to the same $db variable.

[244863]

no sorry forgot to change the post

 

one is db05 -> getdatadb05

 

the other db -> getdata

[trq]

Look, without seeing your actual code we could be here making guesses all night. You want to help us help you? Post the relevant code (copy and pasted from your scripts).

[244863]

#if the db name contains spaces or hyphens, wrap it up in square brackets so it doesn't break. example: [this db]

$connectionPersonnel['server'] = 'gc-hr01,1433';

$connectionPersonnel['user'] = 'web';

$connectionPersonnel['pass'] = 'pass1';

$connectionPersonnel['db'] = 'DPMS';

 

 

#class file for MSSQL Database.

class mssqlClass {

 

#class file for MSSQL Database.

 

function connect($dbhost = NULL){

#This connects to the database, will let you know if you use the wrong credentials

global $connectionPersonnel;

if(! ISSET ($dbconnect)){

$dbconnect = mssql_Connect($connectionPersonnel['server'], $connectionPersonnel['user'], $connectionPersonnel['pass'], true);

}

if(! $dbconnect){

return 'Failed to Connect to Host';

}else{

$select = mssql_select_db($connectionPersonnel['db'], $dbconnect);

if(! $select){

return 'Failed to select Database';

}else{

return $dbconnect;

}

}

}

 

function getData ($query){

#returns the query as an array

$this->data_array = array();

$result = mssql_query($query);

while ($row = mssql_fetch_assoc($result)) {

$this->data_array[] = $row;                                                   

} // while

$m = $this->data_array;                   

return $m;

} // getData   

function query($query){         

#UPDATE, INSERT and DELETE function

$result = mssql_query($query) or die("query didn't work");

}

}

 

#if the db name contains spaces or hyphens, wrap it up in square brackets so it doesn't break. example: [this db]

$connectionPersonneldb05['server'] = 'GC-HR01\\SQLSERVER2005,3427';

$connectionPersonneldb05['user'] = 'webhr';

$connectionPersonneldb05['pass'] = 'pass2';

$connectionPersonneldb05['db'] = 'WEBHR';

 

 

#class file for MSSQL Database.

class mssqlClassdb05 {

 

#class file for MSSQL Database.

 

function connectdb05($dbhost=NULL){

#This connects to the database, will let you know if you use the wrong credentials

 

global $connectionPersonneldb05;

if(!ISSET($dbconnectdb05)){

$dbconnectdb05 = mssql_Connect($connectionPersonneldb05['server'], $connectionPersonneldb05['user'], $connectionPersonneldb05['pass'], true)

or die("Cannot connect to ".$connectionPersonneldb05['server']);

}

if(!$dbconnectdb05){

return 'Failed to Connect to Host';

}else{

$select = mssql_select_db($connectionPersonneldb05['db'], $dbconnectdb05);

if(!$select){

return 'Failed to select Database';

}else{

return $dbconnectdb05;

}

}

}

 

function getDatadb05 ($query){

#returns the query as an array

$this->data_array = array();

$result = mssql_query($query);

while ($row = mssql_fetch_assoc($result)) {

$this->data_array[] = $row;                                                   

} // while

$m = $this->data_array;                   

return $m;

} // getData   

function querydb05($query){         

#UPDATE, INSERT and DELETE function

$result = mssql_query($query) or die("query didn't work");

}

}

 

-------------------------------------------------------------------

 

include_once('datalib.php');

$db = new mssqlClass();

$conn = $db->connect();

global $conn;

 

$db05 = new mssqlClassdb05();

$conn05 = $db05->connectdb05();

global $conn05;

 

-----------------------------------------------------------------

include_once('functions.php');

$sql = "SELECT * FROM Behaviours WHERE BehaviourID = '$ToBeAddressed'";

$ToBeAddressed = $db->getData ( $sql );

 

$userEmail = $db05->getDatadb05 ( "SELECT * FROM Employee WHERE Employee = $SN" );

$userEmail = trim ( $userEmail [0] ['email'] );

[trq]

Firstly, I still don't see any point in the two classes, there both exactly the same, you could just as easily use one class but pass each instance different connection credentials. This brings me to my second point. Using globals completely defeats the purpose of creating objects in the first place. This really makes no sense at all.

 

Now, lets try and find your actual problem. Your getData* functions make no attempt to verify they return any result, this makes things pretty hard to debug.

[244863]

OK I understand about the using one class, the reason that I had two is because I thought it would be easier to debug with two.

Not sure about the global comment as this bit is not mine, but i would assume that by using the include() function I could just delete the global $conn; lines of code and that would sort that.

As for the verification of the getdata() function, im not sure of how to go about this as i thought I could just do this manually by printing out the returned value.

 

could you possibly give me some code to look at for all of this to see what you would do.

 

thanks,

[trq]

While the include idea will work, it still means you will need to manage two different classes. An easier method would be to make one class and pass it connections perameters. eg;

 

$sql2000 = new sqldb('host2000','user','pass','db');
$sql2005 = new sqldb('host2005','user','pass','db');

 

Anyway, your getData() method should probably throw an exception if your query fails, and return false if no results are found. eg;

 

function getDatadb05 ($query){
  $this->data_array = array();
  if ($result = mssql_query($query)) {
    if (mssql_num_rows($result)) {
      while ($row = mssql_fetch_assoc($result)) {
        $this->data_array[] = $row;                                                   
      }
      return $this->data_array;
    } else {
      return false;
  } else {
    throw new exception(mssql_error());
  }
}

 

Now you can at least see if you got any results before trying to use them. eg;

 

$sql = "SELECT * FROM Behaviours WHERE BehaviourID = '$ToBeAddressed'";
if ($ToBeAddressed = $db->getData ( $sql )) {
  // you got results to use
} else {
  // no results found
}

 

Theres still plenty of improvement could be made to these classes (class), in fact, I really wouldn't bother with them at all.

[244863]

What do you mean you would not bother?? what would you do?

 

thanks,

[trq]

All your classes do is put a wrapper around the mssql_* functions, why not use them directly? I mean, don't get me wrong, classes have allot of benefits when there well written, these however are not.

[244863]

So let me get this right, you would just do

 

mssql_Connect('gc-hr01\\sqlserver2005, 3427','webhr','pass01','webhr');

$info = mssql_query("SELECT * FROM Behaviours WHERE BehaviourID = '$ToBeAddressed'");

 

while($row = mssql_fetch_assoc($info)){

 

not sure about this bit??

 

}

[trq]

Yeah, given that your class wasn't particularly helpful or well written.

 

not sure about this bit??

Again, you still need to check your code succeeds before trying to use any results.

 

$sql = "SELECT foo, bar FROM Behaviours WHERE BehaviourID = '$ToBeAddressed'";
if ($results = mssql_query($sql)) {
  if (mssql_num_rows($result)) {
    while($row = mssql_fetch_assoc($results)){
      echo $row['foo'] . ' ' . $row['bar'] . '<br />';
    }
  } else {
    // no results found.
} else {
  // your query failed / handle error
}

[PFMaBiSmAd]

When running the page it grabs data from which ever class is called first and won't grab anything from the second.

 

That's because the mssql_query() function calls are not using the link resource returned by the mssql_connect() statement. All the connection specific mssql_ functions must use the correct link. You are using the link in the mssql_select_db() function calls, what happened when you got to the mssql_query() function calls?

 

You also need to store the link resource in a class variable so that it will be available to the other function(s) in the class.