| Subcribe via RSS

PHP MySQL Class

May 1st, 2009 Posted in PHP

So it's probably pretty late in "the game" to be posting this, however I thought I might do it anyway just for those that may want to get started using OOP in PHP. I've been using this for a while and improving it wherever I can, then I started using CodeIgniter which ended up having a very similar class built into it. So if you are wanting to learn OOP, this might help you a long and it will make for a good transition into an MVC if you decide to move forward from there.

Keep in mind this has very simple functionality. For the most part, you can execute any style of query you want just by using the first example, however, there are a few extra methods built in that will allow you to easily do inserts and updates.

Call me crazy but I never really got around to adding a delete method in here, mainly because I RARELY delete data from a database, I usually update the row with a "delete date" in some sort of delete column. I can't imagine adding delete functionality would be very hard. If you decide to use this, tell me what you think!

PLAIN TEXT
PHP:
  1. <?php
  2.  
  3. /*
  4. **  written by brian dichiara
  5. **  last updated 11-01-2009
  6. **  http://www.briandichiara.com
  7. **  briandichiara@gmail.com
  8. */
  9.  
  10. define('HOSTNAME', 'localhost'); // database connection (usually localhost)
  11. define('DATABASE', 'some_db_name'); // name of database
  12. define('USERNAME', 'some_user_name'); // database username
  13. define('PASSWORD', 'some_password'); // database password
  14. define('T_PREFIX', ''); // table prefix
  15.  
  16. $db_con = mysql_pconnect(HOSTNAME, USERNAME, PASSWORD) or trigger_error(mysql_error(), E_USER_ERROR);
  17.  
  18. class mySQL {
  19.    
  20.     var $debug = false;
  21.     var $sql;
  22.     var $row;
  23.     var $cmd;
  24.     var $total;
  25.     var $id;
  26.     var $record = NULL;
  27.    
  28.     public $where = array();
  29.    
  30.     private $conn;
  31.     private $db;
  32.     private $is_select = false;
  33.     private $is_insert = false;
  34.     private $fetch_type = 'object';
  35.     private $pre_fetch = true;
  36.    
  37.    
  38.     // q=query, pre_fetch=pre fetch (initial execution for selects), fetch_type=fetch type
  39.     function mySQL($q='', $pre_fetch=true, $fetch_type='object', $db=DATABASE, $h=HOSTNAME, $u=USERNAME, $p=PASSWORD){
  40.         $this->sql = trim($q);
  41.         $this->db = $db;
  42.         $this->pre_fetch = (bool)$pre_fetch; // only necessary for selects
  43.         $this->fetch_type = strtolower($fetch_type);
  44.         $this->conn = mysql_pconnect($h, $u, $p) or trigger_error(mysql_error(), E_USER_ERROR);
  45.         if(substr(strtolower($this->sql),0,6) == 'select'){
  46.             $this->is_select = true;
  47.         }
  48.         if(!empty($this->sql)){
  49.             if(substr(strtolower($this->sql), 0, 6) == 'insert'){
  50.                 $this->is_insert = true;
  51.             }
  52.             $this->go();
  53.         }
  54.     }
  55.    
  56.     function increment(){
  57.         if($this->record === NULL){
  58.             $this->record = 0;
  59.         } else {
  60.             $this->record++;
  61.         }
  62.     }
  63.    
  64.     function seek($index){
  65.         if($this->total> 0){
  66.             if(mysql_data_seek($this->cmd, $index)){
  67.                 $this->record = $index;
  68.                 return true;
  69.             }
  70.         }
  71.         return false;
  72.     }
  73.    
  74.     function fetch(){
  75.         $this->increment();
  76.         return $this->row = mysql_fetch_object($this->cmd);
  77.     }
  78.    
  79.     function fetchAssoc(){
  80.         return $this->row = mysql_fetch_assoc($this->cmd);
  81.     }
  82.    
  83.     function fetchArray(){
  84.         return $this->row = mysql_fetch_array($this->cmd);
  85.     }
  86.    
  87.     function loop(){
  88.         if($this->record === NULL){
  89.             $this->increment();
  90.         } else {
  91.             $this->fetch();
  92.         }
  93.     }
  94.    
  95.     function loopArray(){
  96.         if($this->record === NULL){
  97.             $this->increment();
  98.         } else {
  99.             $this->fetchArray();
  100.         }
  101.     }
  102.    
  103.     function loopAssoc(){
  104.         if($this->record === NULL){
  105.             $this->increment();
  106.         } else {
  107.             $this->fetchAssoc();
  108.         }
  109.     }
  110.    
  111.     function go(){
  112.         if($this->debug || !$this->sql){
  113.             $this->debug();
  114.         } else {
  115.             mysql_select_db($this->db, $this->conn) or die(mysql_error());
  116.             $this->cmd = mysql_query($this->sql, $this->conn) or die(mysql_error());
  117.             if($this->is_select){
  118.                 $this->total = mysql_num_rows($this->cmd);
  119.                 if($this->pre_fetch){
  120.                     if($this->fetch_type == 'array'){
  121.                         $this->fetchArray();
  122.                     } elseif($this->fetch_type == 'assoc'){
  123.                         $this->fetchAssoc();
  124.                     } else {
  125.                         $this->fetch();
  126.                     }
  127.                 } else {
  128.                     if($this->fetch_type == 'array'){
  129.                         $this->loopArray();
  130.                     } elseif($this->fetch_type == 'assoc'){
  131.                         $this->loopAssoc();
  132.                     } else {
  133.                         $this->loop();
  134.                     }
  135.                 }
  136.             } elseif($this->is_insert){
  137.                 $this->id = mysql_insert_id();
  138.                 return $this->id;
  139.             }
  140.         }
  141.     }
  142.    
  143.     function where($key, $value=NULL){
  144.         $this->where[$key] = $value;
  145.     }
  146.    
  147.     function insert($tbl, $arr, $d=false){
  148.         $this->debug = $d;
  149.         $this->sql = 'INSERT INTO `'.T_PREFIX.$tbl.'`';
  150.         $this->is_insert = true;
  151.         $this->buildSet($arr);
  152.         $this->go();
  153.     }
  154.    
  155.     function update($tbl, $arr, $d=false){
  156.         $this->debug = $d;
  157.         $this->sql = 'UPDATE `'.T_PREFIX.$tbl.'`';
  158.         $this->buildSet($arr, true);
  159.         $this->go();
  160.     }
  161.    
  162.     function replace($tbl, $arr, $d=false){
  163.         $this->debug = $d;
  164.         $this->sql = 'REPLACE `'.T_PREFIX.$tbl.'`';
  165.         $this->buildSet($arr);
  166.         $this->go();
  167.     }
  168.    
  169.     function buildSet($arr){
  170.         $this->sql .= ' SET ';
  171.         $total = count($arr); $c = 1;
  172.         foreach($arr as $k=>$v){
  173.             $value = ((stristr($v, "'") || stristr($v, '"')) && !stristr($v, "\'") && !stristr($v, '\"')) ? addslashes($v) : $v;
  174.             $value = ($value == 'NOW()') ? $value : escape($value);
  175.             $this->sql .= sprintf("`%s` = %s",
  176.                                   $k, $value);
  177.             if($c <$total){
  178.                 $this->sql .= ', ';
  179.             }
  180.             $c++;
  181.         }
  182.         $this->sql .= $this->buildWhere();
  183.         $this->sql .= ';';
  184.     }
  185.    
  186.     function buildWhere(){
  187.         $total = count($this->where);
  188.         if($total> 0){
  189.             $where = " WHERE ";
  190.             $c=0;
  191.             foreach($this->where as $k=>$v){
  192.                 if($c> 0){
  193.                     $where .= " AND ";
  194.                 }
  195.                 if((stristr($v, "'") || stristr($v, '"')) && !stristr($v, "\'") && !stristr($v, '\"')){
  196.                     $value = addslashes($v);
  197.                 } else {
  198.                     $value = $v;
  199.                 }
  200.                 if($value == 'NULL'){
  201.                     $where .= sprintf("`%s` IS NULL",
  202.                                           $k);
  203.                 } else {
  204.                     $where .= sprintf("`%s` = %s",
  205.                                         $k, escape($value));
  206.                 }
  207.                 $c++;
  208.             }
  209.             $where = str_replace(" = 'NULL'", ' IS NULL', $where);
  210.             $where = str_replace(" = NULL", ' IS NULL', $where);
  211.             return $where;
  212.         }
  213.         return '';
  214.     }
  215.    
  216.     function debug(){
  217.         echo ($this->sql) ? '<pre>'.$this->sql.'</pre>' : 'There is no SQL statement to view.';
  218.     }
  219. }
  220.  
  221. function escape($v, $t='text', $d='', $n=''){
  222.     $val = get_magic_quotes_gpc() ? stripslashes($v) : $v;
  223.     $val = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($val) : mysql_escape_string($val);
  224.     switch($t){
  225.         case 'text' :
  226.         case 'date' :
  227.             $val = ($val != '') ? "'$v'" : 'NULL';
  228.             break;
  229.         case 'long' :
  230.         case 'int' :
  231.             $val = ($val != '') ? intval($val) : 'NULL';
  232.             break;
  233.         case 'defined' :
  234.             $val = ($val != '') ? $d : $n;
  235.             break;
  236.     }
  237.     return $val;
  238. }
  239.  
  240. ?>

Usage:
Basic Select

PLAIN TEXT
PHP:
  1. <?php
  2. $comments = new mySQL('
  3.     SELECT
  4.         *
  5.     FROM
  6.         `comments`
  7.     WHERE
  8.         `deleted` IS NULL
  9.     ORDER BY
  10.         `post_date` ASC'
  11. );
  12.  
  13. if($comments->total> 0){
  14.     do {
  15.         echo '<p>' . $comments->row->name . '
  16.             said ' . $comments->row->text . '
  17.             on ' . $comments->row->post_date .'
  18.         </p>';
  19.     } while ($comments->fetch());
  20. }
  21. ?>

Select with Where

PLAIN TEXT
PHP:
  1. <?php
  2. $post = new mySQL(
  3.     sprintf('
  4.         SELECT
  5.             *
  6.         FROM
  7.             `posts`
  8.         WHERE
  9.             `id` = %s'
  10.         escape($post_id)
  11.     )
  12. );
  13.  
  14. echo '<h1>' . $post->row->title . '</h1>';
  15. echo '<div id="post_content">' . $post->row->post_content . '</div>';
  16. ?>

Insert

PLAIN TEXT
PHP:
  1. <?php
  2. $data = array(
  3.     'name' => $_POST['name'],
  4.     'email' => $_POST['email'],
  5.     'comment' => $_POST['comment']
  6. );
  7.  
  8. $add_comment = new mySQL();
  9. $new_comment_id = $add_comment->insert('comments', $data);
  10. ?>

Update

PLAIN TEXT
PHP:
  1. <?php
  2. $data = array(
  3.     'title' => $_POST['title'],
  4.     'post_content' => $_POST['post_content']
  5. );
  6.  
  7. $update_post = new mySQL();
  8. $update_post->where['id'] = $post_id;
  9. $update_post->update('posts', $data);
  10. ?>

Please note, my recent updates include adding a new function "seek" and now default to return a mysql_fetch_object() instead of mysql_fetch_array(). This can be easily changed back in the constructor.

This entry was posted on Friday, May 1st, 2009 at 2:37 pm and is filed under PHP. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

2 Responses to “PHP MySQL Class”

  1. Joe Coolest Says:

    August 4th, 2009 at 5:58 pm

    I’m attempting to use this class to bridge the gaps in my OOP PHP knowledge. OOP is quite a challenge for me at the moment.

    For the life of me I can’t figure out how to implement a mysql_data_seek function into your class.

    function seek($index) { return mysql_data_seek( $this->????, $index ); }

    I thought I could easily extend your class with a few features to allow me to seek to first and last result of a query, but I am completely stumped at this point. I’m not giving up and don’t really want the functions handed over, but I’d love a few pointers that might help me fill in the gaps in my knowledge regarding what I would need to pass to mysql_data_seek.

    I look forward to becoming a regular follower of your blog. A few of the posts that I have investigated thus far have been extremely helpful. Thank you.


  2. briandichiara Says:

    November 1st, 2009 at 3:38 pm

    Joe, take a look at the recent update. I added SEEK as part of the class. Unfortunately I wasn’t able to test it. If you have any issues, just let me know. I don’t use it much, so hopefully my addition is sufficient. Also note the note above about returning an object instead of an array. Thanks for reading!


Leave a Reply

Preview: