array for PDO MySQL function

ikwijaya

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 />";
}

__)

user7941334

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;
}

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

Change date format for array to MySQL format using PDO

From Dev

Selecting MySql table data into an array using PDO class?

From Dev

translation mysql_fetch_array to PDO::FETCH_NUM

From Dev

How to use a MySQL user function with PDO in PHP?

From Dev

Error fetching MySQL array using PDO

From Dev

PDO: Inserting array in MySQL with Incremental Keys

From Dev

What is the exact equivalent of mysqli_fetch_array function in PDO?

From Dev

Trouble Creating a MySQL Function through PDO

From Dev

PDO equivalent of mysql_fetch_array

From Dev

Escape Array of Strings for IN Statement PDO MYSQL

From Dev

pdo fetchAll function returns an empty array

From Dev

Same array multiple times in MySQL "IN" query with PDO

From Dev

PHP MySQL PDO selecting rows on array with id's

From Dev

JS array to PHP and update table in MYSQL with PDO

From Dev

INSERT INTO array with MySQL and PDO

From Dev

PDO MySQL backups function

From Dev

Create array from MySQL PDO query results

From Dev

translation mysql_fetch_array to PDO::FETCH_NUM

From Dev

PDO: Inserting array in MySQL with Incremental Keys

From Dev

mySQL responses "array" at PHP page (PDO)

From Dev

PDO UPDATE array using php mysql

From Dev

PHP PDO MYSQL - Get Array comma separated from fetchAll

From Dev

Beginner Help: PHP MySQL (PDO) Function

From Dev

MySQL Select with function IN () with bash array

From Dev

Online input to a PHP function that queries (via PDO) MySql outputting JSON

From Dev

PHP PDO MySQL function doesn't edit data, query works

From Dev

PDO mysql LIMIT with placeholder in an array

From Dev

JS array to PHP and update table in MYSQL with PDO

From Dev

PHP PDO: Search array value inside of MySQL's JSON object

Related Related

HotTag

Archive