Connect Object Help (Newbie to OOP w/ PHP)

[themonkey40]

Ok so I am trying to create a class that connects to mysql and runs a query. Here is the code:

class dbConnect {
    public $host;
    public $user;
    public $passwd;
    public $dataB;
    
    function __construct($host,$user,$passwd,$dataB) {
        $this->host = $host;
        $this->user = $user;
        $this->passwd = $passwd;
        $this->dataB = $dataB;
    }
    
    function connect($sql) { 
    
        $conn=mysql_connect($host,$user,$passwd) or die (mysql_error());
        $db=mysql_select_db($dataB,$conn) or die ("Unable to connect to database1"); 
        
        $result = mysql_query($sql, $conn) or die ("Couldn't Connect"); 
            return $result; 
    }
}

$connect1 = new dbConnect("127.0.0.1","example_user","example_pass","database");

$db_query = $connect1->connect("SELECT * FROM database"); 

When I run this, I get the following error:
"Warning: mysql_connect() [function.mysql-connect]: Can't connect to local MySQL server through socket '/var/mysql/mysql.sock' (2)". I know that I can connect, I have done so hundreds of times, just not using the above code. I verified that the data is being passed into the class with the following code:

print "host: {$connect1->host}<br>";
print "user: {$connect1->user}<br>";
print "password: {$connect1->passwd}<br>";
print "database: {$connect1->dataB}"; 

This displays all the information as passed into the class. Any ideas why its not working?

[NogDog]

You want to use $this-> prepended to each class variable in the args for mysql_connect().

PS: You might want to move the connection part either to a separate method or into the constructor, as you likely will not want to connect to the DB each time a query is requested.

PPS: Note that unless you're doing this just for OOP practice/learning, with PHP 5 you already have OOP database classes with the MySQLi and PDO extensions.

[themonkey40]

That I am, practicing OOP. I made the $this-> change and it does work. Could you provide an example of what you mean by moving the connection to the constructor and as a seperate method?

[themonkey40]

also, is there a way to close the database connection in the dbconnect class after the query is run?

[Mindzai]

also, is there a way to close the database connection in the dbconnect class after the query is run?

Yes, but the question is why would you want to? If you want to close the connection at all it is probably best done in the class's destructor method, but it is not generally necessary (and it is actually less efficient) to close and reopen a connection before and after each query.

Generally you would create a connection in the class's constructor method, store the resource identifier as a class property and let PHP close the connection when the script stops execution.

Another tip, it is usually a very good idea to create an abstract superclass which defines methods to be implemented by DBMS-specific child classes. Not only does this allow you to abstract database operations and decouple you from a particular DBMS, it also gives you a place to define common functionality.

Here's a very simple example (I have implemented the close connection in the classes destructor as mentioned above):

<?php

abstract class Database {
    
    protected $link;
    
    protected abstract function connect($host, $user, $pass);
    protected abstract function disconnect();
    protected abstract function selectDb($dbname);
    public abstract function query($sql);
    public abstract function getAssoc($resourceId);
    public abstract function numRows($resourceId);
    // etc
    
    public function __construct($host, $user, $pass, $dbname) {
        if (!$this->link = $this->connect($host, $user, $pass)) {
            throw new Exception("Could not connect to $host as $user");
        }
        if (!$this->selectDb($dbname)) {
            throw new Exception("Could not connect select database $dbname");
        }
    }
    
    public function __destruct() {
        $this->disconnect();
    }
    
}

class MySqlDatabase extends Database {
    
    protected function connect($host, $user, $pass) {
        return mysql_connect($host, $user, $pass);
    }
    
    protected function disconnect() {
        return mysql_close($this->link);
    }
    
    protected function selectDb($dbname) {
        return mysql_selectdb($dbname, $this->link);
    }
    
    public function query($sql) {
        return mysql_query($sql, $this->link);
    }
    
    public function getAssoc($resourceId) {
        return mysql_fetch_assoc($resourceId);
    }
    
    public function numRows($resourceId) {
        return mysql_numrows($resourceId);
    }
    
}


// client code:
try {
    $db = new MySqlDatabase('localhost', 'root', 'pass', 'foo_db');
} catch (Exception $e) {
    die($e->getMessage());
}
if ($result = $db->query('SELECT * FROM `foo`')) {
    if ($db->numRows() > 0) {
        while ($row = $db->fetchAssoc($result)) {
            echo $row['id'];
        }
    }
}

?>

The obvious benefit here is that you can create a second concrete database class, say SqlServerDatabase, and because client code knows it is an instance of Database it can be confident of the interface and safely call the classes methods. Swapping database providers then just becomes a matter of creating a SqlServerDatabase object instead of a MySqlDatabase object - client code is blissfully unaware. (This is called polymorphism in the OO world in case you weren't aware of that).

[NogDog]

Or for extra credit you could look into using the "decorator pattern" to control which DBMS is used, so that you would always instantiate the same main database class name.

[themonkey40]

Mindzai, when I tried your cod, I got the following error.

"Warning: Missing argument 1 for MySqlDatabase::numRows(), called in /Library/WebServer/Documents/Websites/flowline/new_design/code_testing_class.php on line 66 and defined in /Library/WebServer/Documents/Websites/flowline/new_design/code_testing_class.php on line 52

Warning: mysql_numrows(): supplied argument is not a valid MySQL result resource in /Library/WebServer/Documents/Websites/flowline/new_design/code_testing_class.php on line 53"

Not sure why?

[Mindzai]

Well the error message is telling you why - the method expects an argument and the example code I posted doesn't provide it (I was typing directly into the forum reply box so didn't notice). I didn't really intend for you to copy and paste the code and expect it to work though, it was just an example of inheritance and polymorphism I thought might be useful since you said you were learning OOP. The point was the concept not the code itself.

Re the decorator pattern, when I write abstraction for databases (which I don't very often any more since I discovered PDO!), it tends to be for small and simple projects so I've always found the inheritance managable. I agree that for something larger where you start to get into multiple branches of inheritance switching to a composition based approach such as decorator is a good idea.