How to insert 3 php Array values as 3 MYSQL Columns

Question

I need to insert array values in php as Columns in a MYSQL table.

$new_user = array(

        "Nom"  => $_POST['Nom'],

        "EmailID"     => $_POST['EmailID'],

        "localité"  => $_POST['localité']
    );

    $table = "memfis";
    $columnNames = implode(", ", array_keys($new_user));
    $columnPlaceholders = "'" . implode("', '", array_keys($new_user)) . "'";
    $sqld = "INSERT INTO $table ($columnNames) VALUES ($columnPlaceholders);";
    var_dump($sqld); exit;
    $stmt = $pdo->prepare($sqld);
    foreach ($columnNames as $name) {
    $placeholder = "'" . $name;
    $stmt->bindParam($placeholder, $new_user[$name]);
    } 
    $connection->execute($sqld);
     echo "New record created successfully";

It should have displayed " New row added succesfully " and the row should have been added in the table.

I think the issue may well be caused by the accent on the e : ie: localité — Professor Abronsius
– Professor Abronsius, Commented May 26, 2019 at 9:26

Professor Abronsius · Accepted Answer · 2019-05-26 09:32:09Z

I tested the following with the original field names - specifically making note of the acute accent on the e and it failed - when I removed the accent and replaced with a standard letter e it worked fine

<?php


    /* PDO dbo */
    $dbport =   3306;
    $dbhost =   'localhost';
    $dbuser =   'root';
    $dbpwd  =   'xxx';
    $dbname =   'xxx';

    $options=array( 
        PDO::ATTR_CURSOR                    =>  PDO::CURSOR_SCROLL,
        PDO::ATTR_PERSISTENT                =>  false,
        PDO::MYSQL_ATTR_USE_BUFFERED_QUERY  =>  true,
        PDO::ATTR_EMULATE_PREPARES          =>  true,
        PDO::MYSQL_ATTR_INIT_COMMAND        =>  'SET NAMES \'utf8mb4\' COLLATE \'utf8mb4_general_ci\', @@sql_mode = STRICT_ALL_TABLES, @@foreign_key_checks = 1'
    );
    $dsn = 'mysql:host='.$dbhost.';port='.$dbport.';dbname='.$dbname.';charset=UTF8';
    $db = new PDO( $dsn, $dbuser, $dbpwd, $options );







    /* Emulate POST form submission */
    $_POST=[
        'Nom'       =>  'fred flintstone',
        'EmailID'   =>  '[email protected]',
        'localite'  =>  'Bedrock'
    ];


    $table='memfis';

    /* prepare field names */
    $fields = sprintf( '`%s`', implode( '`,`', array_keys( $_POST ) ) );

    /* placeholder arrays */
    $args = [];
    $vals = [];

    /* create placeholder variable names */
    foreach( $_POST as $field => $value ) {
        $args[]=sprintf( ':%s', strtolower( $field ) );
        $vals[ sprintf( ':%s', strtolower( $field ) ) ]=$value;
    }

    /* create the sql statement */
    $sql=sprintf(
        'insert into `%s` ( %s ) values ( %s );',
        $table,
        $fields,
        implode( ', ', $args )
    );


    $stmt = $db->prepare( $sql );
    if( $stmt ){

        # debug info
        printf("<pre>%s\n%s</pre>", $sql, print_r( $vals, true ) );

        # execute the statement
        $res=$stmt->execute( $vals );

        # did it or did it not work?
        if( $res ){
            echo "OK!";
        } else {
            echo "Bogus!";
        }       
    }
?>

The resultant output to screen of the above is:

insert into `memfis` ( `Nom`,`EmailID`,`localite` ) values ( :nom, :emailid, :localite );
Array
(
    [:nom] => fred flintstone
    [:emailid] => [email protected]
    [:localite] => Bedrock
)
OK!

When using the original fieldname localité the following error occurs:

Warning: PDOStatement::execute() [pdostatement.execute.html]: SQLSTATE[HY093]: Invalid parameter number: parameter was not defined in .....

Collectives™ on Stack Overflow

How to insert 3 php Array values as 3 MYSQL Columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related