Connection class to MySQL database using PHP

Connection class to MySQL database using PHP

Online by JSC0d3
June 16, 2014 | | | 1590 recognitions

We all know that PHP is a one of best scripting language for web development.We can build awesome dynamic web pages using PHP. But we must build something valuable other than produce unimportant web sites. So today i am going to tel you how to write and test the connection class to MySQL database using PHP.

This class can:

– Establish MySQL server connections
– Execute SQL queries
– Query a single value or a single row
– Retrieve query results into arrays or resource objects
– Retrieve the last inserted ID
– Manage transactions (transaction processing)
– Retrieve the list tables of a database
– Retrieve the list fields of a table
– Retrieve the length or position of a field
– Measure the time a query takes to execute
– Display query results in an HTML table
– Format SQL parameters (safe against SQL injection hacks)
– Handle errors

Connection class to MySQL database using PHP

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;
    }

}

 

JSC0d3's Logo
About JSC0d3

JSC0d3 is an entrepreneur, online marketer, and an employee of an IT company. When not building websites, creating content, or helping customers improve their online business, spend time with their wife and two beautiful children. Although he still feels new in WordPress, he enjoys sharing what he has learned with all of you! If you want to get in touch with him, you can do this through this website.

On the same idea

Posted by | February 9, 2019

WordPress' wp_link_pages() function, used for displaying page links in multi-page posts, lacks one big feature You can display a list of...

Posted by | December 14, 2018

Preliminary step: Testing whether the code complies with the format of a fiscal identification code (CIF) That is, the maximum length should be 10...

Posted by | November 8, 2017

Nowadays, a lot of bloggers choose to remove HTML tag from WordPress websites However, for beginners and newbies in this field, they may feel...

Previous PostBackNext Post

Leave here an impression