Browse All Articles > Free Tool for Abstracting MySQL Databases in PHP: db2class
Ever wanted to deal with a database as a class? Ever wished you could just load up a line out of database by telling some object "Go get this."?
Traditionally, in order to get information out of a database, you have to construct the SQL statement all by yourself, and go through the debugging process to get the query to work. Then, when it gets done and returned, you have to deal with the results set.
The process is ridiculously cumbersome to program, and I for one am lazy.
How lazy am I? I will spend 27 hours programming a code generator so that I can be lazy for the rest of my life. That laziness has brought forth a wonderful code generation tool called db2class.
db2class is a database table abstraction tool, which creates a class based on the database name and all the columns in the database.
So what's the benefit?
Using db2class will save you programming time, and it will allow you to handle the data in databases as an object. More importantly, they allow you to update your code after a database structure change by simply running the script again!
Setting Up db2class
Running db2class
db2class is meant to be run from the command line. No worries. It's easy.
Linux:
1. Log into the shell.
2. Give the script execution permissions by executing 'chmod 777 db2class' (without the single quotes).
3. Run the script by typing './db2class' (again without the quotes) in the directory where the script resides.
Windows:
1. Download, and unzip.
2. Save to your php directory (i.e., C:\PHP5)
3. Open the command prompt by clicking Start > Run, and typing 'cmd' (without the single quotes) and hitting enter.
4. Change to the php directory by typing 'cd\php5' (again without the quotes).
5. Run the script by typing php db2class
Once the script runs, it will tell you "xxxx class created, have a nice day."
At that point you can open up "My Computer" and browse to the C:\php5 folder. In it, you will find your newly created class. Copy it to your website folder that you are working with (e.g., in Adobe DreamWeaver), and you'll be good to go.
Connecting to MySQL
db2class requires that your MySQL configuration and connection information be stored in mysql.xml:
Don't worry. You don't have to create this file by hand. The first time you run db2class, it will look for the file, and when it cannot find it, it will prompt you to set it up by running this command:
Once you have run this command, it is set-up and you won't need to run it again unless your database configuration changes.
NOTE: If you use db2class in a publicly visible directory, the mysql.xml file will also be publicly visible! It is best to run db2class in an area outside of the DocumentRoot and then copy the resulting class to wherever the class is being used.
Our Example
For the examples below, we are going to work with a fictional database table named "people" that has the following structure:
Understanding How db2class Generates the Class Name and Properties
The Class Name and the File Name
db2class uses the name of the table to create the file name and the name of the class. This is done so that even with complex tables, it is easy to understand what class refers to what table.
So, our example table will create a class called "people" and be saved into a file, "people.class.php".
Class Properties
Classes generated with db2class will have a list of properties that that match the column names. This way, you can set the value of any column by addressing its corresponding property in the class.
So, the value of "people_firstname" is stored in $x->people_firstname, when $x is an instantiated class of "people".
So, let's say we wanted to save John Smith's information in our database. All we have to do is create a people class, set the values of the properties, and run the insert_me() method of the class to save it to the database:
Understanding Generated Class Methods
db2class generates 7 methods:
load_me()
Usage: Used from within scripts.
Purpose: to load a record from the database when the unique id is known.
Syntax: $p->load_me($id);
Return Value: true or false depending on if the query could be completed or not.
Generated Code:
execute_query($sql)
Usage: Not intended to be used within scripts, but publicly available.
Purpose: Executes a query inside the class.
Return Value: true or false depending on if the query could be completed or not.
Generated Code:
commit_suicide()
Usage: Used from within scripts.
Purpose: Removes a record from the database.
Syntax: $p->commit_suicide();
Return Value: true or false depending on if the query could be completed or not.
Generated Code:
update_me()
Usage: Used from within scripts.
Purpose: Update a record in the database
Syntax: $p->update_me();
Return Value: true or false depending on if the query could be completed or not.
Generated Code:
insert_me()
Usage: Used from within scripts.
Purpose: Update a record in the database
Syntax: $p->update_me();
Return Value: true or false depending on if the query could be completed or not.
Generated Code:
escape_me()
Usage: Not used from within scripts.
Purpose: Escape a string for storage in the database.
unescape_me()
Usage: Not used from within scripts.
Purpose: Escape a string for storage in the database.
Using db2class Generated Classes
Let's say we have a few people to remove out of our database. We know their IDs because we received the from a POST operation on a previous page that had a form where you could select multiple people to delete.
Here's what that code would look like:
Using Polymorphism with db2class
As a best practice, I recommend creating the base class and then extending that class to add functionality.
This practice holds one HUGE benefit: you can change / update your mysql databases and tables at will, and not have to worry about updating your code.
To illustrate my point, let's say that we have or original table and class. To that class, we've added a method that does a lookup of people by their email address (something else that can be considered unique).
Later, after adding the lookup by email method to the class, you decide that the people table needs to hold phone numbers as well. What do you do? If you regenerate the class using db2class, it will overwrite your new table. If you don't regenerate the class, you have to hand code everything, and that could take hours.
The solution is to use Polymorphism and the --parent switch.
When you create a class using db2class, you should always use the following syntax:
Technically, the --parent argument is optional, but when included, it creates a new file called people.class.parent.php instead of people.class.php.
With the parent class created, you can now create a new class that extends the people class. We'll call this new class person for our example.
The example code above just uses the person class rather than the people class for two reasons. First, it gives you the ability to upgrade people.class.parent.php using db2class without having to worry about the custom code encapsulated in people.class.php, and secondly, it allows you to conceptualize the logic a little better. For example, it makes better English sense to say "For each person in x, do this" on a for loop than it does to say "for each people in x".
Conclusion
The db2class table abstraction tool is extremely useful for those programmers who wish to be able to programmatically control the data in their databases in an object oriented fashion. Using db2class, you can maintain cleaner code with a much more efficient code base.
If you have any problems using db2class, just drop a comment below.
How to Get db2Class
The most current and up to date version of db2class is available at the db2class repository.
Traditionally, in order to get information out of a database, you have to construct the SQL statement all by yourself, and go through the debugging process to get the query to work. Then, when it gets done and returned, you have to deal with the results set.
The process is ridiculously cumbersome to program, and I for one am lazy.
How lazy am I? I will spend 27 hours programming a code generator so that I can be lazy for the rest of my life. That laziness has brought forth a wonderful code generation tool called db2class.
db2class is a database table abstraction tool, which creates a class based on the database name and all the columns in the database.
So what's the benefit?
Using db2class will save you programming time, and it will allow you to handle the data in databases as an object. More importantly, they allow you to update your code after a database structure change by simply running the script again!
Setting Up db2class
Running db2class
db2class is meant to be run from the command line. No worries. It's easy.
Linux:
1. Log into the shell.
2. Give the script execution permissions by executing 'chmod 777 db2class' (without the single quotes).
3. Run the script by typing './db2class' (again without the quotes) in the directory where the script resides.
Windows:
1. Download, and unzip.
2. Save to your php directory (i.e., C:\PHP5)
3. Open the command prompt by clicking Start > Run, and typing 'cmd' (without the single quotes) and hitting enter.
4. Change to the php directory by typing 'cd\php5' (again without the quotes).
5. Run the script by typing php db2class
Once the script runs, it will tell you "xxxx class created, have a nice day."
At that point you can open up "My Computer" and browse to the C:\php5 folder. In it, you will find your newly created class. Copy it to your website folder that you are working with (e.g., in Adobe DreamWeaver), and you'll be good to go.
Connecting to MySQL
db2class requires that your MySQL configuration and connection information be stored in mysql.xml:
<?xml version="1.0"?>
<mysql>
<user>user</user>
<pass>shazbot</pass>
<server>localhost</server>
</mysql>
Don't worry. You don't have to create this file by hand. The first time you run db2class, it will look for the file, and when it cannot find it, it will prompt you to set it up by running this command:
db2class --mysql --user=<user> --pass=<pass> --server=<server>
Once you have run this command, it is set-up and you won't need to run it again unless your database configuration changes.
NOTE: If you use db2class in a publicly visible directory, the mysql.xml file will also be publicly visible! It is best to run db2class in an area outside of the DocumentRoot and then copy the resulting class to wherever the class is being used.
Our Example
For the examples below, we are going to work with a fictional database table named "people" that has the following structure:
CREATE TABLE IF NOT EXISTS `people` (
`people_id` int(11) unsigned NOT NULL auto_increment,
`people_firstname` int(11) NOT NULL,
`people_lastname` int(11) NOT NULL,
`people_email` int(11) NOT NULL,
PRIMARY KEY (`people_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
Understanding How db2class Generates the Class Name and Properties
The Class Name and the File Name
db2class uses the name of the table to create the file name and the name of the class. This is done so that even with complex tables, it is easy to understand what class refers to what table.
So, our example table will create a class called "people" and be saved into a file, "people.class.php".
Class Properties
Classes generated with db2class will have a list of properties that that match the column names. This way, you can set the value of any column by addressing its corresponding property in the class.
So, the value of "people_firstname" is stored in $x->people_firstname, when $x is an instantiated class of "people".
So, let's say we wanted to save John Smith's information in our database. All we have to do is create a people class, set the values of the properties, and run the insert_me() method of the class to save it to the database:
$p = new people();
$p->people_firstname = "John";
$p->people_lastname = "Smith";
$p->people_email = "jsmith@someplace.com";
$p->insert_me();
Understanding Generated Class Methods
db2class generates 7 methods:
load_me()
execute_query($sql)
commit_suicide()
update_me()
insert_me()
escape_me()
unescape_me()
load_me()
Usage: Used from within scripts.
Purpose: to load a record from the database when the unique id is known.
Syntax: $p->load_me($id);
Return Value: true or false depending on if the query could be completed or not.
Generated Code:
$sql = "SELECT * FROM `people` WHERE `people_id` = '$this->people_id'";
$result = mysql_query($sql);
if($result != FALSE && mysql_num_rows($result)>0)
{
$row = mysql_fetch_assoc($result);
$this->people_id = $row['people_id'];
$this->people_firstname = $row['people_firstname'];
$this->people_lastname = $row['people_lastname'];
$this->people_email = $row['people_email'];
}
$this->unescape_me();
execute_query($sql)
Usage: Not intended to be used within scripts, but publicly available.
Purpose: Executes a query inside the class.
Return Value: true or false depending on if the query could be completed or not.
Generated Code:
function execute_query($sql)
{
if($result = mysql_query($sql))
{
return true;
}
else
{
return false;
}
}
commit_suicide()
Usage: Used from within scripts.
Purpose: Removes a record from the database.
Syntax: $p->commit_suicide();
Return Value: true or false depending on if the query could be completed or not.
Generated Code:
function commit_suicide()
{
$sql = "DELETE FROM `people` WHERE `people_id` = '$this->people_id'";
return $this->execute_query($sql);
}
update_me()
Usage: Used from within scripts.
Purpose: Update a record in the database
Syntax: $p->update_me();
Return Value: true or false depending on if the query could be completed or not.
Generated Code:
function update_me()
{
$this->escape_me();
$sql = "UPDATE `people`
SET `people_id` = '$this->people_id'
,`people_firstname` = '$this->people_firstname'
,`people_lastname` = '$this->people_lastname'
,`people_email` = '$this->people_email'
WHERE `people_id` = '$this->people_id' LIMIT 1";
return $this->execute_query($sql);
}
insert_me()
Usage: Used from within scripts.
Purpose: Update a record in the database
Syntax: $p->update_me();
Return Value: true or false depending on if the query could be completed or not.
Generated Code:
function insert_me()
{
$this->escape_me();
$sql = "INSERT INTO `people` (
`people_id`,`people_firstname`,`people_lastname`,`people_email`)
VALUES ('".$this->people_id."','".$this->people_firstname."',
'".$this->people_lastname."','".$this->people_email."')";
return $this->execute_query($sql);
}
escape_me()
Usage: Not used from within scripts.
Purpose: Escape a string for storage in the database.
unescape_me()
Usage: Not used from within scripts.
Purpose: Escape a string for storage in the database.
Using db2class Generated Classes
Let's say we have a few people to remove out of our database. We know their IDs because we received the from a POST operation on a previous page that had a form where you could select multiple people to delete.
Here's what that code would look like:
$ids = $_POST['ids'];
for($x=0;$x<sizeof($ids);$x++)
{
$x = new classID() //Created class from db2class
$x->people_id = $ids['$x'];
$x->load_me();
if(!$x->commit_suicide())
echo "$x->people_firstname could not be deleted! They just won't go away! :-(";
}
Using Polymorphism with db2class
As a best practice, I recommend creating the base class and then extending that class to add functionality.
This practice holds one HUGE benefit: you can change / update your mysql databases and tables at will, and not have to worry about updating your code.
To illustrate my point, let's say that we have or original table and class. To that class, we've added a method that does a lookup of people by their email address (something else that can be considered unique).
Later, after adding the lookup by email method to the class, you decide that the people table needs to hold phone numbers as well. What do you do? If you regenerate the class using db2class, it will overwrite your new table. If you don't regenerate the class, you have to hand code everything, and that could take hours.
The solution is to use Polymorphism and the --parent switch.
When you create a class using db2class, you should always use the following syntax:
db2class <database> <table> --parent
Technically, the --parent argument is optional, but when included, it creates a new file called people.class.parent.php instead of people.class.php.
With the parent class created, you can now create a new class that extends the people class. We'll call this new class person for our example.
<?php
class person extends people
{
//Do nothing. We haven't extended it yet.
}
?>
The example code above just uses the person class rather than the people class for two reasons. First, it gives you the ability to upgrade people.class.parent.php using db2class without having to worry about the custom code encapsulated in people.class.php, and secondly, it allows you to conceptualize the logic a little better. For example, it makes better English sense to say "For each person in x, do this" on a for loop than it does to say "for each people in x".
Conclusion
The db2class table abstraction tool is extremely useful for those programmers who wish to be able to programmatically control the data in their databases in an object oriented fashion. Using db2class, you can maintain cleaner code with a much more efficient code base.
If you have any problems using db2class, just drop a comment below.
How to Get db2Class
The most current and up to date version of db2class is available at the db2class repository.
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (0)