Jump to content

  •  

Photo
- - - - -

Basic Database Class using MySQLi

PHP4Beginners

  • Please log in to reply
No replies to this topic

#1 Wilson18

Wilson18

    Administrator

  • root
  • 112 posts
  • LocationUniversity of Birmingham

Posted 13 June 2015 - 03:38 PM

This is a basic class used to connect to a database using MySQLi along with some useful functions like ExecuteQuery and one to clean a string of harmful characters. I have also included a readme to show you how to use this. Please make sure to do your own security testing as this is only used for demonstration purposes and its secureness cannot be guarenteed. 

 

Please use this GitLab Link to get these files. If updates are needed, they will be done there and you can also submit your own changes 

 

Database Class 

<?php
class Database {
        //Private variable to store sql connection
        private $sql;
        //function which is called when object is created
        function __construct() {
                //start the sql connection. Replace with your database config
                $this->sql = new mysqli('Hostname','Username','Password','DatabaseName');
        }
        /*
        Function to exequte sql query.
        $IO is for operations which do not have a return value like insert queries
        */
        public function ExecuteQuery($Q, $IO = false){
                //Store sql variable locally
                $sql=$this->sql;
                //init results array and store them after executing the sql query
                $results_array = array();
                $result = $sql->query($Q);
                //If there is no expected array of results, just return the $result (true or false depending if it worked!)
                if($IO){
                     return $result;
                }else{
                        //if query was successful, store and return the results
                        if($result){
                                while ($row = $result->fetch_assoc()) {
                                $results_array[] = $row;
                                }
                                return $results_array;
                        }else{
                                //not successful, return nothing
                                return null;
                        }
                }
        }
        //called when object is deleted - close sql connection
        function __destruct(){
                $this->sql->close();
        }
        //clean a string using the mysqli real escape string function
        public function cleanString($string){
                return mysqli_real_escape_string($this->sql, $string);
        }
}

Read Me - Example use

<?php
/*
To use the Database.php file, you must first edit it and change the
line in the __construct method to use the correct connection details for your database.
This requires the hostname (normally localhost), the username, password and database name to use.

After saving the file, you will then need to include the file in the class you want to use it
        include "Database.php";
You then create an new object with it and use it. You can see some examples uses below.
*/

include "Database.php";

$db = new Database();
$username="username";
//Using a select query
$result = $db->ExecuteQuery("SELECT * FROM users WHERE username=".$username);
//print the array of results.
print_r($result);
//echo specific field from the first row (starting at 0)
echo "<br>Some field = ". $result[0]['name']."<br>";
$username="newUsername";
$password="newPassword";
$name="bob";
//doing an insert query using the clean string method.
$result=$db->ExecuteQuery("INSERT INTO users (username, password, name) VALUES ('".$db->cleanString($username)."','".$db->cleanString($password)."','".$db->cleanString($name)."')",true);

if($result){
        echo "insert successful";
}else{
        echo "something went wrong";
}

/*
The result of this is:
Array
(
    [0] => Array
        (
            [id] => 1
            [username] => username
            [password] => password
            [name] => name
        )

)
<br>Some field = name<br>insert successful
*/


List what you read?

Check me out on: YouTube, Twitter, Facebook, Google+






Also tagged with one or more of these keywords: PHP4Beginners