在mysqli中准备语句时

在mysqli中准备语句时

关注
发信
关注(28)粉丝(399)

在mysqli中准备语句时，为什么会出现SQL错误?

本文介绍了在mysqli中准备语句时，为什么会出现SQL错误?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有以下查询:

INSERT INTO ipi_messages (Message_userID, Message_fromName, Message_fromEmail, Message_subject, Message_body) VALUES(0, 'hope', '[email protected]', 'i hope', 'this works')

我收到以下MySQL错误:

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

这是我的表架构:

这是我的主要PHP代码:

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:

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:

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:

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成为整数而不是字符串.

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.

为什么在准备mysqli语句时出现SQL错误?我尝试只接受SQL查询并在数据库上运行它，然后插入行就很好了.

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.

推荐答案

我终于弄清楚我在做什么错.我收到该错误是因为我将实际值放入了sql的VALUES()部分.这样，当我的bind_param方法试图绑定值时，它没有要绑定的?标记，而是看到了实际值.

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.

我的新类.Messages.php看起来像:

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(){}
}

感谢大家的帮助！

这篇关于在mysqli中准备语句时，为什么会出现SQL错误?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

08-13 13:12
Powered by LMLPHP ©2024 在mysqli中准备语句时 0.038624