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:

```
<div>Example code</div>
```

You don't need to escape code in backticks, Markdown does that for you. If anything screws up, contact us and we can fix it up for you.

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