Alex Winder Alex Winder - 1 year ago 85
MySQL Question

PHP OOP Learning Curve - MySQL Object Failure

I'm currently learning PHP and I'm new to OOP. I'm trying to create an object to handle MySQL queries and connections.

This is what I've created so far:

class MySQLDatabase {

private $connection;

function __construct() {

public function open_connection() {
$this->connection = mysqli_connect(DB_SERVER, DB_USER, DB_PASS, DB_NAME);
if(mysqli_connect_errno()) {
"Database connection failed: " . mysqli_connect_error() .
" (" . mysqli_connect_errno() . ")"

public function close_connection() {
if(isset($this->connection)) {

public function query($sql) {
$cleaned_sql = mysqli::real_escape_string($sql);
$result = mysqli_query($this->connection, $cleaned_sql);
return $result;

public function mysql_prep($string) {
$escaped_string = mysqli_real_escape_string($this->connection, $string);
return $escaped_string;

private function confirm_query($result) {
if (!$result) {
die("Database query failed.");

And on the public-facing side (doing a test to make sure things work as expected):

$sql = "INSERT INTO users (id, username, password, first_name, last_name) ";
$sql .= "VALUES (1, 'jbloggs', 'secretpwd', 'Joe', 'Bloggs')";
$result = $database->query($sql);

Currently, I just get the output of:
Database query failed.

The issue seems to be something to do with my mysql_prep function, as when I remove that all works fine.

Any advice is greatly welcomed.

Thanks in advance!

Answer Source

You're running your ENTIRE query through the escape function, which is exactly the WRONG thing to do. That eliminates any quotes necessary for the query to be syntactically correct.

Consider this:

INSERT INTO foo (bar) VALUES ('baz')

Since you're escaping the entire thing, you're sending this to the database:

INSERT INTO foo (bar) VALUES (\'bar\')

since those quotes are escape, they're not quotes anymore. they're plaintext characters, and the DB is looking for a field named 'bar' to get a value from. You can't look up a field in a record for insertion, because you're INSERTING the record and it doesn't exist yet. And 'bar' is unlikely to exist in your table definition.

So your query fails with a syntax error, and since you have a fixed/unchanging/useless "failed" message, you never get told the reason WHY it failed.

At least change your die() to something like

die("Query failed: " . mysqli_error($this->connection));