PHP Array of Array insert Mysql

Question

Request your help on how to insert a PHP array of type below into Mysql with single query by avoiding the foreach loop as below query.

What would be the performance gain in we use a single query to insert vs the below query(foreach).

PHP Array

(
    [0] => Array
        (
            [Name] => NNN1
            [Type] => TTT1
        )

    [1] => Array
        (
            [Name] => NNN2
            [Type] => TTT2
        )

Query:

foreach ($array as $i) {
    $na =  $item["Name"]; 
    $ty = $item["Type"]; 
    $db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName);
    $dc = $db->prepare("INSERT INTO allocation (`Name`, `Type`) VALUES (?, ?) ON DUPLICATE KEY UPDATE Type = IF(Type != VALUES(Type), VALUES(Type), '$ty')");
    $dc->bind_param("ss", $na, $ty);
    $dc->execute();
    $dc->execute();
    $dc->close();
}

Although still using foreach, you can move new mysqli() and $db->prepare before the loop. And remove the second $dc->execute() — Carl Binalla
– Carl Binalla, Commented Nov 14, 2019 at 5:27
Hi Carl, as suggested if we make the changes then will the data be inserted in one go(single SQL connection) or would it required more connection. — Vino
– Vino, Commented Nov 14, 2019 at 5:41
If we change the code as suggested by you we are getting the below errors mysqli_stmt::bind_param(): Couldn't fetch mysqli_stmt mysqli_stmt::execute(): Couldn't fetch mysqli_stmt mysqli_stmt::close(): Couldn't fetch mysqli_stmt — Vino
– Vino, Commented Nov 14, 2019 at 5:54
Your update clause Type = IF(Type != VALUES(Type), VALUES(Type), '$ty') doesn't make any sense as VALUES(Type) will be the same as $ty, so you may as well just write Type = VALUES(Type) — Nick
– Nick, Commented Nov 14, 2019 at 6:29

Lets-c-codeigniter · Accepted Answer · 2019-11-14 06:07:00Z

2

I think you are using wrong variables. In your loop $i should be $item

Keep Database connection code out of the loop

$db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName);

foreach ($array as $item) // $i should be $item
{
    $na =  $item["Name"]; 
    $ty = $item["Type"];  
    $dc = $db->prepare("INSERT INTO allocation (`Name`, `Type`) VALUES (?, ?) ON DUPLICATE KEY UPDATE Type = IF(Type != VALUES(Type), VALUES(Type), '$ty')");
    $dc->bind_param("ss", $na, $ty);
    $dc->execute();
    $dc->execute();
}
$dc->close();

edited Nov 14, 2019 at 6:07

answered Nov 14, 2019 at 5:50

Lets-c-codeigniter

7142 gold badges7 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Vino Over a year ago

Sorry, that was a typo mistake

Vino Over a year ago

Yes it is working, but i want to way to insert the data by avoiding foreach loop as any operation within the loop will require many connections(length of the array), hence trying to find a way to avoid such issues

Tabrosh Shaikh Over a year ago

Multiple Values Insert with PDO Prepared Statements link

Collectives™ on Stack Overflow

PHP Array of Array insert Mysql

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related