php, how to connect to a database as a class, send sql statments and return the result

Question

Forgive me if the question is a little odd
I can clarify if needed:

I have the code that can connect to a mysql database as normal, however i have encapsulated it as a class:

<?php
define("HOST", "127.0.0.1");     // The host you want to connect to.
define("USER", "phpuser");    // The database username.
define("PASSWORD", "Secretpassword");    // The database password.

class DBConnection{

    function conn($sql, $database){
        $DB = new mysqli(HOST,USER,PASSWORD,$database);
        if ($DB->connect_error){
            die("Connection failed: " . $DB->connect_error);
            exit();
        }

        if ($result = $DB->query($sql)){
            return TRUE;
            $DB->close();
         } 
         else{            
             echo "Error: " . $sql . "<br>" . $DB->error;            
             $DB->close();
         }
    }
}
?>

I have done it this way so i can include this class in any subsequent php page and allow them to send it an sql statment and the database, see below as an example:

$sql = ("INSERT INTO  users (first_name, last_name, username, email, password, group_level) VALUES ('John', 'Doah','JDoah', 'example@email', 'password', 'user')");

        $DB = new DBConnection;
        $result = $DB->conn($sql,"members");

        if ($result ==TRUE){
            return "Record added sucessfully";
        }

This works fine. however, im looking to send other sql statments to DBConnection.

How do i do that and to have it pass back any results that it recives? errors, boolean, row data etc. The caller will worry about parsing it.

Hopefully that makes sense.

Class methods coded like function conn() are a bit PHP4. check the modern PHP manual — RiggsFolly
– RiggsFolly, Commented Apr 4, 2019 at 17:54
And opening and closing a connection around each query is a good way to slow things down for no good reason — RiggsFolly
– RiggsFolly, Commented Apr 4, 2019 at 17:55
And I would guess you are passing a table name to that function but using it as a database name inside the query — RiggsFolly
– RiggsFolly, Commented Apr 4, 2019 at 17:57
I should really remove the constent DATABASE, i give the database name when calling the class. The tablename is within the sql query. — ben jay hutton
– ben jay hutton, Commented Apr 4, 2019 at 18:00

CoderJoe · Accepted Answer · 2019-05-09 18:36:51Z

This is an old class I used to use way back in the days of mysql still works but will need to be updated for mysqli or newer

class DBManager{
    private $credentials = array(
        "host" => "localhost",
        "user" => "",
        "pass" => "",
        "db" => ""
    );

    function DBManager(){
        $this->ConnectToDBServer();
    }

    function ConnectToDBServer(){
        mysql_connect($this->credentials["host"],$this->credentials["user"],$this->credentials["pass"]) or die(mysql_error());
        $this->ConnectToDB();
        session_start();
    }

    function ConnectToDB(){
        mysql_select_db($this->credentials["db"]) or die(mysql_error());
    }

    function Insert($tableName,$data){
        $parameters = '';

        $len = count($data);
        $i = 0;
        foreach($data as $key => $value){
            if(++$i === $len){
                $parameters .= $key . "='$value'";
            }else{
                $parameters .= $key . "='$value'" . ", ";
            }
        }

        $query = "INSERT INTO $tableName SET $parameters";

        mysql_query($query) or die(mysql_error());
        return true;
    }

    function GetRow($tableName,$select,$where){
        $selection = '';

        $len = count($select);
        $i = 0;
        foreach($select as $key){
            if(++$i === $len){
                $selection .= $key;
            }else{
                $selection .= $key . ",";
            }
        }

        $whereAt = '';

        foreach($where as $key => $value){
            $whereAt .= $key . "='$value'";
        }

        $query = "SELECT $selection FROM $tableName WHERE $whereAt";
        $result = mysql_query($query);

        while($row = mysql_fetch_array($result)){
            return $row;
        }
    }
}

The key things here is you can create a persistent connection to your database without rewriting a bunch of code

Example

global $DB;
$DB = new DBManager();

Since the connection happens in the constructor you will now have a connection on the page you call this code and can begin getting and setting to the database through use of $DB->GetRow() and $DB->Insert() which makes things much easier and was modeled after the $wpdb instance which is a class that manages the database in wordpress sites

Examples

For these examples we will assume you have a table as such

Insert new student

//create an associative array
$data = array(
  "student_id" => 1,
  "birth_date" => "02/06/1992",
  "grade_level" => 4
);

//Send Call

$dm->Insert("student",$data);

Get data

//Create selection
$selection = array("grade_level");

//Create associative array for where we want to find the data at
$where = array(
"id" => 1
);

//Get Result
$result = $dm->GetRow("student",$selection,$where);

//do something with result
echo $result->grade_level;

Collectives™ on Stack Overflow

php, how to connect to a database as a class, send sql statments and return the result

1 Answer 1

Examples

Insert new student

Get data

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Examples

Insert new student

Get data

Comments

Your Answer

Sign up or log in

Post as a guest

Related