为什么在mysqli的准备语句时，我得到一个SQL错误？(Why do I get a SQL er

2019-10-18 05:22发布

站内文章 / 前沿技术
37 0 0

我有以下查询： 

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

而我得到以下MySQL错误： 

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

这里是我的表模式：

这是我主要的PHP代码： 

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

编辑：我已经加入了PHP类和我使用的一起更新了我的SQL查询，使0一个int，而不是字符串的脚本。

为什么我会得到一个SQL错误，当我准备我的mysqli的声明？ 我试着服用刚刚SQL查询和数据库上运行它，并且插入行就好了。

Answer 1:

  1. 该错误是不是在引用部分，但在此之前它。 所以，尽管很多评论，有什么不对的“0”本身。
  2. 没有错误的一瞥看到 - 所以，你必须调试代码。
    • 首先你必须确保引用查询与错误相同的查询。 你必须始终把文件名和行号与错误消息一起。 要做到这一点，代替通常的无用die($mysqli->error)它必须是trigger_error($myqli->error."[$sql]")
    • 这也将是分裂查询到多条线路，使错误消息的更多信息是个好主意。


Answer 2:

我重新创建您的架构，并使用您的查询，一个从MySQL命令行和phpMyAdmin的其他插入两行。 截图是在这里： 。 您的查询似乎是有效的，所以这个问题出在其它地方（可能在你的PHP代码）。

这不是你的问题，但正如其他人所说，Message_userID的数据类型为int而你代表一个字符串报价有0。



Answer 3:

我终于想通了，我在做什么错。 我得到这个错误，因为我把实际值到VALUES()我的SQL的一部分。 这样，当我bind_param方法试图绑定值，它没有足够的? 标记绑定到，相反，它看到的实际值。

我的新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){
        $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(){}
}

谢谢大家对你的帮助！



文章来源: Why do I get a SQL error when preparing a statement in mysqli?
标签： php mysql mysqli prepared-statement
举报

收藏的人(0)

Ta的文章 更多文章
登录 后发表评论
0条评论
还没有人评论过~