MySQLi OOP Class Insert Function Not Working

Question

I am currently practicing OOP, creating a MySQLi class that will have atleast the basic MySQLi functions (insert, select, update, etc). This is what I have got so far:

if(!class_exists('dbc')) {
class dbc {
    public function __construct($host = host, $username = username, $password = password, $database = database) {
        // Make the constants class variables
        $this->host = host;
        $this->username = username;
        $this->password = password;
        $this->database = database;

        $this->connection = new mysqli($this->host, $this->username, $this->password, $this->database);

        if($this->connection->connect_errno) {
            die('Database connection error!');
            return false;
        }
    }

    public function __deconstruct() {
        if($this->connection) {
            $this->connection->close();
        }
    }

    public function insert($table, $variables = array()) {
        if(empty($table) || empty($variables)) {
            return false;
        }

        $sql = "INSERT INTO $table ";

        $fields = array();
        $values = array();
        foreach($variables as $field => $value) {
            $fields[] = "'" . $field . "'";
            $values[] = "'" . $value . "'";
        }

        $fields = '(' . implode(', ', $fields) . ')';
        $values = '(' . implode(', ', $values) . ')';

        $sql .= $fields . ' VALUES ' . $values;

        $query = $this->connection->query($sql);

        if(!$query) {
            echo mysqli_error($this->connection);
        }

        echo $sql;
    }
}
}

As you can see, I create the connection via the details from the config file, I then send a query through the established connection. But for some reason when I attempt to create a MySQLi insert query, I just get the same error over, and over again:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''name', 'option') VALUES ('Sub Title', 'This is a test website')' at line 1

I even echoed out the sql query, which appeared to be the correct format:

INSERT INTO options ('name', 'option') VALUES ('Sub Title', 'This is a test website')

I have spent hours of Googling, trial and error, etc, trying to fix this, and have had no luck, and as it's 12:30am, I'm tired and may be missing something critical, so if anyone knows what is causing this problem, it'll be greatly appreciated for a solution, etc.

Thanks, Kieron

Marty · Accepted Answer · 2016-02-12 00:44:14Z

2

The column names in the first set of parenthesis should not be quoted:

INSERT INTO options (name, option) VALUES ('Sub Title', 'This is a test website')
//                   ^^^^  ^^^^^^

Though you can use backticks ` around the column names e.g. `name`, `option`.

edited Feb 12, 2016 at 0:44

answered Feb 12, 2016 at 0:39

Marty

39.5k19 gold badges97 silver badges164 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sutton Over a year ago

Just changed it, and I'm still getting the same error.

Marty Over a year ago

If you changed it then the error would be different (it wouldn't say ...right syntax to use near ''name... because that quote would have been removed). Can you post the exact error you're now getting?

Sutton Over a year ago

Nevermind, I just realised I was using option as a column name, even though it's reserved via MySQLi... Bedtime me thinks. Thanks, I am trying to set your answer to green but there is a waiting period.

RiggsFolly · Accepted Answer · 2016-02-12 01:00:40Z

2

Your connection is definitely not working as you are missing the $ on these 4 lines infront of the parameter names

   $this->host = host;
   $this->username = username;
   $this->password = password;
   $this->database = database;

Should be

   $this->host     = $host;
   $this->username = $username;
   $this->password = $password;
   $this->database = $database;

Also the name you have used for your class deconstructor is incorrect it should be

public function __destruct() () {

yours will not cause an error but it will not run automatically on class destruction with your name.

@Marty is correct about the use of backticks and not single quotes around your query syntax, but I dont see how the connection gets made based on the first error I mentioned, and therefore how you get a sensible SQL error reported, however something may be going on that is not obvious from the code you showed us.

edited Feb 12, 2016 at 1:00

answered Feb 12, 2016 at 0:53

RiggsFolly

94.7k22 gold badges108 silver badges152 bronze badges

2 Comments

Marty Over a year ago

It looks like those may be constants somewhere (although it seems the $ were still missed, but it would work either way if that's the case).

RiggsFolly Over a year ago

Odd that they should be passed as parameters in that case

Collectives™ on Stack Overflow

MySQLi OOP Class Insert Function Not Working

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related