Why do I get a SQL error when preparing a statemen

I have the following query:

INSERT INTO ipi_messages (Message_userID, Message_fromName, Message_fromEmail, Message_subject, Message_body) VALUES(0, 'hope', 'thisworks@gmail.com', 'i hope', 'this works')

And I get the following MySQL error:

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 '0' at line 1

Here's my table schema:

Here is my main PHP code:

<?php
    require 'lib/class.Database.php';
    require 'lib/class.Messages.php';

    $messageDatabase = new Database('ipi2');
    $messageDatabase->newDatabaseUsers([
                                        ['Query','ipi_query','k2QvHmtxGhVN'],
                                        ['admin','ipi_admin','r0HHRsQ76kS2']
                                        ]);
    $messageDatabase->makeConnection('admin');

    var_dump($_POST);

    if(array_key_exists('sendMessage', $_POST)){
        // $query = "INSERT INTO ipi_messages (Message_userID, Message_fromName, Message_fromEmail, Message_subject, Message_body) VALUES(?,?,?,?,?)";
        // $result = $messageDatabase->query($query, $_POST['to'], $_POST['fromName'], $_POST['fromEmail'], $_POST['subject'], $_POST['message']);

        $messages = new Messages($messageDatabase);
        $messages->postMessage([
                                'Message_userID' => (int)$_POST['to'],
                                'Message_fromName' => $_POST['fromName'],
                                'Message_fromEmail' => $_POST['fromEmail'],
                                'Message_subject' => $_POST['subject'],
                                'Message_body' => $_POST['message']
                                ]);
    }

    if(array_key_exists('getMessages', $_POST)){
        $query_get = "SELECT * FROM ipi_messages WHERE Message_userID = ?";
        $result_get = $messageDatabase->query($query_get, $_POST['user']);
    }
?>
<!doctype html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Messages</title>
    <style type="text/css">h5, h6{margin: 0;padding: 0;}</style>
</head>
<body>
    <h3>Send Message</h3>
    <form name="sendMessage" method="post" action="">
        <h5>To:</h5>
        <select name="to">
            <option value=""></option>
            <option value="0">Fillip Peyton</option>
            <option value="1">Fillswitch</option>
        </select>
        <br/>

        <h5>Name:</h5>
        <input type="text" name="fromName">
        <h5>Email:</h5>
        <input type="text" name="fromEmail">
        <h5>Subject:</h5>
        <input type="text" name="subject">
        <h5>Message:</h5>
        <textarea name="message"></textarea>
        <br/>

        <input type="submit" name="sendMessage" value="Send">
    </form>

    <h3>Your Messages:</h3>
    <form name="getMessages" method="post" action="">
        <select name="user">
            <option value=""></option>
            <option value="0">Fillip Peyton</option>
            <option value="1">Fillswitch</option>
        </select>
        <input type="submit" name="getMessages" value="Get Messages">
        <?php
            if (isset($result_get)) {
                foreach ($result_get as $result) {
        ?>
                    <p>
                        <b><?php echo $result['Message_fromName']; ?></b><br/>
                        <?php echo $result['Message_fromEmail']; ?><br/>
                        <i><?php echo $result['Message_subject']; ?></i><br/>
                        <?php echo $result['Message_body']; ?>
                    </p><hr>
        <?php
                }
            }
        ?>
    </form>
</body>
</html>

class.Messages.php:

<?php
require_once 'lib/classFunctions.php';

class Messages
{
    protected $database;
    protected $userID = -1;

    public function __construct(Database $messageDatabase) {
        $this->database = $messageDatabase;
    }

    public function getMessages($userID = null){

    }

    public function postMessage(Array $columnsValues){
        // $query = "INSERT INTO ipi_messages (Message_userID, Message_fromName, Message_fromEmail, Message_subject, Message_body) VALUES(?,?,?,?,?)";

        $columns = '';
        $values = '';
        $queryParams = array();

        foreach ($columnsValues as $column => $value) {
            $columns .= "$column, ";
            $values .= is_int($value) ? "$value, " : "'$value', ";
            $queryParams[] = $value;
        }

        $columns = substr($columns, 0, strlen($columns) - 2);
        $values = substr($values, 0, strlen($values) - 2);

        $query = "INSERT INTO ipi_messages ($columns) VALUES($values)";

        // array_unshift($queryParams, $query);

        // $result = $messageDatabase->query($query, $_POST['to'], $_POST['fromName'], $_POST['fromEmail'], $_POST['subject'], $_POST['message']);
        echo $query . '<br/>';
        return $result = call_user_func_array(array($this->database,'query'), refValues($queryParams));
    }

    public function deleteMessage(){}
}

class.Database.php:

<?php
require_once 'lib/classFunctions.php';

class Database
{
    protected $DATABASE = 'database';
    protected $SERVER = 'localhost';
    protected $DATABASEUSERS;
    protected $CONNECTION;


    public function __construct($database, $server = 'localhost'){
        $this->DATABASE = $database;
        $this->SERVER = $server;
    }

    public function makeConnection($userType){
        $user = $this->DATABASEUSERS[strtolower($userType)];
        $this->CONNECTION = new mysqli($this->SERVER, $user->getUsername(), $user->getPassword(), $this->DATABASE) or die('Cannot make connection to database...');
    }

    public function escapeSpecialChars($string){
        return $this->CONNECTION->real_escape_string($string);
    }

    public function newDatabaseUser($userType, $username, $password){
        $userType = strtolower($userType);
        $this->DATABASEUSERS[$userType] = new DatabaseUser($userType, $username, $password);
    }

    public function newDatabaseUsers($usersCollection){
        foreach ($usersCollection as $user) {
            $userType = strtolower($user[0]);
            $username = $user[1];
            $password = $user[2];       

            $this->DATABASEUSERS[$userType] = new DatabaseUser($userType, $username, $password);
        }
    }

    public function closeConnection(){
        if($this->CONNECTION)
            $this->CONNECTION->close();
        else
            throw new Exception("No connection available.", 1);
    }

    public function query($query, $vars = null){
        $stmt = $this->CONNECTION->stmt_init();

        $args = func_get_args();
        if($stmt->prepare($query)){

            if($vars != null){
                $queryParams = array();
                $queryTypes = '';
                $argsCount = count($args);

                for( $i = 1 ; $i < $argsCount ; $i++ ){
                    $var = $args[$i];
                    $varType = gettype($var);
                    switch ($varType) {
                        case 'string':
                            $queryTypes .= 's';
                            break;
                        case 'integer':
                            $queryTypes .= 'i';
                            break;
                        case 'double':
                            $queryTypes .= 'd';
                            break;
                        case 'blob':
                            $queryTypes .= 'b';
                            break;

                        default:
                            throw new Exception("Could not bind parameter of type: " . $varType, 1);                        
                            break;
                    }
                } 
                $queryParams[] = $queryTypes;

                for( $i = 1 ; $i < $argsCount ; $i++ )
                    $queryParams[] = $args[$i];

                // $stmt->bind_param($queryParams);
                call_user_func_array(array($stmt,'bind_param'), refValues($queryParams));
            }

            $isExecuted = $stmt->execute();
            if($isExecuted)
                return $result = $stmt->get_result();
            else
                die("Could not execute query($query):" . $this->CONNECTION->error);

        }else{
            die("Could not prepare statement: " . $this->CONNECTION->error);
        }

        $stmt->reset();
    }
}

class DatabaseUser
{
    protected $USERTYPE = 'query';
    protected $USERNAME = 'username';
    protected $PASSWORD = 'password';

    public function __construct($userType, $username, $password) {
        $this->USERTYPE = $userType;
        $this->USERNAME = $username;
        $this->PASSWORD = $password;
    }

    public function getUserType(){ return $this->USERTYPE; }
    public function getUsername(){ return $this->USERNAME; }
    public function getPassword(){ return $this->PASSWORD; }
}

classFunctions.php:

<?php
function refValues($arr){
    if (strnatcmp(phpversion(),'5.3') >= 0) //Reference is required for PHP 5.3+
    {
        $refs = array();
        foreach($arr as $key => $value)
            $refs[$key] = &$arr[$key];
        return $refs;
    }
    return $arr;
}

EDIT: I have added the PHP classes and scripts I am using along with updated my SQL query to make the 0 an int instead of a string.

Why am I getting a SQL error when I prepare my mysqli statement? I tried taking just the SQL query and running it on the database, and the row inserted just fine.

回答1:

The error is not on the cited part, but before it. So, despite of many comments, there is nothing wrong with '0' itself.
There is no error visible at glance - so, you have to debug your code.
- first of all you have to make sure that cited query is the same query with error. you have to always put file name and line number along with error message. To do this, instead of usual useless die($mysqli->error) it have to be trigger_error($myqli->error."[$sql]")
- it would be also a good idea to split the query into multiple lines to make error message more informative.

回答2:

I recreated your schema and inserted two rows using your query, one from the MySQL command line and the other from PhpMyAdmin. Screenshot is here: . Your query seems to be valid so the problem lies elsewhere (probably in your PHP code).

This is not your problem but as others have mentioned, the datatype of Message_userID is INT and you have 0 in quotes which represents a string.

回答3:

I finally figured out what I was doing wrong. I was getting that error because I was placing the actual values into the VALUES() portion of my sql. This way, when my bind_param method was trying to bind values, it didn't have the ? markers to bind to, rather, it saw the actual values.

My new class.Messages.php looks like:

<?php
require_once 'lib/classFunctions.php';

class Messages
{
    protected $database;
    protected $userID = -1;

    public function __construct(Database $messageDatabase) {
        $this->database = $messageDatabase;
    }

    public function getMessages($userID = null){

    }

    public function postMessage(Array $columnsValues){
        $columns = '';
        $values = '';
        $queryParams = array();

        foreach ($columnsValues as $column => $value) {
            $columns .= "$column, ";
            $values .= "?, ";
            $queryParams[] = $value;
        }

        $columns = substr($columns, 0, strlen($columns) - 2);
        $values = substr($values, 0, strlen($values) - 2);

        $query = "INSERT INTO ipi_messages ($columns) VALUES($values)";

        array_unshift($queryParams, $query);

        return $result = call_user_func_array(array($this->database,'query'), refValues($queryParams));
    }

    public function deleteMessage(){}
}

Thanks everyone for your help!