Numerous examples from robots to bicycles have been offered as “easy” explanations of what OOP is. I’ve opted to show you how OOP works with a real-life example, for a programmer. By creating a MySQL CRUD class you can easily create, read, update and delete entries in any of your projects, regardless of how the database is designed.
Setting up the skeleton of our class is fairly simple once we figure out exactly what we need. First we need to make sure that we can do our basic MySQL functions. In order to do this, we need the following functions:
- Select
- Insert
- Delete
- Update
- Connect
- Disconnect
Those seem pretty basic, but I’m sure that as we go through, we’ll notice that a lot of them utilize some similar aspects, so we may have to create more classes. Here is what your class definition should look like. Notice that I made sure that the methods were created with the public keyword.
class Database
{
public function connect() { }
public function disconnect() { }
public function select() { }
public function insert() { }
public function delete() { }
public function update() { }
}
function connect()
This function will be fairly basic, but creating it will require us to first create a few variables. Since we want to make sure that they can’t be accessed from outside our class, we will be setting them as private. These variables will be used to store the host, username, password and database for the connection. Since they will pretty much remain constant throughout, we don’t even need to create modifier or accessor methods for it. After that, we’d just need to create a simple mysql statement to connect to the database. Of course, since as programmers we always have to assume the user (even if it is us) will do something stupid, lets add an extra layer of precaution. We can check if the user has actually connected to the database first, and if they have, there really isn’t a need to re-connect. If they haven’t then we can use their credentials to connect.
private db_host = ‘’;
private db_user = ‘’;
private db_pass = ‘’;
private db_name = ‘’;
public function connect()
{
if(!$this->con)
{
$myconn = @mysql_connect($this->db_host,$this->db_user,$this->db_pass);
if($myconn)
{
$seldb = @mysql_select_db($this->db_name,$myconn);
if($seldb)
{
$this->con = true;
return true;
} else
{
return false;
}
} else
{
return false;
}
} else
{
return true;
}
}
As you can see, it makes use of some basic mysql functions and a bit of error checking to make sure that things are going according to plan. If it connects to the database successfully it will return true, and if not, it will return false. As an added bonus it will also set the connection variable to true if the connection was successfully complete.
public function disconnect()
This function will simply check our connection variable to see if it is set to true. If it is, that means that it is connected to the database, and our script will disconnect and return true. If not, then there really isn’t a need to do anything at all.
public function disconnect()
{
if($this->con)
{
if(@mysql_close())
{
$this->con = false;
return true;
}
else
{
return false;
}
}
}
public function select()
This is the first function where things begin to get a little complicated. Now we will be dealing with user arguments and returning the results properly. Since we don’t necessarily want to be able to use the results right away we’re also going to introduce a new variable called result, which will store the results properly. Apart from that we’re also going to create a new function that checks to see if a particular table exists in the database. Since all of our CRUD operations will require this, it makes more sense to create it separately rather than integrating it into the function. In this way, we’ll save space in our code and as such, we’ll be able to better optimize things later on. Before we go into the actual select statement, here is the tableExists function and the private results variable.
private $result = array();
private function tableExists($table)
{
$tablesInDb = @mysql_query('SHOW TABLES FROM '.$this->db_name.' LIKE "'.$table.'"');
if($tablesInDb)
{
if(mysql_num_rows($tablesInDb)==1)
{
return true;
}
else
{
return false;
}
}
}
This function simply checks the database to see if the required table already exists. If it does it will return true and if not, it will return false.
public function select($table, $rows = '*', $where = null, $order = null)
{
$q = 'SELECT '.$rows.' FROM '.$table;
if($where != null)
$q .= ' WHERE '.$where;
if($order != null)
$q .= ' ORDER BY '.$order;
if($this->tableExists($table))
{
$query = @mysql_query($q);
if($query)
{
$this->numResults = mysql_num_rows($query);
for($i = 0; $i < $this->numResults; $i++)
{
$r = mysql_fetch_array($query);
$key = array_keys($r);
for($x = 0; $x < count($key); $x++)
{
// Sanitizes keys so only alphavalues are allowed
if(!is_int($key[$x]))
{
if(mysql_num_rows($query) > 1)
$this->result[$i][$key[$x]] = $r[$key[$x]];
else if(mysql_num_rows($query) < 1)
$this->result = null;
else
$this->result[$key[$x]] = $r[$key[$x]];
}
}
}
return true;
}
else
{
return false;
}
}
else
return false;
}
While it does seem a little scary at first glance, this function really does a whole bunch of things. First off it accepts 4 arguments, 1 of which is required. The table name is the only thing that you need to pass to the function in order to get results back. However, if you want to customize it a bit more, you can do so by adding which rows will be pulled from the database, and you can even add a where and order clause. Of course, as long as you pass the first value, the result will default to their preset ones, so you don’t have to worry about setting all of them. The bit of code right after the arguments just serves to compile all our arguments into a select statement. Once that is done ,a check is done to see if the table exists, using our prior tableExists function. If it exists, then the function continues onwards and the query is performed. If not, it will fail.
The next section is the real magic of the code. What it does is gather the columns and data that was requested from the database. It then assigns it to our result variable. However, to make it easier for the end user, instead of auto-incrementing numeric keys, the names of the columns are used. In case you get more than one result each row that is returned is stored with a two dimensional array, with the first key being numerical and auto-incrementing, and the second key being the name of the column. If only one result is returned, then a one dimensional array is created with the keys being the columns. If no results are turned then the result variable is set to null. As I said earlier, it seems a bit confusing, but once you break things down into their individual sections, you can see that they are fairly simple and straightforward.
public function insert()
This function is a lot simpler than our prior one. It simply allows us to insert information into the database. As such we will require an additional argument to the name of the table. We will require a variable that corresponds to the values we wish to input. We can simply separate each value with a comma. Then, all we need to do is quickly check to see if our tableExists, and then build the insert statement by manipulating our arguments to form an insert statement. Then we just run our query.
public function insert($table,$values,$rows = null)
{
if($this->tableExists($table))
{
$insert = 'INSERT INTO '.$table;
if($rows != null)
{
$insert .= ' ('.$rows.')';
}
for($i = 0; $i < count($values); $i++)
{
if(is_string($values[$i]))
$values[$i] = '"'.$values[$i].'"';
}
$values = implode(',',$values);
$insert .= ' VALUES ('.$values.')';
$ins = @mysql_query($insert);
if($ins)
{
return true;
}
else
{
return false;
}
}
}
As you can see, this function is a lot simpler than our rather complex select statement. Our delete function will actually be even simpler.
public function delete()
This function simply deletes either a table or a row from our database. As such we must pass the table name and an optional where clause. The where clause will let us know if we need to delete a row or the whole table. If the where clause is passed, that means that entries that match will need to be deleted. After we figure all that out, it’s just a matter of compiling our delete statement and running the query.
public function delete($table,$where = null)
{
if($this->tableExists($table))
{
if($where == null)
{
$delete = 'DELETE '.$table;
}
else
{
$delete = 'DELETE FROM '.$table.' WHERE '.$where;
}
$del = @mysql_query($delete);
if($del)
{
return true;
}
else
{
return false;
}
}
else
{
return false;
}
}
And finally we get to our last major function. This function simply serves to update a row in the database with some new information. However, because of the slightly more complex nature of it, it will come off as a bit larger and infinitely more confusing. Never fear, it follows much of the same pattern of our previous function. First it will use our arguments to create an update statement. It will then proceed to check the database to make sure that the tableExists. If it exists, it will simply update the appropriate row. The hard part, of course, comes when we try and create the update statement. Since the update statement has rules for multiple entry updating (IE – different columns in the same row via the cunning use of comma’s), we will need to take that into account and create a way to deal with it. I have opted to pass the where clause as a single array. The first element in the array will be the name of the column being updated, and the next will be the value of the column. In this way, every even number (including 0) will be the column name, and every odd number will be the new value. The code for performing this is very simple, and is presented below outside the function:
for($i = 0; $i < count($where); $i++)
{
if($i%2 != 0)
{
if(is_string($where[$i]))
{
if(($i+1) != null)
$where[$i] = '"'.$where[$i].'" AND ';
else
$where[$i] = '"'.$where[$i].'"';
}
else
{
if(($i+1) != null)
$where[$i] = $where[$i]. ' AND ';
else
$where[$i] = $where[$i];
}
}
}
The next section will create the part of the update statement that deals with actually setting the variables. Since you can change any number of values, I opted to go with an array where the key is the column and the value is the new value of the column. This way we can even do a check to see how many different values were passed to be updated and can add comma’s appropriately.
$keys = array_keys($rows);
for($i = 0; $i < count($rows); $i++)
{
if(is_string($rows[$keys[$i]]))
{
$update .= $keys[$i].'="'.$rows[$keys[$i]].'"';
}
else
{
$update .= $keys[$i].'='.$rows[$keys[$i]];
}
// Parse to add commas
if($i != count($rows)-1)
{
$update .= ',';
}
}
Now that we’ve got those two bits of logic out of the way, the rest of the update statement is easy. Here it is presented below:
public function update($table,$rows,$where)
{
if($this->tableExists($table))
{
// Parse the where values
// even values (including 0) contain the where rows
// odd values contain the clauses for the row
for($i = 0; $i < count($where); $i++)
{
if($i%2 != 0)
{
if(is_string($where[$i]))
{
if(($i+1) != null)
$where[$i] = '"'.$where[$i].'" AND ';
else
$where[$i] = '"'.$where[$i].'"';
}
}
}
$where = implode('=',$where);
$update = 'UPDATE '.$table.' SET ';
$keys = array_keys($rows);
for($i = 0; $i < count($rows); $i++)
{
if(is_string($rows[$keys[$i]]))
{
$update .= $keys[$i].'="'.$rows[$keys[$i]].'"';
}
else
{
$update .= $keys[$i].'='.$rows[$keys[$i]];
}
// Parse to add commas
if($i != count($rows)-1)
{
$update .= ',';
}
}
$update .= ' WHERE '.$where;
$query = @mysql_query($update);
if($query)
{
return true;
}
else
{
return false;
}
}
else
{
return false;
}
}
Now that we have that we’ve finished our last function, our simple CRUD interface for MySQL is complete. You can now create new entries, read specific entries from the database, update entries and delete things. Also, be creating and reusing this class you’ll find that you are saving yourself a lot of time and coding. Ah, the beauty of object oriented programming.
The Use
So we've got our class all made, but how do we use it? This part is simple. Lets start by creating a very simple system database to use in our testing. I created a database called test, and then ran the MySQL statment. You can place it in any database that you like, just make sure that you change the connection variables at the top of the script to match:
The first line is commented out simply because not everyone will need it. If you need to run that more than once, you will need to uncomment it the second time to ensure that it creates the table.
Now that our table is created and populated, it's time to run a few simple queries on it.
<?php;
include('crud.php');
$db = new Database();
$db->connect();
$db->select('mysqlcrud');
$res = $db->getResult();
print_r($res);
?>
If done correctly, you should see the following:
Likewise we can go a step further and run an update query, and then output the results:
<?php;
$db->update('mysqlcrud',array('name'=>'Changed!'),array('id',1));
$db->update('mysqlcrud',array('name'=>'Changed2!'),array('id',2));
$res = $db->getResult();
print_r($res);
?>
We should see this
Now for a simple insert statement:
;<?php;
$db->insert('mysqlcrud',array(3,"Name 4","this@wasinsert.ed"));
$res = $db->getResult();
print_r($res);
?>
Enjoyed this Post?
Subscribe to our RSS Feed, Follow us on Twitter or simply recommend us to friends and colleagues!
By Angelo Rodrigues
Getting Good with JavaScript
Learn how to liven up your webpages, from start to finish. Veteran Nettuts+ author Andrew Burgess helps you Get Good with JavaScript.
Join our newsletter!
UberMenu - WordPress Mega Me ... only $16.00 
RoyalSlider – Touch-Enable ... only $12.00 
Kult House - More
Web Basix
Just getting started? Start with our basix tutorials on the fundamentals. View All Basix Tutorials
Create a Tutorial, Get Paid!
Have something to teach the world? Want to earn money doing it? Tutorials, screencasts, and articles published here on Nettuts+ are largely contributed by readers just like you! We'll pay you great money for good content. Find Out More
Super php class! Thx for sharing!
Your tutorial is brilliant but when I come returning the method GetResult(), turns out it is not specified anywhere.
Did I miss it or is it something I should know how to add myself?
Great tut but couldn’t you just simplify parts of the code? Such as
if($ins)
{
return true;
}
else
{
return false;
}
to if($ins) return true;
return false;
what about return $ins;
A better solution would be to use typecasting.
return (bool) $ins;
Hi
great post dude..very usefull..
can any one please tell me how to write function for join query….
THANKS
where is GetResult() method from this class?
For anyone confused about the GetResult() function, you need to add this inside the class:
public function GetResult(){
return $this->result;
}
This is because $result is private inside the class, therefore you need a function inside the class that can grab the $result and return it. You can then call the GetResult() function outside of the class to access the private $result array.
$db->select(‘nameoftable’);
$results = $db->GetResult();
print_r($results);
Alternatively, just change:
private $result = array();
to
public $result = array();
and call
$db->select(‘nameoftable’);
print_r($db->result());
$db->select(‘nameoftable’);
print_r($db->result());
should be:
$db->select(‘nameoftable’);
print_r($db->result);
I apologize to the author (Angelo) if this sounds harsh, but I’m very disappointed with the quality of this tutorial, and fear that it’s teaching people very bad coding habits. The idea of making CRUD functions (create, retrieve, update, delete) for a database class is completely flawed.
At first glance a CRUD database class makes your code look cleaner and easier to write, but you’ve obviously never handled complex queries before; or you’re just assuming that your readers are too ignorant to ever use advanced queries. You’ve left no room for the possibility for someone to even use a simple JOIN statement in their queries, which proves this class is completely useless.
You also left out a simple escape function that would make strings safe to use in queries without the risk of an SQL injection attack.
Here’s the structure I would use to build a Database class… The ideal Database class would be called as a singleton instance, so its usage could easily be extended across multiple classes and functions. And it would employ the following functions and constructor:
* private function __construct
* public static function getInstance($database)
* public function connect (you could call this function manually, or make it a private function and call it during the __construct)
* public function disconnect (not a must, but always nice to have)
* public function escape (simple use of mysql_escape_string, but ensures we’re connected to mysql first)
* public function query (yes, forget the CRUD functions entirely!)
* public function fetchFirst (calls query and returns first row as array)
* public function fetchArray (calls query and returns the entire result one row at a time)
* public function storeArray (calls query and returns the entire result as an array)
* private function error (simple error reporting)
A database class with the above structure would be simple, powerful, flexible, and easy to implement into any project.
In this case, the user still has to write select, insert, update etc queries manually in their code which is not the best practice of oops
Well NO!
because: Normaly you extend you MySQL-Class and have some public methods like GetUserList() which has the query predefined.
So basicly you got a extended class of you mysql class which has a lot of functions for getting, inserting and updating your data …
help needed,how can i select only a specific column data instead of all .thank you.
@Nites
in the second parameter you put your column name.
$db = new Database();
$db->connect();
$db->select(“table”,”columnname”,”whatever=’$whatever’”);
$res = $db->getResult();
$rows = count($res);
awsome !! 2 thumbsup ,,,, thank’s, this class is very useful…
i have problem when i call this function
$db = new Database();
$db->select(‘mytable_name1′,’*',”aktif =’1′”,’aid DESC LIMIT 1, 7′);
$resA = $db1->getResult();
$rowsA=count($resA);
echo $rowsA; // this show 7 record
then i select another table at the bottom of code above
$db->select(‘mytable_name1′,’*',”aktif =’1′”,’aid DESC LIMIT 1, 2′);
$resB = $db1->getResult();
$rowsB=count($resB);
echo $rowsB; // this still show 7 record, it should show 2 record
can you give me solution to solve this problem ?
yeah, same problem overhere
Insert this instead of the GetResult function, for it, to clear the results between querys.
public function ClearResult()
{
$this->result = null;
}
public function GetResult()
{
return $this->result;
ClearResult();
}
connect();
$db->select(‘testdb’, ‘*’, ‘title=”sdf”‘, ‘null’);
$res = $db->getResult();
//print_r($res[1]);
foreach ($res as $v1) {
foreach ($v1 as $v2) {
echo “$v2″;
}
echo “”;
}
?>
Great Demo.
Thanks for showing us this.
Fantastic info.thank you very much! good luck!
Sorry, but this i really poor OOP. I totally agree with Zorro. This is very bad practise.
I understand that you want to give your readers a possibilty to use PHP to create SQL query’s, but how about this?
SELECT u.username, a.article_id, l.language_id FROM articles AS a LEFT JOIN (users AS u INNER JOIN languages AS l ON l.user_id=u.user_id) ON a.user_id=a.user_id WHERE a.verified=1 AND u.active=1 AND u.banned!=1
How the hell would you ever create this query? Well, it is possible, if you would create a better function. But we are talking about OOP. And OOP is designed to create scalable and dynamic applications.
So teach these people something about using OOP for creating User classes, with Guests and Admins. That’s real life, and also where it’s made for.
David
You write one then…
Actually, I would quite like to see this. I am a novice to PHP and keen for good examples to follow.
David and Zorro,
I think the author did a great job to present the basic comments. You are both correct that there is a better way. I would like to see your analysis and implementations. They would make follow-on tutorials. I am eager to go to the next step.
write one that has industry standards. and we will all be glad.
Waw…………… Very Very Good Tutorial OOP……. thank you so much…….
Thanks for the help!
I had trouble getting the update to work in this example.
The parameter of “$where” didn’t supply a condition.
Got it to work like so. Hope this helps.
public function update($table,$rows,$where,$condition)
{
$where = implode($condition,$where);
}
$db->update(‘users’,array(‘userid’=>’Bernard!’),array(‘id’,3),”=”);
$db->update(‘users’,array(‘userid’=>’Santa!’),array(‘id’,4),”=”);
$db->select(‘users’);
I fixed updated method .. and here is the code
public function update($table,$rows,$where)
{
if($this->tableExists($table))
{
// Parse the where values
// even values (including 0) contain the where rows
// odd values contain the clauses for the row
for($i = 0; $i < count($where); $i++)
{
if($i%2 != 0)
{
if(is_string($where[$i]))
{
if($where[($i+1)] != null)
$where[$i] = "='".$where[$i]."' AND ";
else
$where[$i] = "='".$where[$i]."'";
}
}
}
$where = implode('',$where);
$update = 'UPDATE '.$table.' SET ';
$keys = array_keys($rows);
for($i = 0; $i $newPassword);
$where = array(‘email’,$email,’password’,$password);
$updated = $db->update(‘users’,$rows,$where);
Hello,
Is there more you give the above code?
I can’t work the code.
if you have working code, please share with me.
Please help me.
Thank you.
It seems that the ‘disconnect’ method is not included in the complete class available for download at the beginning of the article.
Ok, so I’m looking at this and newbie to the whole OOP I’m staring myself blind on $this->con. Where is “con”. I can’t find it anywere. Is this a magic-bullet thing?
Super post! Thanks for sharing this
Your code very easy to read. This is call readeable. Most people suggest to short the code but I think they can edit it as they want. first thing make as simple code for all type PHP programmer.
Thanks alot :)
I think its nice too, and thank you for those who posted the $condition thing in update. Now the update works :)
There are lot of errors in this code i have a better one with me, if u guys want i can share that
One thing i have noticed that would be useful is that you call the tableExists function when whilst executing a update, delete or insert function call. The use of this procedure would be useful on the select function too :)
Just a thought as its very easy to implement and adds an extra bit of valiation.
Oh wheres my manors! Great post too :)
Hello
great post dude..very usefull..
But if i want select two table How can i do ?
Thanx for this tutorial! Allthough some people allready stated some issues about handling complex mysql queries, the shown class is a good fondation for adjustments and improvements.
Thanx!
Thanks for the tutorial…its really a best one ever dealt with…
I get this error…
Notice: Undefined property: Database::$con in C:\wamp\www\bronxtiger\inc\class.database.inc.php on line 12
for this line of code
if(!$this-<con)
in the connect function
I have this error too. I thing it is a missing property
What is the query to select distinct from database?
Hi,
I have this query that i want to transfirm over to the OOP structure that you have explained:
$getErrorsToday = mysql_query(“SELECT * FROM roberrors WHERE date = ‘$today’”);
How to i transfirm this into your stucture? I’ve tryed and failed…
Mine is
$db->select(‘*’, ‘table’,”id=’$id’”);
$rs=$db->recordset();
@OLE
$db->select(“roberrors where date=’$today’”);
$getErrorsToday = $db->getResult();
I was looking for a php oop for example, update, delete, insert, and select the information that I have a problem with it, rendering it slow.
Ajax + PHP + MySQL + OOP I have an easy to understand, help me to assist me.
Thanks for the advice everyone.
Very nice class!
I was wondering where and what should I put to receive utf8 text from databases without it being changed to error characters? (character “Ā” shows up like “?”)
Thank you, already!
hi, beautiful coding
but i can’t use the update,insert and delete method
somebody help me plisss :(
I am new to oop and I am greatfull for any examples. I know people say there is a quicker shorter way of doing things but as a novice when they are written in full it makes it easier for us to follow as most literature on php tends to give you the full versions.
Anyway I have picked out the bits I wanted to include in my own project and changes it to suit my need. A problem I keep finding is that I can not get ‘mysql_insert_id’ to work. I do have multiple databases and these are selected in the query and the within the last id query as ‘mysql_insert_id(database2). I need the ID to insert information into other tables from the same database.
Does anyone have a work around for this or can explain why this would fail.
Thankyou
Ignore my last question. Pure stupidity was the cause of the failure. I was calling a connection twice from two different places. Not a mistake I will make again hopefully. “You live and learn or your not living!” as they say.
Hello,
First of all, I wanna say thank you for all this great subject.
Then, i want to inform you that the exposed source code contains errors. I’m surprised that the source code written on this page is correct, but the downloadable class contains errors. I tested them. Please fix it ASAP.
Thanks again.
Why this class does not use mysqli, the improved connection of PHP5? I read it is better to use mysqli, but I haven’t seen any PHP classes using that function. It is getting difficult to run mysqli_query() function, which requires two parameters. First one is database connection and second query. Any lights on this? Thanks in advance..
I am also having UTF8 problems, one of my tables has the world’s second-level political administrative divisions. So, I have a lot of this: Portugal [province] => A�ores. Need some advice, set_names doesn’t work, and everything else is in UTF8.
i think the solution is:
on the 39th line of the code is:
$myconn = @mysql_connect($this->db_host,$this->db_user,$this->db_pass);
insert a line after this line and put:
mysql_set_charset(‘utf8′,$myconn);
awesome code! this helps a lot!
really nice
This is not OOP. Nor is it good. Please, for the love of god, don’t ever do anything like this in real code.
Talk is cheap, show me the code!
Excellent code but update is not working, can you please help me why update function is not working?
I changed the where parsing block in update and am not having many issues see if its works for you ..
// Parse the where values
// even values (including 0) contain the where rows
// odd values contain the clauses for the row
$maxConds = count($where) ;
if ($maxConds%2 == 0){
for($i = 0; $i < $maxConds ; $i++)
{
if($i%2 != 0)
{
if(is_string($where[$i]))
{
if($i<$maxConds-1)
$where[$i] = '"'.$where[$i].'" AND ';
else
$where[$i] = '"'.$where[$i].'"';
}
}
}
$where = implode('',$where);
}
Update.
I add the else condition block
it’s work
$maxConds = count($where) ;
if ($maxConds%2 == 0){
for($i = 0; $i < $maxConds ; $i++)
{ echo $i." ";
if($i%2 != 0)
{
if(is_string($where[$i]))//if value is string
{
if($iupdate(‘posts’,array(‘name’=>’Changed3′,’content’=>’contentChange’),array(‘name’,'Changed2′)))
echo “success”;
else echo “false”;
sorry at above answer :(
here’s code
it’s work
$maxConds = count($where) ;
if ($maxConds%2 == 0){
for($i = 0; $i < $maxConds ; $i++)
{ echo $i." ";
if($i%2 != 0)
{
if(is_string($where[$i]))//if value is string
{
if($i<$maxConds-1)
$where[$i] = '"'.$where[$i].'" AND ';
else
$where[$i] = '"'.$where[$i].'"';
}
}
else
{
$where[$i] = $where[$i].'=';
}
}
$where = implode('',$where);
}
Thank you for your code and the code solved the problem of update BUT why did you check the string i just didn’t get. It killed 30 minutes of my life. :-( The problem was in multy condition and both the condition was int. Yet thank you for the help again.
Nice work thanks for this it will help me in my project thanks .