How to connect mysql database using PHP oop way?

Question

I am currently learning PHP and trying to figure out how to connect my MySQL database named (lead_gen_business) using OOP. I was given a task to get the specific output by the way. My problem is I am having a hard time connecting to my database. For the context of the code. I created a database and the code inside is a pre-built function that I modified for OOP.

Database

class Database
{
    public $connection;

    public function __construct()
    {

        if (!defined('DB_HOST')) {
            define('DB_HOST', 'localhost');
        }

        if (!defined('DB_USER')) {
            define('DB_USER', 'root');
        }

        if (!defined('DB_PASS')) {
            define('DB_PASS', 'Yourpass');
        }
    }

    public function connect($db_name)
    {
        $this->connection = new mysqli(DB_HOST, DB_USER, DB_PASS, $db_name);
    }

    /*-----------------------END OF CONNECTION PROCESS------------------------*/
    /*----------------------DATABASE QUERYING FUNCTIONS-----------------------*/
    //SELECT - used when expecting single OR multiple results
    //returns an array that contains one or more associative arrays
    function fetch_all($query)
    {
        $data = array();
        global $connection;
        $result = $connection->query($query);
        while ($row = mysqli_fetch_assoc($result)) {
            $data[] = $row;
        }
        return $data;
    }
    //SELECT - used when expecting a single result
    //returns an associative array
    function fetch_record($query)
    {
        global $connection;
        $result = $connection->query($query);
        return mysqli_fetch_assoc($result);
    }
    //used to run INSERT/DELETE/UPDATE, queries that don't return a value
    //returns a value, the id of the most recently inserted record in your database
    function run_mysql_query($query)
    {
        global $connection;
        $result = $connection->query($query);
        return $connection->insert_id;
    }
    //returns an escaped string. EG, the string "That's crazy!" will be returned as "That\'s crazy!"
    //also helps secure your database against SQL injection
    function escape_this_string($string)
    {
        global $connection;
        return $connection->real_escape_string($string);
    }
}

    require_once('database.php');
    
    class Sites extends Database
    {
    
        public $client;
        public $havingArr = [];
        public $count = "COUNT(*)";
        public $id;
    
        public function select($myClient)
        {
            // Assuming $myClient is an array containing column names
            foreach ($myClient as $data) {
                $this->client = $data;
            }
            return $this;
        }
    
        public function group_by($siteId)
        {
            $this->id = $siteId;
            return $this;
        }
    
        public function having($paraCount, $greaterThan, $number)
        {
            // Building having condition
            $this->havingArr[] = "$paraCount $greaterThan $number";
            return $this;
        }
    
    
        public function get()
        {
            // $havingCondition = implode(" AND ", $this->havingArr);
            // $query = "SELECT {$this->client}, {$this->count} FROM sites
            //  GROUP BY {$this->id} HAVING $havingCondition";
            $query = "SELECT * FROM sites";
            // Assuming $this->connection->fetch_all is a method that executes the query and fetches all rows
            $result = $this->fetch_all($query);
    
            return $result;
        }
    }

And lastly...

    include('database.php');
    include('sites.php');
    
    
    class Query_builder extends Sites
    {
    
        public function __construct($db_name)
        {
    
            $this->connect($db_name);
        }
    
        public function show_get()
        {
            $datas = $this->get();
    
            foreach ($datas as $data) {
                echo $data['site_id'] . "<br>";
                echo $data['domain_name'] . "<br>";
            }
    
            return $this;
        }
    }
    
    $show = new Query_builder('lead_gen_business');
    
    var_dump($show->show_get());

I got a lot of errors before this and the last error I got is this.

Fatal error: Uncaught Error: Call to a member function query() on null in C:\wamp64\www\OOP Fundamentals\Advance OOP\Query Builder\database.php on line 38

Sorry if it's too long. I already spent a day trying to figure out this independently. Any answer is much appreciated! Thanks in advance.

Answer 1

You use global $connection in one of your functions, but when you created the connection, you assigned it to $this->connection, a class variable.

I think the important fact you're missing is that these are different variables.

You assigned the connection to $this->connection, but this does not assign the connection to the global variable $connection. A class variable is not a global.

Then when you try to use global $connection in one of your functions, that variable has nothing assigned to it. In PHP, you can reference variables that has not been initialized, but it is initially just null.

That's why you got the error when you try to call $connection->query():

Uncaught Error: Call to a member function query() on null

A variable with value null is not an object, and has no member functions. It's like trying to call a member function on a value of true or the integer 42.

$my_var = 42;
$my_var->query(); // this would cause the same error

If you assign the connection to $this->connection, then you must use the same reference in subsequent functions of the class.

For example:

function fetch_all($query)
{
    $data = array();

    $result = $this->connection->query($query);
    while ($row = mysqli_fetch_assoc($result)) {
        $data[] = $row;
    }
    return $data;
}

Here the function uses $this->connection, assuming it was set by a prior call to the connect() function of the class.

There are also opportunities to improve your class in other ways, so the answer of what is the OOP way could be longer. But I'm answering only the reason for the error you showed in your post above.

Answer 2

We've all done this mistake (me too!).

A database class is the worst thing to build in order to learn OOP. You aren't familiar yet with databases or classes, so you end up with creating a wrapper for the builtin database extension that does not add a single benefit, prevents you from using features that you still don't know that exist (prepared statements, getting auto-incremented IDs, handling transactions...) and often breaks basic functionality (such as error reporting).

Additionally, making your domain classes (Invoice, Customer, BlogPost...) extend your custom Database class is not only not useful, it also makes it very hard to design and understand application logic. Why? Because it doesn't make sense as inheritance relationship, so you end up being confused and unsure about how to proceed when you add more models.

My tips:

1. Use raw native database classes (PDO and PDOStatement, or mysqli and mysqli_stmt if you really don't find mysqli utterly counter-intuitive).

2. Don't make database connections and queries part of your application model design. Instead, consider them services (such as email delivery or PDF generation). The clases than handle such services belong to a separate hierarchy.

3. Global variables don't make coding easier, they make it significantly harder. It's not "cool, I don't have to pass variables along so my app will be easier to write", it is "drat, I don't know where my variables come from or are being changed, this is a mess".

4. Don't get obsessed with saving keystrokes, that should never be the purpose of application logic. You'll spend way more time thinking and debugging than typing, and if you really think your typing slows you down, you're efforts will be better invested in touch typing classes.