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:
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']);
}
?>
Messages
Send Message
Your Messages:
class.Messages.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 . '
';
return $result = call_user_func_array(array($this->database,'query'), refValues($queryParams));
}
public function deleteMessage(){}
}
class.Database.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:
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.
Answer
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:
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!
No comments:
Post a Comment