False function:
public static function addRecordToTable($table, $fields = array(), $values = array(), $alias = array()) {
//...
$sql = " INSERT INTO $table ";
$fields = implode("`, `", $fields);
$newalias = implode("', '", $alias); // GOOD!!!: => $newalias = "alias1', 'alias2', 'alias3"
$sql .= "(`$fields`) VALUES ('$newalias')";
$alias = explode(', ', $newalias); // FALSE VALUES!!!: => $alias = array(alias1', 'alias2', 'alias3)
$data = $data = Helpers::join2ArAliasAndArValue($values, $alias);
//...
}
So, $newaliasis CORRECT (see comments in the code), because in the form with the single quotes must be inserted into the INSERT sql statement!
The $alias is used INCORRECT (see comments in the code). So, just delete
$alias = explode(', ', $newalias);
Because you don't need to implode $alias to $newalias and then explode this one to a new $alias again.
So, correct function:
public static function addRecordToTable($table, $fields = array(), $values = array(), $alias = array()) {
//...
$sql = " INSERT INTO $table ";
$fields = implode("`, `", $fields);
$newalias = implode("', '", $alias);
$sql .= "(`$fields`) VALUES ('$newalias')";
$data = $data = Helpers::join2ArAliasAndArValue($values, $alias);
//...
}
EDIT 2:
I tried to refactor your code in order to give you a picture of bringing some handling strategies together. Please read the code comments for details. I would recommend
- to use exception handling, in order to be able to always discover the errors raised by failed database operations (and not just that). You can see an older answer of me, if you wish:
Exception handling for PDO::prepare() and PDOStatement::execute() + A generalized exception handling scheme
- to use
sprintf() when building complex strings like sql statements (but don't abuse their use).
Here is the addRecordToTable() function as I see it:
public static function addRecordToTable($table, $fields = array(), $values = array(), $alias = array()) {
// Use try-catch blocks for exception handling.
try {
$database = DatabaseFactory::getFactory()->getConnection();
/*
* Build your sql statement using sprintf()
* and placeholders (defined through "%s").
* See: http://php.net/manual/en/function.sprintf.php
*/
$sql = sprintf(
" INSERT INTO %s (`%s`) VALUES ('%s')"
, stripslashes(strtolower($table))
, implode("`, `", $fields)
, implode("', '", $alias)
);
// I corrected here also, because you had "$data = $data = ...".
$data = Helpers::join2ArAliasAndArValue($values, $alias);
$query = $database->prepare($sql);
// Added this validation.
if (!$query) {
throw new Exception('The SQL statement can not be prepared!');
}
$executed = $query->execute($data);
// Added this validation.
if (!$executed) {
throw new Exception('The PDO statement can not be executed!');
}
$output = $query->rowCount() == 1 ? true : false;
/*
* Corrected (e.g. added) here also, because you
* have to return the results, e.g. the $output.
*/
return $output;
} catch (PDOException $pdoException) {
echo '<pre>' . print_r($pdoException, true) . '</pre>';
exit();
} catch (Exception $exception) {
echo '<pre>' . print_r($exception, true) . '</pre>';
exit();
}
}
EDIT 3:
Using PDOStatement::bindValue (or PDOStatement::bindParam) to prepare an sql statement. A general example:
//...
$sql = 'INSERT INTO demo_table (id, name) VALUES (:id, :name)';
$statement = $connection->prepare($sql);
if (!$statement) {
throw new Exception('The SQL statement can not be prepared!');
}
// Integer binding ":id".
$statement->bindValue(':id', $id, $this->getInputParameterDataType($id));
// String binding ":name".
$statement->bindValue(':name', $name, $this->getInputParameterDataType($name));
//...
function getInputParameterDataType($value) {
$dataType = PDO::PARAM_STR;
if (is_int($value)) {
$dataType = PDO::PARAM_INT;
} elseif (is_bool($value)) {
$dataType = PDO::PARAM_BOOL;
}
return $dataType;
}
