在mysqli中准备语句时，为什么会出现SQL错误? [英] Why do I get a SQL error when preparing a statement in mysqli?
本文介绍了在mysqli中准备语句时，为什么会出现SQL错误?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有以下查询:
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错误:
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错误?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
