You have the right idea -- but there are a lot of improvements that can be made.
First, think about the class itself (MysqlConnectClass) and what its purpose is.
The first thing I notice (and this is a relatively minor point) is that you have the word 'Class' in the name of your class. There's nothing wrong with that if you do that to all your classes as a naming convention, but if you really want to be thinking in terms of OO, everything will be a class so there's no point in typing the extra 5 characters.
Furthermore, it's a widely accepted convention to reserve [initial] capitalization for the names of your classes only -- nothing else (so I also recommend lower-casing the first letter of your method names). Naturally, as with any convention, not everyone agrees, but you certainly can't go wrong with it.
Ok, so what does that leave us with? MysqlConnect -- which I would rename to MysqlConnection because class names should, generally, be nouns since classes represent 'things'.
Now that we've refined the name, we need to think about what the purpose of a MysqlConnection class is. That's fairly obvious, the purpose is to handle a connection to a mysql database -- nothing more, nothing less. Keep in mind that "in most cases it is better to build small objects with specific task than to build large objects with many tasks" [p.11 "The Object-Oriented Thought Process" by Matt Weisfeld].
Next, think about what kinds of behaviors/actions a MysqlConnection object needs to be able to do in order to "handle a connection to a mysql database". These behaviors will become your methods and should be named with verbs since they represent actions. There are two obvious ones: open and close. But is there anything else that a MysqlConnection should do? Why have a MysqlConnection in first place? To execute a SQL statement and possibly do something with the result set. Now, re-read the last sentence. Notice anything? There were two verbs, and one in particular is catching my eye: execute (the other is 'do' but I'll address that later; in fact there were also two nouns: statement and result -- which I'll mention shortly).
Should we add an 'execute' method to MysqlConnection? Some people would say yes. Personally, I disagree with this -- but it's up for debate so I'll explain my thinking. What do you 'execute'? I already answered this: a statement. Which is a noun and therefore a good candidate for a class. What do you do with a statement? You execute it. So let's say we choose to create a Statement class and place the execute method on it (technically it's a MysqlStatement, but I'll use Statement for brevity). What else do you do with a statement? Not much, which is why some people choose not to implement a separate Statement class -- it doesn't do enough to make it worthwhile. Generally speaking, if a class only has one method, then it probably shouldn't be a class.
But, a Statement class is useful for than just executing SQL statements. Think about it for a second. What else do you frequently do with SQL statements other than executing them? You probably put the value of a variable in your statements all them time (e.g. "SELECT * FROM table WHERE id = $id"). A lot of times those variables contain data that came from a form submission by an end-user -- which means you can't trust it. That's why we have functions like mysql_escape_string() (which is very similar to addslashes()). Wouldn't it be great if the Statement class was responsible for doing things like addslashes() so you don't have to worry about it? I definitely think so. In fact, I can't think of a better place to put that kind of functionality. For that reason, I think that a Statement class is very worthwhile.
If we're going to have a separate Statement class with an execute method (instead of putting execute on MysqlConnection), how do we create a statement? See that verb? Why not put a create() method on your MysqlConnection class? Well, we should probably call it createStatement() so its purpose is a little clearer. Sounds good to me. Then we can be done with MysqlConnection, right? Not so fast.
We have to talk about how MysqlConnection gets its job done. What data does it need to open() a connection to a database? The name of the database server (host), the name of the database, a username, and password. And when we open() the connection we need to store the resource id, so we can later close() the connection (which reminds me, let's also add a getConnectionId() method, so our Statement objects will be able use the connection resource id when execute()'ing sql statements). Guess what, we just figured out all our properties: host, database name, username, password, and resource ID (you had 4 out of 5, Sillysoft). NOW we're done with MysqlConnection, right? Nope, no quite, but almost.
We need to figure out how a MysqlConnection will get access to the data that those properties store. Sillysoft, you hard coded them in the class, which is okay unless you want the class to be reusable. I would recommend passing them to MysqlConnection when you instantiate the class. That way you could connect to multiple mysql databases using the same class. If you want them hard-coded define some constants in a convenient file that always gets include()'d so you don't have to hunt for them.
Here's what we have so far (without error handling to keep things simple):
PHP Code:
// Previously MysqlConnectClass()
class MysqlConnection
{
var $connectionId;
var $database; // Previously $dbname
var $host; // Previously $dblocation
var $password; // Previously $dbpass
var $username; // Previously $dbuser
function MysqlConnection($host, $database, $username, $password)
{
$this->host = $host;
$this->database = $database;
$this->username = $username;
$this->password = $password;
$this->open();
}
// Previously CloseMysqlConn()
function close()
{
$this->connectionId = mysql_close($this->connectionId);
}
function &createStatement($sql)
{
return new MysqlStatement($sql, $this);
}
function getConnectionId()
{
return $this->connectionId;
}
function open()
{
$this->connectionId = mysql_connect($this->host, $this->username, $this->password);
mysql_select_db($this->database, $this->connectionId);
}
}
Now if you still want to hard code the connection parameters in a class you can easily extend the base class:
PHP Code:
class Sillysoft_MysqlConnection extends MysqlConnection
{
var $dblocation = '*****';
var $dbname = '****';
var $dbpass = '**';
var $dbuser = '***';
function Sillysoft_MysqlConnection()
{
$this->MysqlConnection($dblocation, $dbname, $dbuser, $dbpass);
}
}
Now let's flesh out the Statement class. We want to be able to pass parameters to the Statement and have it automatically put quotes around it and make sure the string is escaped. Then, when we execute the Statement, we want it to return a ResultSet (actually a MysqlResultSet), which is the other noun/class I mentioned a while ago. Like so:
PHP Code:
class MysqlStatement
{
var $connection;
var $parameters;
var $sql;
function MysqlStatement($sql, &$connection)
{
$this->connection =& $connection;
$this->parameters = array();
$this->sql = $sql;
}
function &execute()
{
$resultId = mysql_query($this->getPreparedSql(), $this->connection->getConnectionId());
return new MysqlResultSet($resultId);
}
function getPreparedSql()
{
$sql_parts = explode('?', $this->sql);
$sql = $sql_parts[0];
for ($i = 1, $max = count($sql_parts); $i < $max; $i++)
{
$sql .= $this->parameters[$i] . $sql_parts[$i];
}
return $sql;
}
function setParameter($index, $val)
{
$this->parameters[$index] = "'" . mysql_escape_string($val) . "'";
}
}
/*
// Usage:
$conn =& new MysqlConnection('localhost', 'my_db', 'my_user', 'my_pass');
$stmt =& $conn->createStatement(
'SELECT last_login FROM user WHERE username = ? AND password = ?');
$stmt->setParameter(1, $username);
$stmt->setParameter(2, $password);
$rs =& $stmt->execute();
*/
The ResultSet class is pretty simple too. What behaviors should it have? As a minimum, you should be able to iterate through the rows, get the total number of rows, get the fields within a row, and close/release/free the ResultSet. There may be other stuff that would be useful, but I'm keeping this example bare bones. Take a look:
PHP Code:
class MysqlResultSet
{
var $record;
var $resultId;
function MysqlResultSet($resultId)
{
$this->record = array();
$this->resultId = $resultId;
}
// Previously FreeMysqlMem()
function close()
{
mysql_free_result($this->resultId);
$this->record = array();
$this->resultId = NULL;
}
function getField($field)
{
return $this->record[$field];
}
// previously GetNumRows()
function getRowCount()
{
return mysql_num_rows($this->resultId);
}
// previously GetRowInfo() - sort of
function next()
{
$this->record = mysql_fetch_assoc($this->resultId);
return ($this->record !== FALSE);
}
}
Putting it all together, here's sample usage of all three classes.
PHP Code:
$conn =& new MysqlConnection('localhost', 'my_db', 'my_user', 'my_pass');
$stmt =& $conn->createStatement(
'SELECT model FROM car WHERE date >= ? AND date <= ? ORDER BY model ASC');
$stmt->setParameter(1, $start_date);
$stmt->setParameter(2, $end_date);
$rs =& $stmt->execute();
while ($rs->next())
{
echo $rs->getField('model') . '<br>';
}
$rs->close(); //optional
$conn->close(); //optional
These classes are seriously lacking in the way of error handling, but otherwise they work as is. And you can easily refactor them into a full-blown database abstraction layer, even if it only works with MySQL for now.
Keep in mind that everyone has there own opinions about what the best way of doing OOP is. For example, if you look at the PEAR database abstraction classes you'll see they have a much different approach than I (granted they do a lot more too -- but are very bloated). As you learn more and more about OOP you'll find better ways of doing things.
I don't know of any good articles off the top of my head. The vast majority of PHP specific articles, especially those dealing with OOP, are of very low quality. I wouln't look to them for examples of good OOP, I recommend looking to the pros for advice. In fact, when I was developing these classes originally, my main reference was the Java API docs (
http://java.sun.com/j2se/1.4.1/docs/api/). Even if you don't know Java (I don't), they're really informative. Of course Java != PHP, but there's a lot to learn by seeing how they do things in Java. Check out the java.sql package, in particular the Connection, PreparedStatement, and ResultSet interfaces. The classes I presented above are basically simplfied versions of them. I refer to those docs all time when I am working on my classes.
A really great book for learning the foundations of OOP is the book I quoted above, "The Object-Oriented Thought Process" by Matt Weisfeld (ISBN: 0672318539). It's only about 200 pages so it's concise, but very helpful. There's very little code -- it's all about the concepts. I highly recommend it.
Another fantastic book, especially if you're trying to make the transition from procedural to OO, is Refactoring by Martin Fowler. It's considered a classic (and has been mentioned on these forums many times).
Well, this post is a tad longer then I thought it would be when I started. Hope it all makes sense and helps you.
-geoff
Originally Posted by shad0w
(((( Sorry, i parsed another file-header. Really, this is InvisionBoard class for MySQL database, but i have my own! database classes for PgSQL, MSSQL and mSQL for IBforums, which wasn't included in original IBforums package, 'cause i don't have MySQL on my hosting-server. Sorry for codesample - i had no intention to arrogate to myself somebody else's code, it's just an accidental mistake
Posting Permissions
Bookmarks