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
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:
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:
<?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
;<?php;
$db->insert('mysqlcrud',array(3,"Name 4","this@wasinsert.ed"));
$res = $db->getResult();
print_r($res);
?>
Related Posts
Check out some more great tutorials and articles that you might like
Plus Members
Source Files, Bonus Tutorials and
More for $9 a month for all TUTS+
sites in one subscription.
User Comments
( ADD YOURS )Yoz November 26th
a lot of unimportant code like:
# if(mysql_num_rows($tablesInDb)==1)
# {
# return true;
# }
# else
# {
# return false;
# }
equals to:
# return mysql_num_rows($tablesInDb)==1;
or
# 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;
# }
# }
is the same as:
( )# public function connect()
# {
# if($this->con) return true
# $myconn = @mysql_connect($this->db_host,$this->db_user,$this->db_pass);
# if(!$myconn) return false;
# $seldb = @mysql_select_db($this->db_name,$myconn);
# if(!$seldb) return false;
# $this->con = true;
# return true;
# }
salman November 26th
great job
( )nice article and well explained
wish this was part of php framework series
Jhay November 26th
Great! Very informative
( )e303 November 26th
This is the first post regarding PHP development that makes sense so far.
Building new PHP framework from scratch is quite pointless. It is like reinventing the wheel.
( )Roshan Bhattarai November 26th
very useful……..nice work as always…..
( )Rydgel November 26th
Great tut !
( )chris simpson November 26th
great work.
( )Anton Lindqvist November 26th
Good job!
If you want to take the select() function a bit further you could put all your rows in a array and then add something like this to the function.
if(is_array($rows)) $rows = implode(’,', $rows);
( )Chris November 26th
The tutorial is interesting, but you forgot to consider on sql injection. I my opinion a notice would be helpfull.
( )Shane November 26th
When I first took a look at PHP a while ago, I was a little shocked at the functional approach of much of the code I saw. Sure, it works, but structuring things in an object-oriented manner is much more modern with myriad benefits.
It’s nice to see an overview of a useful PHP class. Thanks for posting.
( )Esteban November 26th
I was looking for this tutorial. Thanks!!
( )Bram Van der Sype November 26th
Nice post, well structured, but there is a LOT of unnecessary code. The connect function for example:
# 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;
# }
# }
# }
# return false;
# }
Works just as well…
Also:
# $myconn = @mysql_connect($this->db_host,$this->db_user,$this->db_pass);
# if($myconn)
The use of @ is quite “wrong” here. It also slows the code down a lot.
( )A much better way would be:
# if(($myconn = mysql_connect($this->db_host,$this->db_user,$this->db_pass)) !== FALSE)
Code_Bleu November 26th
Where is the getResult() function?
( )Lamin November 26th
Your code is very clean and readable even though PHP tends to be very ugly sometimes. Great good.
( )Deepak November 26th
Nicely written and structures article.. after all you get to see what PHP and MySql can be used for in Real World Applications
( )endo November 26th
It’s called active records, and exists in every php framework. But good work $)
( )insicdesigns November 26th
Nice job as always in Nettuts.
( )Rui Silva November 26th
I think the use of ORM’s like doctrine and propel it’s a better idea. But, nice tutorial for beginners learn how to use MVC.
( )Scott November 26th
Would you not use mysqli?
( )Nick Shepherd November 26th
I have to remind myself that this really isn’t a “programmer’s” website. With this said there is a lot of unnecessary code in this class. With PHP 5 being the now defacto php version on any server you really should be implementing Exception Handling. This would also eliminate your extra tableExists() query for every single query you run with the class. Although this isn’t a breakable performance thing with small amounts of queries and on a small scale but as soon as any sizeable amount of traffic hits a server those extra queries stack up on load time.
Most mysql errors that are returned can be predicted and custom messages and/or error correction can be handled very easily with simple Exception Handling.
Anyways, it’s a good start for a simple explanation for beginners. Just pointing out that there is a lot more optimized and better way for handling this type of feature in a software solution. Especially since 9 times out of 10 the interface between the application and the mysql server is the bottleneck in most applications.
( )Ibrahim Abid November 26th
Wow great tips thank’s dude
( )Scott November 26th
sticking ‘@’ in front of every db call is a bad idea
( )zy November 26th
Interesting tutorial.
Something rather unrelated to this but, what is the PHP editor used in the screenshots? It looks like Aptana but I know it isn’t =\
( )Angelo R. November 26th
@Bram – There definitely is a lot of unncessary code, but when writing I had to choose between short and sweet or trying to explain as many different ideas as possible. I opted to throw in extra bits, hopefully showing people a little more than they expected. Also, I structured certain statements the long way, so that people using it could modify it easily. Perhaps they have a custom error class that they use. This way they can clearly see whats happening.
@Nick – This is definitely not the most optimized version of this. My hope is that users reading this hopefully understand how certain things work, and will then attempt to go about making it better. You never learn more than when you do something for yourself, and mucking with other people’s code is a skill any programmer should have.
@Zy – The editor being used is NetBeans, it offers the most recent version with a PHP plugin (version 6.5).
@Everyone else – Thanks everyone, hopefully you all learned a few things
( )WebDevVote November 26th
You’re voted !!!
( )Track back from http://webdevvote.com
merigee November 26th
nice…………….
( )Renkai November 26th
I like it. Thanks Angelo!
( )Neil November 26th
I would LOVE to see a tutorial like this for ASP.net (vb) 2.0!!
( )Joe Casabona November 26th
Nice tut! Though my database class includes one more function to prevent SQL injections.
( )Mukarram November 26th
Hii…can anybody tell me wat happend to http://psdtuts.com – it’s not showing any content on website….Is it hacked ????
( )Reader November 26th
Is interesting and well explain, very good for starters….. it is very insecure though. This will not prevent SQL Injection or malicious users from dropping tables for example….. plus all these methods and properties (if something like this wanted to be done) should be made protected if not private!…..
Also instead of creating a whole class for the database connection, it would be better to use the pre installed (PHP 5) MYSQLI class…. has all the connections and is more powerful and secure. “I”, stands for improved.
Good job though, like i said…. it explains some basic concepts for starters.
( )weblizzer November 26th
great tutorial,
for getting the the values i prefer to use while loop
$rowResult = array();
while($row = mysql_fetch_assoc($result)){
$rowResult[] = $row;
}
return $rowResult;
////// to work onit
once you call the function and get the result
all you need to display is to used via foreach function
( )Mukarram November 26th
now it’s working….
( )btw i ws getting only these entries ” Hello world ” written by collis.
Mike rice November 26th
Very nice and simple tutorial for the beginners!
( )Thanks!
Jeffrey Way November 26th
@Mukarram – It was just a database problem. It’s back up now!
( )Bret November 26th
good tut in the methodolgy of creating OO Database methods. I agree that why revinvent the wheel – at this point just use a framework like Cake or Ruby but it still is good to have a general handle of how these frameworks operate under the hood…
( )Amr Tamimi November 26th
really great .. really easy tips.. for beginners
( )Thanks!
AB November 26th
Be nice if you covered the more recent additions of PHP OOP instead of making another cliche site. If you do something for a noob, he’ll leech off of you day after day. If you teach a noob, he’ll still leech off of you. If you pin him down, beat the crap out of him, and tell him to do it himself, he’ll finally learn.
( )Guillaume November 26th
thx !
( )Steven November 26th
Here’s another way to optimize this class…
( )Currently you query the database every time to make sure the table exists. This is not efficient because it adds an extra query to every other query (create, retrieve, update, delete) you make. Instead, there should be a __construct() method which gets all the tables and stores them in an array. Then change the tableExists() method to look within that array. This eliminates all those extra queries, but retains the functionality.
Steven November 26th
You should also add a $limit parameter to the select() method
( )Miles Johnson November 26th
Like yoz said, you have way too many if statements that could be shortened dramatically.
Also what if they have complicated WHERE clauses like.
WHERE id = 1 AND username != ‘user’ AND age > 20
( )Stuart November 26th
Since each time you call the Database class you also call connect() I’d suggest putting the database connection stuff in the class constructor. That way it’s automatically handled on class instantiation saving code and increasing efficiency.
( )Sebastian Perez November 26th
hey , dude , some issue on insert method that from a simple form , when check if the value is_string() , skips …
Check this ,also im checking how to fix this ..
( )Mark November 26th
There seems to be quite a few example of bad and unnecessary coding practices described in this article.
The very first comment talks about using the double equal in
if(mysql_num_rows() == 1)
why not just do
return (bool) mysql_num_rows(resource); ?
for($x = 0; $x < count($key); $x++)
that count($key) function is going to be called on every iteration. You could fix this a few ways
for($x = 0, $count = count($key); $x < $count; $x++)
Whats the point of this code here? You are returning both the column_names as keys and indexes
$r = mysql_fetch_array($query);
$key = array_keys($r);
I could go on, as this wouldnt be the way that I would have done things. But thanks for posting the tut.
why not use mysql_ fetch_ assoc ?
( )Adam November 26th
You should do a tutorial on how to write a database abstraction layer, because people should get use to using one instead of writing direct connections to just MYSQL.
( )Permana Jayanta November 26th
This is great tutorial. Now I understand more about PHP database class. Is there PHP-MySQL database class roundup ? I would like to see how people code their PHP-MySQL database class
( )Greg M November 26th
@Shane, I think you must have meant “procedural” when you wrote “functional. Functional code in PHP is very rare because the language doesn’t support it well, although if you do go the effort you can still reap many of the benefits of the approach.
( )Reader November 26th
Great work, love to see the same for mysqli
( )Anonymous November 26th
This tutorial teaches fundamental flaws, and useless queries. Like checking if the table exists every time a query is made. If it’s not it will spit out an error regardless, so another query wasted.
I hate to sound blunt, but the people who get hired to write tutorials here seem like they are just learning but come off thinking they’re experts.
( )Swapnil Sarwe November 26th
I was using self made Database class for my framework. but this is much better and flexible. this sould surely find place in my custom framework.
( )Nice job. thanx
Ignas November 26th
nice one
( )Mr. Magic November 27th
Protip: use PDO, Zend_Db, Doctrine or Propel. No need to code crappy custom db classes.
( )abhijit November 27th
Mr. Magic is right. Also the mysql PHP extension is not recommended for mysql versions newer than 4.1.3, you should use the mysqli extension instead. The tutorial is nice for learning OOP, not for learning a good DB access class.
( )britalian November 27th
How do you call the insert function where the first database column is an auto incremented ID field?
( )Abdo November 27th
Thanks
( )britalian November 27th
No worries, got it!
( )Legion Dave November 27th
Save some time… for basic scaffold use
http://www.phpscaffold.com/
( )Ange Chierchia November 27th
Great tut!
I haven’t developed with OOP aspects of PHP but I must say that tut is a good introduction, and a good practice example.
Thanks
( )Jordan Payne November 27th
Nice tutorial
Need to work on my OOP skills a bit more and this was a good help 
( )Melvin Walls November 27th
Great tutorial definitely a read ill pass around.
( )devsmt November 27th
returning a mute array form select() instead of a business object lowers the power of that idea…and makes the implementation extremely easy.
( )it also lacks the full power of sql(grouping, join, calculations) and that also makes it trivial to implement, but very similar to the php4 procedural approach.
MstPhp November 27th
Pfff, crappy quality. You should read some refactor tutorials first…
( )bijl November 27th
funny how negative comments are getting removed, dont forget people. this is major bad practise.
dont follow these guides
( )Can Berkol November 28th
Very useful tutorial for beginners; however for serious projects keep in mind that this example is not secure. Especially a few more precautions must be taken to prevent SQL injections.
( )neron-fx November 28th
e303 said “Building new PHP framework from scratch is quite pointless. It is like reinventing the wheel.”
I beg to differ, I think you can learn a hell of a lot about how code works by developing your own framework from scratch. I basically learned OOP by sitting down and writing my own PHP framework from scratch! It was one of the most valuable learning experiences of my career so far! It taught me OOP, good programming practices and has made my life easier in the process.
With regards to the tutorial, yes it’s not resource efficent and has a few niggles and gripes in terms of ‘best programming practices’, but regardless I think this is a good well explained introduction to working with OOP and some of the basic syntax. Some of the commentors here should get of the guy’s back or produce something better themselves!
( )Christian November 28th
Excelent, I try the phpmaker its a great tool for CRUD.
( )woozy November 28th
Nice tutorial. I agree with Yoz though, some methods have better ways to build, using internal functionalities that PHP gives us.
( )m.a.yoosuf November 28th
flow is nice, but better to add a sample Crud operation
it will be helpful for learning ppl like me
( )zy November 29th
@Angelo R.
Thanks! Looks like I have another PHP IDE I can look into besides Dreamweaver and Aptana.
( )kareem November 30th
this is wonderful topic …. i will put acopy of this topic on
( )my site here
http://www.as7ap4you.com
Franke November 30th
I am very confused regarding the $result variable. For instance, you use the function update() to update the database, then you call getResults() to get the results, but the update() function never sets the $result variable in any way, it just:
( )$query = @mysql_query($update);
if($query)
{
return true;
}
else
{
return false;
}
Can someone explain this PHP voodoo?
Chris Gunther December 1st
Good basic tutorial, doesn’t really cover complex queries involving multiple tables or complex where clauses. I find your use of the $rows parameter to represent column names in your functions is misnamed in my opinion, $columns or $fields would have been a better name, but just a small gripe.
I never knew the i in mysqli meant improved. I’ll have to look into that for future projects.
( )SeismicMike December 4th
Great! I will probably be using the concepts from this in my projects from now on!
One comment though. In your select function you call mysql_fetch_array and then have an use is_int() to make sure you’re only getting alphanumeric keys. You can reduce this to only one line by using mysql_fetch_assoc() instead – it returns only alphanumeic keys. so inestead of :
array (
[0] => 1
[id] => 1
[1] => Bob
[name] => Bob
…
)
you get just:
array (
( )[id] => 1
[name] => Bob
….
)
Jim September 22nd
Well explain nice article on PHP and MYSQL good job…
( )SeismicMike December 4th
I think I’ve improved on your connect function a bit. I only use one return statement. I took out all of your returns and just put in a return $this->con; at the end of the function. That way if $this->con is true coming in, or if it’s false coming in and successful connection is made, it will return true (I’m sill setting $this->con = true; on successful DB selection). All other scenarios should return false.
( )Enrique December 4th
Cool Article, specially for small scripts for ocassions when a framework is too much…
( )Thanks !
Nick_Sol December 7th
Hi, my question is, in the insert function, how can i use it if the id of the table is auto_increment?
( )ruairi December 10th
Excellent tutorial! Too many PHP / OOP tutorials use abstract analogys..i’ve been waiting for this kind of thing, a straight forward real-world usage.
( )Floris December 12th
I have a question. If you get an array like this:
http://nettuts.s3.amazonaws.com/135_crud/images/update.png
Say I wanted to get the name value from the second block in the array, how would I go about doing that?
( )farhaan December 15th
Great start to OOP CRUD. Given there are so many comments about optimising the code. Could any of the readers post an optimised version?? This is a great opportunity to create something wonderful
( )Xobb December 18th
Nice tip for newbies that start with OOP.
( )Michael December 22nd
This is the first post regarding PHP development that makes sense so far.
Building new PHP framework from scratch is quite pointless. It is like reinventing the wheel.
You’re right, find another career, because its all been done.
Game over Internet.
The wheel is far from perfect.
( )Buffy December 24th
I have never come across a php framework that has exactly what i need. I don’t know but your clients must be quite lame. Anyway, greate tutorial, shows a lot of options.
( )K.Kane December 29th
Where do they get these guys from? I learn about better coding practice from the comments than I do from the articles. You guys need to do better screening. Blind leading the blind. Some articles are good, but not this one. It is sad when you get expert comments and not so expert articles. Its seems to be a trend sadly. I don’t like to compromise. Quality over quantity any day.
Thanks for the comments guys.
( )VImAl December 29th
good job BRO!!!!!!!!!!!
( )Ahad January 1st
@Kane valid comment, I totally agree with you.
I have never seen a simple CRUD Operation like this one. I also don’t work with php that often.
However in Rails, this would have taken not more than 10 minutes to set up the database, connect to it, add your tables (Active Record) and create your simple models and views for your CRUD actions….
Then its just rake:db migrate and you are all set…..
Girls/Guys at the end of the day its about keeping it simple, and only experience will show you the light.
No disrespect to the author intended here. We each have our personal opinions and technologies we prefer to work on.
( )Maarten January 8th
Thanks for this article, it really helps me understanding how to implement OOP with PHP.
And those commenters who say the code can is bad and can be improved; please post some examples of what, why and how it can be improved!
Thanks.
( )Bill January 8th
I fail to see the value in encapsulating MySQL in this fashion. If I wanted to build a select, I’d use mysql_query($sql) and be done with it. In this age, every programmer should be well versed in basic SQL, so creating a “framework” that merely permutates and restricts SQL’s syntax only results in frustration.
It is also absolutely terrible OOP in that it doesn’t actually represent an object. A far more appropriate abstraction of a database is the ActiveRecord pattern, where the object represents the actual data, not its storage mechanism.
I’ll go as far as saying that this tutorial is doing a disservice to its readers. If you learned something today, you learned it wrong. There is a world of science and time-tested practices behind programming, and they should be studied long before attempting a disciplined exercise like the design of a data structure and related interface(s). Making it “easy” only gives people more ways to get it wrong.
( )Araba Oyunları January 19th
Great work, I love this site.
( )Philips Tel January 25th
Very good.. I like your tutorial. It is sturctured and readable.
But, You forgot to write the display() function??
It didn’t work.
thx
( )JackTheRipper January 29th
The getResult is in the source you can download
public function getResult()
( ){
return $this->result;
}
enver January 31st
clean and simple example. thanks a lot.
( )kailas February 4th
nice work.i like this site.
( )Rob February 6th
I love commenters like YOZ.
As soon as someone else’s code is too hard to read you complain, yet when someone else’s code is space out with separate easy to follow lines you’re the first to cry n00b.
I was able to follow the code and it resemble how I write as well.
Great tuturial. If may add:
I you standard dize you db naming and tablie naming and most importantly field naming you can stream line the insert and edit functions by a large amount.
Here is my insertFromPost function let me know what you think:
field names have a prefix like user_login, user_pass, etc…
function insertFromPost( $table , $prefix )
{
foreach( $_POST as $k => $v )
{
if( ereg( “^”.$prefix , $k ) )
{
$fields .= $k . “, “;
$values .= “‘” . mysql_escape_string( $v ) . “‘, “;
}
}
$fields = ereg_replace( “, $” , “” , $fields );
$values = ereg_replace( “, $” , “” , $values );
$q = “INSERT INTO “.$table.” (”.$fields.”) VALUES (”.$values.”)”;
( )return $q;
}
Ronny February 7th
How well does this work with advanced SQL queries?
Or is it possible to extend the class to work with nesten queries?
( )Cody February 11th
Just out of curiosity, where is the variable ‘con’ used ($this->con) defined. Should it not be defined with the instance variables?
( )windir February 13th
Finally someone who have understood that the “bicycle” and “car” examples for explaining OOP are useless…
I might have overlooked it, but you don’t show what the getResult() looks like?
( )obSedrick February 15th
nice article .. found somewhat similiar article on
( )http://www.a-scripts.com/index.php/php-tutorials/46-brief-introduction-to-oop
Rocky February 17th
I really think the OO method should only extend the query,
$q = “SELECT * FROM `table`”;
$res = $db->query($q);
which would be a rewrite of a standard mysql query
class connection {
function connect(){
$c = mysql_connect($this->host, $this->username, $this->password);
mysql_select_db($this->database);
}
function query($string){
return mysql_query($string) or die(mysql_error());
}
}
$db = new connection;
// Database Object
// Database Logic
// Database Functions
// Any other functions you wish to re-right.
// We are basicly taking plain mysql functions and trading them for OOPs // CORE FUNCTIONALITY.
// We can also trigger our own errors and check if a connection is active within the class.
When connecting we will be passing our function something to use to connect to the database at the core value of each object we will be instating, a instance of that object.
We will then render our objects into a page after we include them into a file.
The class could extend fetching:
mysql_fetch_array($result);
mysql_fetch_assoc($result);
OR you could throw it as a object:
In which case u would use: mysql_fetch_object($result);
The hole idea of the class is to pass and control the data while obtaining, clean, practical code which will take care of all database logic.
( )Rocky February 17th
Another thing is that you will be able to instate pre-defined functions to clean / sort user-input or you can write your own working programs to filter and do checking.
- You could report errors within the query, you could report failed inserts into a database,
( )Dan Beland February 19th
Good stuff… the only thing that seems odd to me is how you refer to columns/fields in the database as ‘rows’. This could potentially be very confusing for beginners that don’t have a solid grasp on databases.
For example:
$q = ‘SELECT ‘.$rows.’ FROM ‘.$table;
This could just be a matter of opinion, but I think that should be
$q = ‘SELECT ‘.$fields.’ FROM ‘.$table;
Especially since PHP refers to it as fields in it’s functions:
# mysql_field_flags
# mysql_field_len
# mysql_field_name
etc…
Anyways, I’m just being picky. Good job!
( )php March 5th
why don’t we just use php adodb and be done with it. no need to recreate everything from scratch
( ).
Mike March 9th
I’m new to OO so I was working through this tutorial line by line and running it locally. In your connection section in the actual tutorial you have
private db_host ”;
private db_user ”;
private db_pass ”;
private db_name ”;
I typed this exactly and php gave me an error. Obviously I was frustrated, so I downloaded the .zip and noticed you had $ in front of each variable and I realized you must have left them out in the tutorial on accident. Hopefully this will help any other newbies with this tutorial.
( )Dani March 17th
I am trying to convince myself to some OOP in PHP, but …
What does the example in this tutorial give me for an advantage compared to procedural method? I only see a bunch functions nothing really special
It’s nicely explained (has some shortcomings when it comes to security but that should be covered in another tutorial) but do you really need go OO for this?
( )Hemadri March 18th
Hi
Thank you for sharing nice thought, I found two bugs in your code. In update function if we pass two parameter for while condition it appends.
In select function, if the database having only single row it shows some wrong output..
Can you please check and modify your code. Please email me your code so that I will test some more advanced…
Thanks
( )rajveer patel March 19th
very very great job
( )Dan March 22nd
Sorry but even for being an example there are far too much lines.
This not coding with extended layout for didactive purposes, this is taking detours.
Especially the update function is a real joke.
@Angelo : u might wanna read this http://be2.php.net/manual/en/control-structures.foreach.php and also using @ in front of every mysql function is bad.
( )Araba Oyunları March 24th
Thank you , very good, admin
( )Nayana Adassuriya March 26th
can any body explain me the purpose or mean of the code in update function line no 14
if(($i+1) != null)
i cant understand the real logic there
i guess it should be
if($where[$i+1] = null)
all php expert ,reply me soon
( )VIVEK PRASAD March 27th
It is a very nice tutorial for fresher php developer like me
( )T April 6th
Good tutorial for people new to PHP and OOP; however, you do not need separate functions for the CRUD [Create, Read, Update, & Delete] functionality when all you need is a simple Query function because sometimes you need more advanced queries than what can be created when working with in this type of limitation. A singleton design pattern also benefits DB classes as it allows you to use one connection across an entire framework and reduce overhead and increase performance. I agree with a few of the earlier replies about there being redundant code and useless additional code that could be refactored into more efficient code…
( )WebX April 12th
I think the whole premise behind writing a OOP class to an existing Mysql class is rather silly, dangerous and a good reason why PHP received the black eye that it has over the years concerning security holes.
Although the tutorial based an example for OOP programming in an Mysql environment, it completely rendered SQL injection out. The main issues that I have found is that there is no direct reference to standard (old and new) mysql functions to sanitize data.
Example_1 provides a little better way of going about an INSERT or UPDATE statement.
Example_1:
==============
$newNote = addslashes($newNote);
$newNote = htmlspecialchars($newNote);
mysql_real_escape_string(trim($newNote));
==============
I can now INSERT newNote without much worry as long as I also sanitized the data coming in from whatever $_REQUEST method was used in my form (IE. $_GET, $_POST, $_COOKIE…etc) using string length, intval, trim or regex expressions (etc…..) to make extra certain the data coming down to the mysql portion of my script is already cleansed for use.
Another trick for returning results (and one that was briefly mentioned above) is what Example_2 can provide for ease of use anywhere.
Example_2
================
function options()
{
global $products;
$query = ‘SELECT * FROM `category` LIMIT 0, 100′;
$get_data = mysql_query($query);
if (!$get_data || mysql_num_rows($get_data) stripslashes($t['id']), ‘name’ => stripslashes($t['name']));
}
return $products;
}
// call the above function
options();
//spit out the results
//could add more error checking here such as is_array() or a whole host of // other array checking functions
foreach ($products as $opt)
{
print (” {$opt['name']} \n”);
}
=================
In short, data sanitation is the biggest issue you will ever have to deal with concerning PHP and Mysql. You can create miles of code in OOP fashion and loose track of what exactly it is that supposedly saves you time and effort. Just compare the above OOP class to a standard Mysql function and return code set. All of the above “OOP code” is literally reinventing the wheel, presenting obvious holes for SQL injections and yet I can see that some had no issues snapping the code up and probably pasting it into their own scripts.
In conclusion, tutorials are nice. Examples are nice, however the one true tutorial all should be reading and learning from is coding PHP securely. Now, before anybody lambastes me for my own examples, just know that I am not going to provide an entire script to emphasize a point. nor spend half a night providing an example for somebody to skin the cat another way more “proficiently”. Each to their own, just do it securely.
( )WebX April 12th
Welp, that was what I was afraid of. Formatting of my PHP code examples are all screwed up and missing another variable return in the for loop, but oh well….no points for prettiness tonight I guess.
( )tom April 13th
thanks, grate
( )Luis Alfonso April 15th
wuaa esto es genial, lastima que me cueste un poco entender porq no hablo ingles!!!
( )Zoran April 18th
Thanks for this tutorial, it is not optimized, but it’s a good starting point for writing better CRUD class. All those who complain can simply just offer a better tutorial or be silent.
( )Von April 22nd
Thanks for your time and knowledge. Cool! highly recommended to every OOP newbie like me.
( )Mehdi Lahmam B. May 5th
Hi there,
a little correction for update() function.
Line 237 : if($where[$i+1] != null) instead of if($i+1 != null)
Line 238 : $where[$i] = ‘=”‘.$where[$i].’” AND ‘; //missing =
Thanks to nettuts and their contributors for that wonderful work
( )Billa Rajinikar May 6th
very godd tutorial
( )odel May 13th
hi! everyody… nice tutorial as always. a trade mark of nettuts?
i’m new to all these and just don’t know the difference between these two:
$query = @mysql_query($q) and $query = mysql_query($q)
i hope someone can clarify this matter for me.
tia
( )Yash May 30th
it jus avoid warning
( )Custom PHP July 12th
Interesting Tutorial. Thanks!
( )Hans-Kristian August 14th
It is NOT object-oriented to wrap functions in classes like this. A common misunderstanding.
( )jobish August 15th
Great ………….
Thank you dear
( )djazzc August 18th
There is a typo in your download code making the update function not working (correctly).
line 245:
$where = implode(”,$where);
should be:
$where = implode(’=',$where);
Apart from that thanks for a nice tutorial
( )viml August 26th
u are not considered the network traffic,
( )if ur db server in the another host you cannot get desired result
tahir karaca September 11th
hi,
thanks for class.
But results is multiplied in $db->getResults() expression for every query. example :
$db->select(’table1_tb’);
$arr1 = $db->getResult();
$db->select(’table2_tb’);
$arr2 = $db->getResult();
$db->select(’table3_tb’);
$arr3 = $db->getResult();
$arr3 is groving every query. But when only use $arr3 is more small value.
why?
( )Billa Rajinikar Reddy Ameerpet September 12th
Your article is very good
( )Sham Sehgal October 8th
Hey man, its a nice post and is informative too, thanks a ton for that…. but there are too much conditions u applied on this code.. I think we can make it a bit easier by removing some conditions and other variables…. otherwise its a good post and I really appreciate your hard work….
Thanks
( )Brennan Novak October 12th
This tutorial is terrible. The code in the tutorial is different than the code in the source files included. I’m sorry to be so negative but it’s just a bummer after going part by part writing the code trying to learn and then it not working. Then once trying with the source it works fine, they bug fixing to see where there are inconsistencies. Not sure why no body else has pointed this out….
( )Natalie Crowe October 13th
Could you please fix the problems with the source code and your tutorial and resubmit. Having major issues here particularly with the update function.
( )mike October 20th
Thank you so much for this tutorial. As a newbie to OOP I found easy to grasp what I think is a difficult area. There is lots on the web about OOP using php, but it’s so abstract and full of terms and definitions as to be confusing. There is nothing like a good, worked example. Well done, and thank you for oversimplifying it and leaving out such ‘essential’ topics such as ’security’ which were not within the scope of this tuturial.
( )