CODEWITHSUNDEEP
phpmysqlsqlarrayssql-insert
user5405873
user5405873

Reputation:

How to insert multiple rows at once in a table with array of key value pair data

I want to insert array() key value pair data into a table

<?php
$foreignKey = 2;
$array = array(
   'availability' => array(
       array('day' => 'monday','time' => 'am'),
       array('day' => 'wednesday','time' => 'pm'),    
   ),
);

My availability table - in the beginning

table: availability

| id  |  foreign_id  |   day  |  time  |
+-----+--------------+--------+--------+

resultant table:

| id  |  foreign_id  |   day    |  time  |
+-----+--------------+----------+--------+
|  1  |       2      | monday   |  am    |
+-----+--------------+----------+--------+
|  2  |       2      |wednesday |  pm    |
+-----+--------------+----------+--------+


$sql = "INSERT INTO availability ";

Upvotes: 0

Views: 197

Answers (2)

Ali Najafi
Ali Najafi

Reputation: 11

Here's how you can insert multiple rows into the availability table using PHP and prepared statements:

<?php
$foreignKey = 2;
$array = array(
    'availability' => array(
        array('day' => 'monday', 'time' => 'am'),
        array('day' => 'wednesday', 'time' => 'pm'),
    ),
);

// Database connection parameters
$servername = "your_servername";
$username = "your_username";
$password = "your_password";
$dbname = "your_dbname";

try {
    // Connect to database
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    // Set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Prepare SQL statement
    $sql = "INSERT INTO availability (foreign_id, day, time) VALUES ";
    $values = array();

    foreach ($array['availability'] as $item) {
        $values[] = "(:foreign_id, :day, :time)";
    }

    $sql .= implode(", ", $values);

    $stmt = $conn->prepare($sql);

    // Bind parameters and execute the statement for each row
    foreach ($array['availability'] as $item) {
        $stmt->bindParam(':foreign_id', $foreignKey);
        $stmt->bindParam(':day', $item['day']);
        $stmt->bindParam(':time', $item['time']);
        $stmt->execute();
    }

    echo "Records inserted successfully";

} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}

// Close connection
$conn = null;
?>

Explanation: Database Connection:

Replace 'your_servername', 'your_username', 'your_password', and 'your_dbname' with your actual database connection details. Prepared Statement and SQL Construction:

Construct the initial part of the SQL query: INSERT INTO availability (foreign_id, day, time) VALUES. Prepare an array $values to store placeholders for each row to be inserted. Loop through Array Data:

Iterate through each element in $array['availability']. For each element, add (:foreign_id, :day, :time) to the $values array. Prepare and Execute the SQL Statement:

Prepare the SQL statement using $sql. Prepare the statement with $conn->prepare($sql). For each row in $array['availability']: Bind the parameters (:foreign_id, :day, :time) with the actual values. Execute the statement. Exception Handling:

Use a try-catch block to catch any exceptions that may occur during database operations. If an exception is thrown, it will be caught and an error message will be displayed. Output:

If the records are inserted successfully, it will echo "Records inserted successfully". Notes: This code assumes you are using PDO for database operations, which is a good practice for database interactions in PHP due to its security and ease of use. Ensure to replace the database connection parameters ($servername, $username, $password, $dbname) with your actual database credentials. The foreach loop is used to insert each row from the $array['availability'] array into the database table.

Upvotes: 0

Ravi
Ravi

Reputation: 31417

You could loop through your array and bind and execute

$stmt->prepare("INSERT INTO availability (fld1, fld2) VALUES(?, ?)");
foreach($array as $row)
{
    $stmt->bind_param($row['fld1'], $row['fld2']);
    $stmt->execute();
}

Upvotes: 2

Related Questions