Quite a few examples from robots to bicycles have been supplied as “straightforward” explanations of what OOP is. I’ve opted to point out you the way OOP works with a real-life instance, for a programmer. By making a MySQL CRUD class you may simply create, learn, replace and delete entries in any of your tasks, no matter how the database is designed.
Organising the skeleton of our class is pretty easy as soon as we determine precisely what we’d like. First we have to be sure that we are able to do our primary MySQL capabilities. With the intention to do that, we’d like the next capabilities:
- Choose
- Insert
- Delete
- Replace
- Join
- Disconnect
These appear fairly primary, however I’m certain that as we undergo, we’ll discover that quite a lot of them make the most of some related elements, so we could must create extra courses. Here’s what your class definition ought to appear like. Discover that I made certain that the strategies have been created with the general public key phrase.
1 |
class Database |
2 |
{
|
3 |
public perform join() { } |
4 |
public perform disconnect() { } |
5 |
public perform choose() { } |
6 |
public perform insert() { } |
7 |
public perform delete() { } |
8 |
public perform replace() { } |
9 |
}
|
perform join()
This perform will likely be pretty primary, however creating it is going to require us to first create just a few variables. Since we need to be sure that they will’t be accessed from exterior our class, we will likely be setting them as non-public. These variables will likely be used to retailer the host, username, password and database for the connection. Since they’ll just about stay fixed all through, we don’t even must create modifier or accessor strategies for it. After that, we’d simply must create a easy mysql assertion to hook up with the database. In fact, since as programmers we at all times must assume the person (even whether it is us) will do one thing silly, lets add an additional layer of precaution. We are able to examine if the person has truly linked to the database first, and if they’ve, there actually isn’t a must re-connect. In the event that they haven’t then we are able to use their credentials to attach.
1 |
non-public db_host = ‘’; |
2 |
non-public db_user = ‘’; |
3 |
non-public db_pass = ‘’; |
4 |
non-public db_name = ‘’; |
5 |
|
6 |
public perform join() |
7 |
{
|
8 |
if(!$this->con) |
9 |
{
|
10 |
$myconn = @mysql_connect($this->db_host,$this->db_user,$this->db_pass); |
11 |
if($myconn) |
12 |
{
|
13 |
$seldb = @mysql_select_db($this->db_name,$myconn); |
14 |
if($seldb) |
15 |
{
|
16 |
$this->con = true; |
17 |
return true; |
18 |
} else |
19 |
{
|
20 |
return false; |
21 |
}
|
22 |
} else |
23 |
{
|
24 |
return false; |
25 |
}
|
26 |
} else |
27 |
{
|
28 |
return true; |
29 |
}
|
30 |
}
|
As you may see, it makes use of some primary mysql capabilities and a little bit of error checking to be sure that issues are going in keeping with plan. If it connects to the database efficiently it is going to return true, and if not, it is going to return false. As an added bonus it is going to additionally set the connection variable to true if the connection was efficiently full.
public perform disconnect()
This perform will merely examine our connection variable to see whether it is set to true. Whether it is, that signifies that it’s linked to the database, and our script will disconnect and return true. If not, then there actually isn’t a must do something in any respect.
1 |
public perform disconnect() |
2 |
{
|
3 |
if($this->con) |
4 |
{
|
5 |
if(@mysql_close()) |
6 |
{
|
7 |
$this->con = false; |
8 |
return true; |
9 |
}
|
10 |
else
|
11 |
{
|
12 |
return false; |
13 |
}
|
14 |
}
|
15 |
}
|
public perform choose()
That is the primary perform the place issues start to get a bit of sophisticated. Now we will likely be coping with person arguments and returning the outcomes correctly. Since we don’t essentially need to have the ability to use the outcomes instantly we’re additionally going to introduce a brand new variable referred to as outcome, which is able to retailer the outcomes correctly. Aside from that we’re additionally going to create a brand new perform that checks to see if a selected desk exists within the database. Since all of our CRUD operations would require this, it makes extra sense to create it individually reasonably than integrating it into the perform. On this approach, we’ll save area in our code and as such, we’ll have the ability to higher optimize issues in a while. Earlier than we go into the precise choose assertion, right here is the tableExists perform and the non-public outcomes variable.
1 |
non-public $outcome = array(); |
2 |
|
3 |
non-public perform tableExists($desk) |
4 |
{
|
5 |
$tablesInDb = @mysql_query('SHOW TABLES FROM '.$this->db_name.' LIKE "'.$desk.'"'); |
6 |
if($tablesInDb) |
7 |
{
|
8 |
if(mysql_num_rows($tablesInDb)==1) |
9 |
{
|
10 |
return true; |
11 |
}
|
12 |
else
|
13 |
{
|
14 |
return false; |
15 |
}
|
16 |
}
|
17 |
}
|
This perform merely checks the database to see if the required desk already exists. If it does it is going to return true and if not, it is going to return false.
1 |
public perform choose($desk, $rows = '*', $the place = null, $order = null) |
2 |
{
|
3 |
$q = 'SELECT '.$rows.' FROM '.$desk; |
4 |
if($the place != null) |
5 |
$q .= ' WHERE '.$the place; |
6 |
if($order != null) |
7 |
$q .= ' ORDER BY '.$order; |
8 |
if($this->tableExists($desk)) |
9 |
{
|
10 |
$question = @mysql_query($q); |
11 |
if($question) |
12 |
{
|
13 |
$this->numResults = mysql_num_rows($question); |
14 |
for($i = 0; $i < $this->numResults; $i++) |
15 |
{
|
16 |
$r = mysql_fetch_array($question); |
17 |
$key = array_keys($r); |
18 |
for($x = 0; $x < rely($key); $x++) |
19 |
{
|
20 |
// Sanitizes keys so solely alphavalues are allowed
|
21 |
if(!is_int($key[$x])) |
22 |
{
|
23 |
if(mysql_num_rows($question) > 1) |
24 |
$this->outcome[$i][$key[$x]] = $r[$key[$x]]; |
25 |
else if(mysql_num_rows($question) < 1) |
26 |
$this->outcome = null; |
27 |
else
|
28 |
$this->outcome[$key[$x]] = $r[$key[$x]]; |
29 |
}
|
30 |
}
|
31 |
}
|
32 |
return true; |
33 |
}
|
34 |
else
|
35 |
{
|
36 |
return false; |
37 |
}
|
38 |
}
|
39 |
else
|
40 |
return false; |
41 |
}
|
Whereas it does appear a bit of scary at first look, this perform actually does an entire bunch of issues. First off it accepts 4 arguments, 1 of which is required. The desk title is the one factor that you’ll want to go to the perform with a view to get outcomes again. Nonetheless, if you wish to customise it a bit extra, you are able to do so by including which rows will likely be pulled from the database, and you’ll even add a the place and order clause. In fact, so long as you go the primary worth, the outcome will default to their preset ones, so that you don’t have to fret about setting all of them. The little bit of code proper after the arguments simply serves to compile all our arguments right into a choose assertion. As soon as that’s performed ,a examine is finished to see if the desk exists, utilizing our prior tableExists perform. If it exists, then the perform continues onwards and the question is carried out. If not, it is going to fail.
The following part is the true magic of the code. What it does is collect the columns and knowledge that was requested from the database. It then assigns it to our outcome variable. Nonetheless, to make it simpler for the top person, as an alternative of auto-incrementing numeric keys, the names of the columns are used. In case you get a couple of outcome every row that’s returned is saved with a two dimensional array, with the primary key being numerical and auto-incrementing, and the second key being the title of the column. If just one result’s returned, then a one dimensional array is created with the keys being the columns. If no outcomes are turned then the outcome variable is ready to null. As I mentioned earlier, it appears a bit complicated, however when you break issues down into their particular person sections, you may see that they’re pretty easy and simple.
public perform insert()
This perform is so much easier than our prior one. It merely permits us to insert info into the database. As such we would require a further argument to the title of the desk. We would require a variable that corresponds to the values we want to enter. We are able to merely separate every worth with a comma. Then, all we have to do is shortly examine to see if our tableExists, after which construct the insert assertion by manipulating our arguments to type an insert assertion. Then we simply run our question.
1 |
public perform insert($desk,$values,$rows = null) |
2 |
{
|
3 |
if($this->tableExists($desk)) |
4 |
{
|
5 |
$insert = 'INSERT INTO '.$desk; |
6 |
if($rows != null) |
7 |
{
|
8 |
$insert .= ' ('.$rows.')'; |
9 |
}
|
10 |
|
11 |
for($i = 0; $i < rely($values); $i++) |
12 |
{
|
13 |
if(is_string($values[$i])) |
14 |
$values[$i] = '"'.$values[$i].'"'; |
15 |
}
|
16 |
$values = implode(',',$values); |
17 |
$insert .= ' VALUES ('.$values.')'; |
18 |
$ins = @mysql_query($insert); |
19 |
if($ins) |
20 |
{
|
21 |
return true; |
22 |
}
|
23 |
else
|
24 |
{
|
25 |
return false; |
26 |
}
|
27 |
}
|
28 |
}
|
As you may see, this perform is so much easier than our reasonably complicated choose assertion. Our delete perform will truly be even easier.
public perform delete()
This perform merely deletes both a desk or a row from our database. As such we should go the desk title and an non-obligatory the place clause. The the place clause will tell us if we have to delete a row or the entire desk. If the the place clause is handed, that signifies that entries that match will should be deleted. After we determine all that out, it’s only a matter of compiling our delete assertion and working the question.
1 |
public perform delete($desk,$the place = null) |
2 |
{
|
3 |
if($this->tableExists($desk)) |
4 |
{
|
5 |
if($the place == null) |
6 |
{
|
7 |
$delete = 'DELETE '.$desk; |
8 |
}
|
9 |
else
|
10 |
{
|
11 |
$delete = 'DELETE FROM '.$desk.' WHERE '.$the place; |
12 |
}
|
13 |
$del = @mysql_query($delete); |
14 |
|
15 |
if($del) |
16 |
{
|
17 |
return true; |
18 |
}
|
19 |
else
|
20 |
{
|
21 |
return false; |
22 |
}
|
23 |
}
|
24 |
else
|
25 |
{
|
26 |
return false; |
27 |
}
|
28 |
}
|
And at last we get to our final main perform. This perform merely serves to replace a row within the database with some new info. Nonetheless, due to the marginally extra complicated nature of it, it is going to come off as a bit bigger and infinitely extra complicated. By no means concern, it follows a lot of the identical sample of our earlier perform. First it is going to use our arguments to create an replace assertion. It’s going to then proceed to examine the database to be sure that the tableExists. If it exists, it is going to merely replace the suitable row. The exhausting half, after all, comes once we try to create the replace assertion. Because the replace assertion has guidelines for a number of entry updating (IE – totally different columns in the identical row through the crafty use of comma’s), we might want to take that under consideration and create a strategy to cope with it. I’ve opted to go the the place clause as a single array. The primary ingredient within the array would be the title of the column being up to date, and the following would be the worth of the column. On this approach, each even quantity (together with 0) would be the column title, and each odd quantity would be the new worth. The code for performing that is quite simple, and is offered under exterior the perform:
1 |
for($i = 0; $i < rely($the place); $i++) |
2 |
{
|
3 |
if($i%2 != 0) |
4 |
{
|
5 |
if(is_string($the place[$i])) |
6 |
{
|
7 |
if(($i+1) != null) |
8 |
$the place[$i] = '"'.$the place[$i].'" AND '; |
9 |
else
|
10 |
$the place[$i] = '"'.$the place[$i].'"'; |
11 |
}
|
12 |
else
|
13 |
{
|
14 |
if(($i+1) != null) |
15 |
$the place[$i] = $the place[$i]. ' AND '; |
16 |
else
|
17 |
$the place[$i] = $the place[$i]; |
18 |
}
|
19 |
}
|
20 |
}
|
The following part will create the a part of the replace assertion that offers with truly setting the variables. Since you may change any variety of values, I opted to go together with an array the place the hot button is the column and the worth is the brand new worth of the column. This manner we are able to even do a examine to see what number of totally different values have been handed to be up to date and may add comma’s appropriately.
1 |
$keys = array_keys($rows); |
2 |
for($i = 0; $i < rely($rows); $i++) |
3 |
{
|
4 |
if(is_string($rows[$keys[$i]])) |
5 |
{
|
6 |
$replace .= $keys[$i].'="'.$rows[$keys[$i]].'"'; |
7 |
}
|
8 |
else
|
9 |
{
|
10 |
$replace .= $keys[$i].'='.$rows[$keys[$i]]; |
11 |
}
|
12 |
// Parse so as to add commas
|
13 |
if($i != rely($rows)-1) |
14 |
{
|
15 |
$replace .= ','; |
16 |
}
|
17 |
}
|
Now that we’ve acquired these two bits of logic out of the way in which, the remainder of the replace assertion is simple. Right here it’s offered under:
1 |
public perform replace($desk,$rows,$the place) |
2 |
{
|
3 |
if($this->tableExists($desk)) |
4 |
{
|
5 |
// Parse the the place values
|
6 |
// even values (together with 0) comprise the the place rows
|
7 |
// odd values comprise the clauses for the row
|
8 |
for($i = 0; $i < rely($the place); $i++) |
9 |
{
|
10 |
if($i%2 != 0) |
11 |
{
|
12 |
if(is_string($the place[$i])) |
13 |
{
|
14 |
if(($i+1) != null) |
15 |
$the place[$i] = '"'.$the place[$i].'" AND '; |
16 |
else
|
17 |
$the place[$i] = '"'.$the place[$i].'"'; |
18 |
}
|
19 |
}
|
20 |
}
|
21 |
$the place = implode('=',$the place); |
22 |
|
23 |
|
24 |
$replace = 'UPDATE '.$desk.' SET '; |
25 |
$keys = array_keys($rows); |
26 |
for($i = 0; $i < rely($rows); $i++) |
27 |
{
|
28 |
if(is_string($rows[$keys[$i]])) |
29 |
{
|
30 |
$replace .= $keys[$i].'="'.$rows[$keys[$i]].'"'; |
31 |
}
|
32 |
else
|
33 |
{
|
34 |
$replace .= $keys[$i].'='.$rows[$keys[$i]]; |
35 |
}
|
36 |
|
37 |
// Parse so as to add commas
|
38 |
if($i != rely($rows)-1) |
39 |
{
|
40 |
$replace .= ','; |
41 |
}
|
42 |
}
|
43 |
$replace .= ' WHERE '.$the place; |
44 |
$question = @mysql_query($replace); |
45 |
if($question) |
46 |
{
|
47 |
return true; |
48 |
}
|
49 |
else
|
50 |
{
|
51 |
return false; |
52 |
}
|
53 |
}
|
54 |
else
|
55 |
{
|
56 |
return false; |
57 |
}
|
58 |
}
|
Now that we’ve got that we’ve completed our final perform, our easy CRUD interface for MySQL is full. Now you can create new entries, learn particular entries from the database, replace entries and delete issues. Additionally, be creating and reusing this class you’ll discover that you’re saving your self quite a lot of time and coding. Ah, the fantastic thing about object oriented programming.
The Use
So we have our class all made, however how will we use it? This half is straightforward. Lets begin by making a quite simple system database to make use of in our testing. I created a database referred to as take a look at, after which ran the MySQL statment. You may place it in any database that you simply like, simply just be sure you change the connection variables on the high of the script to match:
The primary line is commented out just because not everybody will want it. If you’ll want to run that greater than as soon as, you’ll need to uncomment it the second time to make sure that it creates the desk.
Now that our desk is created and populated, it is time to run just a few easy queries on it.
1 |
<?php; |
2 |
embrace('crud.php'); |
3 |
$db = new Database(); |
4 |
$db->join(); |
5 |
$db->choose('mysqlcrud'); |
6 |
$res = $db->getResult(); |
7 |
print_r($res); |
8 |
?>
|
If performed appropriately, you need to see the next:
Likewise we are able to go a step additional and run an replace question, after which output the outcomes:
1 |
<?php; |
2 |
$db->replace('mysqlcrud',array('title'=>'Modified!'),array('id',1)); |
3 |
$db->replace('mysqlcrud',array('title'=>'Changed2!'),array('id',2)); |
4 |
$res = $db->getResult(); |
5 |
print_r($res); |
6 |
?>
|
We must always see this
Now for a easy insert assertion:
1 |
;<?php; |
2 |
$db->insert('mysqlcrud',array(3,"Identify 4","this@wasinsert.ed")); |
3 |
$res = $db->getResult(); |
4 |
print_r($res); |
5 |
?>
|
