# PHP MySQLi database object

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; } }  • Member access operator surrounded with spaces is a real rarity. – slepic Feb 11 '20 at 6:05 • @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. – sBucholtz Feb 12 '20 at 0:51 • 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. – KIKO Software Feb 12 '20 at 9:42
• 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() – sBucholtz Feb 13 '20 at 2:12
• 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'. – KIKO Software Feb 13 '20 at 11:54