PHP MySQLi database object

Question

I've been learning more about PHP Objects and Classes, and my immediate reaction was to create a Database Object for handling the MySQLi Connection, Statements and Results. Its not meant to be an ultimate do-all object, but help with WET code. Obviously this has been done before... at least a hundred times.

There's no question lingering on whether I should be doing this, my question is more along the lines of am I doing this right?.

Basically I don't want to write out 10+ lines of code each time I make an SQL statement, let alone deal with the other functions that take place. So I made an object that can do the SQL connection, statement, and get results. The goal was to make the interaction as simple as possible and I like to think I achieved that. Thoughts?

Usage:

Create the Object

$db = new Sqli();

Execute a Statement

$db->statement($sql, $param)

$db->statement("SELECT column_name FROM table WHERE = ?", "bind_me")

$db->statement("INSERT INTO table (col1, col2, col3) VALUES (?, ?, ?)", [$foo, $bar, $baz]);

Print Results

print_r($db->result());

JSON Result

print json_encode($db->result());

PHP Code:

class Sqli
{
    const DBHOST = "localhost";
    const DBUSER = "";
    const DBPASS = "";
    const DBNAME = "";

    protected $conn;
    protected $stmt;

    function __construct()
    {
        $this->setConnection();
    }

    private function setConnection()
    {
        mysqli_report(MYSQLI_REPORT_STRICT|MYSQLI_REPORT_ERROR);
        try
        {
            $conn = new mysqli(
                self::DBHOST, 
                self::DBUSER, 
                self::DBPASS, 
                self::DBNAME
            );
        }
        catch(MySQLi_sql_exception $e)
        {
            throw new \MySQLi_sql_exception(
                $e->getMessage(), 
                $e->getCode()
            );
        }
        $this->conn = $conn;
    }

    public function statement($sql, $param)
    {
        $stmt   = $this->conn->prepare($sql);

        if($param !== FALSE)
        {

            if(!is_array($param))
            {
                $param = [$param];
            }

            $types = str_repeat("s", count($param));

            $stmt->bind_param($types, ...$param);
        }

        $stmt->execute();

        $stmt->store_result();

        $this->stmt = $stmt;
    }

    public function result()
    {
        $stmt = $this->stmt;

        $meta = $stmt->result_metadata();

        while($field = $meta->fetch_field())
        {
            $param[] = &$row[$field->name];
        }

        call_user_func_array([$stmt, "bind_result"], $param);

        while($stmt->fetch())
        {

            foreach($row as $key => $val)
            {
                $r[$key] = filter_var($val, FILTER_SANITIZE_FULL_SPECIAL_CHARS, FILTER_FLAG_ENCODE_HIGH|FILTER_FLAG_ENCODE_LOW|FILTER_FLAG_ENCODE_AMP);
            }
            $result[] = $r;
        }

        return $result;
    }
}

Answer 1

Dont call private methods in constructor, put the code into the constructor directly (well unless you have reason to call the same piece of code from elsewhere too, not your case tho).

Don't put your db credentials into the class's constants. This way you are unable to use the class to connect to any other database. Well you can change their values, but that means change the code, or you can extend the class and override the constants. But that's also not very convenient.

You might want to prefer PDO instead of the mysqli extension. The PDO contains mysql adapter in it (maybe you need pdo_mysql extension i think). But you will have the freedom to change the underlying database transparently anytime (well unless you are using some specific sql dialect features).

PDO basically offers the same as your class does, except it has no hardcoded credentials, which, as I already mentioned, is bad.

So learn to use PDO and you may find out that you need no such wrapper at all, you just need a place where you pass the right credentials to its contructor. This place could be described as database connection factory.