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

• slepic: Member access operator surrounded with spaces is a real rarity.
• sBucholtz: @slepic My code view wasn't helping, it looked better with spaces... And I noticed that you're right, none of the code I've read surrounds them with spaces I guess it is rare. So I changed it and now code hinting works a lot better lol. Thank you for pointing that out.
• KIKO Software: Does this code work? I don't see where $row is coming from in result(). I didn't test the code myself, but it seems weird to me.
• sBucholtz: Yes, I would not post if it did not work, it will work on any prepared query. If you have no params to bind then call $db->statement($sql, FALSE). For the $row variable you need to understand how references work php.net/manual/en/language.references.pass.php it is created in the first while loop and references the field name from $meta->fetch_field()
• KIKO Software: I tested your code, and indeed it does work. I just find it difficult to understand. I know how references work, I just don't like (to use) them. I prefer the "normal" way, illustrated in the examples of execute(). What I'm missing from your class is a way to bind parameters. I hope you don't do stuff like SELECT * FROM table WHERE col1 = '$inputValue'.

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.

• sBucholtz: I realise the credentials being defined directly in the object isn't ideal. For my particular use case it is not an issue since there is no plan to connect to any other DB. But I can completely get behind why you wouldn't do it this way. I will seriously consider changing it. I kind of figured out the private method was completely redundant (useless) and moved the code into the constructor on my local files after I posted the question. None-the-less I still wanted an answer on which way is correct. Thank you for the answer +1