i have try to use PDO MySQL in PHP with execute command. But i stuck with array in execute command, here code :
public static function join2ArAliasAndArValue($values=array(),$alias=array()){
$data = array();
for($i=0;$i<count($values);$i++){
$data[$alias[$i]] = $values[$i];
}
return $data;
}
always return :
Array ( [:id_val] => 01 [:name_val] => tatang [:phone_val] => 0989989 [:address_val] => kemanggisan [:idkey_val] => 100 )
and data must be like this :
Array (
':id_val' => '01' ,
':name_val' => 'tatang',
':phone_val' => '0989989',
':address_val' => 'kemanggisan',
':idkey_val' => '100'
)
how to create like that, remove square brackets and add comma every array value, some person helpme please..
and here my function addrecord :
public static function addRecordToTable($table,$fields=array(),$values=array(),$alias=array()){
$database = DatabaseFactory::getFactory()->getConnection();
$table = stripslashes(strtolower($table));
$sql = " INSERT INTO $table ";
$fields = implode("`, `", $fields);
$newalias = implode("', '", $alias);
$sql .= "(`$fields`) VALUES ('$newalias')";
$alias = explode(', ', $newalias);
$data = $data = Helpers::join2ArAliasAndArValue($values,$alias);
/** DEBUG */
Debug::debugInput('FIELDS',$fields);
Debug::debugInput('NEW-ALIAS',$newalias);
Debug::debugInput('SQL',$sql);
Debug::debugInput('ALIAS',$alias);
Debug::debugInput('DATA',$data);
$query = $database->prepare($sql);
$query->execute($data);
$output = $query->rowCount() == 1 ? true : false;
}
for debug :
public static function debugInput($title,$data){
$action = is_array($data) ? true : false;
if($action){
print $title . " : <b>"; print_r($data); print "</b><br />";
return false;
}
print $title . " : <b>" . $data; print "</b><br />";
}
__)
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, $newalias
is 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);
//...
}
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
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();
}
}
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;
}
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments