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, Nov 14 '19 at 05: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, Nov 14 '19 at 05: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, Nov 14 '19 at 05: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, Nov 14 '19 at 06:29
Hi Nick, The array would be consists of 8000 rows, and basically we are developing a monitoring tool which is monitor the server status(8000+) and update the status in a table in Mysql using PHP, so the "statement Type = IF(Type != VALUES(Type), VALUES(Type), '$ty')" , do not update the field Type is there is no changes , in case of any changes then update. — Vino, Nov 14 '19 at 09:47

Lets-c-codeigniter · Accepted Answer · 2019-11-14T06:07:00.793

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 '19 at 06:07

answered Nov 14 '19 at 05:50

Lets-c-codeigniter

682
2
5
18

1

Sorry, that was a typo mistake – Vino Nov 14 '19 at 05:54
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 – Vino Nov 14 '19 at 06:00
Multiple Values Insert with PDO Prepared Statements [link](https://stackoverflow.com/a/2098689/7552716) – Tabrosh Shaikh Nov 14 '19 at 06:32

PHP Array of Array insert Mysql

1 Answers1