Grow your CSS skills. Land your dream job.

Last updated on:

MySQL Database Access Class

<?php

/* vim: set expandtab tabstop=4 shiftwidth=4 softtabstop=4: */


class dbAccess {

       var $db_connect_id;
       var $query_result;
       var $row = array();
       var $rowset = array();
       var $num_queries = 0;

       //
       // Constructor
       //
       function dbAccess($sqlserver, $sqluser, $sqlpassword, $database, $persistency = true) {

               $this->persistency = $persistency;
               $this->user = $sqluser;
               $this->password = $sqlpassword;
               $this->server = $sqlserver;
               $this->dbname = $database;

               if($this->persistency) {
                       $this->db_connect_id = @mysql_pconnect($this->server, $this->user, $this->password);
               } else {
                       $this->db_connect_id = @mysql_connect($this->server, $this->user, $this->password);
               }
               if($this->db_connect_id) {
                       if($database != "")     {
                               $this->dbname = $database;
                               $dbselect = @mysql_select_db($this->dbname);
                               if(!$dbselect) {
                                       @mysql_close($this->db_connect_id);
                                       $this->db_connect_id = $dbselect;
                               }
                       }
                       return $this->db_connect_id;
               } else {
                       return false;
               }
       }

       //
       // Other base methods
       //
       function destroy() {
               if($this->db_connect_id) {
                       if($this->query_result) {
                               @mysql_free_result($this->query_result);
                       }
                       $result = @mysql_close($this->db_connect_id);
                       return $result;
               } else {
                       return false;
               }
       }

       //
       // Base query method
       //
       function query($query = "", $transaction = FALSE) {
               // Remove any pre-existing queries
               unset($this->query_result);
               if($query != "") {
                       $this->num_queries++;
                       $this->query_result = @mysql_query($query, $this->db_connect_id);
               }
               if($this->query_result) {
                       unset($this->row[$this->query_result]);
                       unset($this->rowset[$this->query_result]);
                       return $this->query_result;
               } else {
                       return ( $transaction == END_TRANSACTION ) ? true : false;
               }
       }

       //
       // Other query methods
       //
       function numrows($query_id = 0) {
               if(!$query_id) {
                       $query_id = $this->query_result;
               }
               if($query_id) {
                       $result = @mysql_num_rows($query_id);
                       return $result;
               } else {
                       return false;
               }
       }
       function affectedrows() {
               if($this->db_connect_id) {
                       $result = @mysql_affected_rows($this->db_connect_id);
                       return $result;
               } else {
                       return false;
               }
       }
       function numfields($query_id = 0) {
               if(!$query_id) {
                       $query_id = $this->query_result;
               }
               if($query_id) {
                       $result = @mysql_num_fields($query_id);
                       return $result;
               } else {
                       return false;
               }
       }
       function fieldname($offset, $query_id = 0) {
               if(!$query_id) {
                       $query_id = $this->query_result;
               }
               if($query_id) {
                       $result = @mysql_field_name($query_id, $offset);
                       return $result;
               } else {
                       return false;
               }
       }
       function fieldtype($offset, $query_id = 0) {
               if(!$query_id) {
                       $query_id = $this->query_result;
               }
               if($query_id) {
                       $result = @mysql_field_type($query_id, $offset);
                       return $result;
               } else {
                       return false;
               }
       }
       function fetchrow($query_id = 0) {
               if(!$query_id) {
                       $query_id = $this->query_result;
               }
               if($query_id) {
                       $this->row[$query_id] = @mysql_fetch_array($query_id);
                       return $this->row[$query_id];
               } else {
                       return false;
               }
       }
       function fetchrowset($query_id = 0) {
               if(!$query_id) {
                       $query_id = $this->query_result;
               }
               if($query_id) {
                   $result = array();
                       unset($this->rowset[$query_id]);
                       unset($this->row[$query_id]);
                       while($this->rowset[$query_id] = @mysql_fetch_array($query_id)) {
                               $result[] = $this->rowset[$query_id];
                       }
                       return $result;
               } else {
                       return false;
               }
       }
       function fetchfield($field, $rownum = -1, $query_id = 0) {
               if(!$query_id) {
                       $query_id = $this->query_result;
               }
               if($query_id) {
                       if($rownum > -1) {
                               $result = @mysql_result($query_id, $rownum, $field);
                       } else {
                               if(empty($this->row[$query_id]) && empty($this->rowset[$query_id])) {
                                       if($this->fetchrow()) {
                                               $result = $this->row[$query_id][$field];
                                       }
                               } else {
                                       if($this->rowset[$query_id]) {
                                               $result = $this->rowset[$query_id][$field];
                                       } else if($this->row[$query_id]) {
                                               $result = $this->row[$query_id][$field];
                                       }
                               }
                       }
                       return $result;
               } else {
                       return false;
               }
       }
       function rowseek($rownum, $query_id = 0){
               if(!$query_id) {
                       $query_id = $this->query_result;
               }
               if($query_id) {
                       $result = @mysql_data_seek($query_id, $rownum);
                       return $result;
               } else {
                       return false;
               }
       }
       function nextid(){
               if($this->db_connect_id) {
                       $result = @mysql_insert_id($this->db_connect_id);
                       return $result;
               } else {
                       return false;
               }
       }
       function freeresult($query_id = 0){
               if(!$query_id) {
                       $query_id = $this->query_result;
               }

               if ( $query_id ) {
                       unset($this->row[$query_id]);
                       unset($this->rowset[$query_id]);

                       @mysql_free_result($query_id);

                       return true;
               } else {
                       return false;
               }
       }
       function error($query_id = 0) {
               $result["message"] = @mysql_error($this->db_connect_id);
               $result["code"] = @mysql_errno($this->db_connect_id);

               return $result;
       }
}
?>

Comments

  1. Permalink to comment#

    I like this class, can you show a sample of how to use it? Thanks.

  2. Esi
    Permalink to comment#

    +1 how to use?

  3. Permalink to comment#

    How do you use???

  4. Joshua Getner
    Permalink to comment#

    this is just a simple mysql wrapper but you use it like you would use any other class you must first initiate the class. Since this class has a construct method we most call that to initiate the class and construct the data in the construct method.

    $db = new dbAccess('server' , 'username' , 'pass' , 'dbname');

    from here you now have the class initiated and a connection to mysql .

    so you can then start running your query methods and query’s…
    hope this helped some….

  5. hokki
    Permalink to comment#

    how do use query? fetch array??

    • Isaiah Marc Sanchez
      Permalink to comment#

      The function fetchrow, and fetchrowset use @mysql_fetch_array.

      $db->fetchrow("SQL SCRIPT");
  6. Isaiah Marc Sanchez
    Permalink to comment#

    If any of you know anything about PHP, its pretty simple to use. Its just like any other SQL class. I did my own changes to the script, but other than that, its great.
    LIke Joshua Said,

    $db = new dbAccess('server' , 'username' , 'pass' , 'dbname');

    Just read the PHP function and you’ll see how it works.

    $db->query("SELECT * FROM user WHERE 1=1");
  7. Qafui
    Permalink to comment#

    Can I use MySQL prepared statements with this class? I do not see any parameter binding in your methods so I’m guessing prepared statements won’t work.

  8. vahd
    Permalink to comment#

    what is END_TRANSACTION ????
    when execute query on wamp at this line error appear????

Leave a Comment

Posting Code

Markdown is supported in the comment area, so you can write inline code in backticks like `this` or multiline blocks of code in in triple backtick fences like ```this```. You don't need to escape code in backticks, Markdown does that for you.

Sadly, it's kind of broken. WordPress only accepts a subset of HTML in comments, which makes sense, because certainly some HTML can't be allowed, like <script> tags. But this stripping happens before the comment is processed by Markdown (via Jetpack). It seems to me that would be reversed, because after Markdown processes code in backticks, it's escaped, thus safe. If you think you can fix this issue, get in touch!

If you need to make sure the code (typically HTML) you post absolutely posts correctly, escape it and put it within <pre><code> tags.

Current ye@r *

*May or may not contain any actual "CSS" or "Tricks".