Wednesday 28 September 2016

php - Why do I get a SQL error when preparing a statement in mysqli?



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:




enter image description here



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



To:




Name:


Email:


Subject:



Message:







Your Messages:






if (isset($result_get)) {
foreach ($result_get as $result) {
?>








}
}
?>






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

c++ - Does curly brackets matter for empty constructor?

Those brackets declare an empty, inline constructor. In that case, with them, the constructor does exist, it merely does nothing more than t...