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
1
2
3
4
5
6
7
8
9
| 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.
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
| 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; } } |
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.
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
| 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.
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
| 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; } } } |
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
| 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; } |
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.
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
| 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; } } } |
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.
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
| 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; } } |
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
| 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 ]; } } } |
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
| $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 .= ',' ; } } |
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
| 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; } } |
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.
1
2
3
4
5
6
7
8
| <?php; include ( 'crud.php' ); $db = new Database(); $db ->connect(); $db ->select( 'mysqlcrud' ); $res = $db ->getResult(); print_r( $res ); ?> |
Likewise we can go a step further and run an update query, and then output the results:
1
2
3
4
5
6
| <?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 ); ?> |
Now for a simple insert statement:
1
2
3
4
5
| ;<?php; $db ->insert( 'mysqlcrud' , array (3, "Name 4" , "this@wasinsert.ed" )); $res = $db ->getResult(); print_r( $res ); ?> |
No comments:
Post a Comment