Tags
Asked 2 years ago
17 Jun 2021
Views 243
john

john posted

How can I insert multiple records in mysql using php ?

How can I insert multiple records in mysql using php
steave

steave
answered Apr 28 '23 00:00

To insert multiple records in MySQL using PHP, you can use the INSERT INTO statement with multiple VALUES clauses, each containing a set of values to be inserted. Here's an example:



// Connect to the database
$conn = mysqli_connect("localhost", "username", "password", "database");

// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

// Define the values to be inserted
$values = array(
    array('John', 'Doe', 25),
    array('Jane', 'Smith', 30),
    array('Bob', 'Johnson', 40)
);

// Build the query
$query = "INSERT INTO mytable (firstname, lastname, age) VALUES ";

foreach ($values as $row) {
    $query .= "(?, ?, ?),";
}

// Remove the last comma from the query string
$query = rtrim($query, ",");

// Prepare the statement
$stmt = mysqli_prepare($conn, $query);

// Bind the parameters
mysqli_stmt_bind_param($stmt, 'ssi', $firstname, $lastname, $age);

// Iterate over the values and execute the statement
foreach ($values as $row) {
    $firstname = $row[0];
    $lastname = $row[1];
    $age = $row[2];

    if (!mysqli_stmt_execute($stmt)) {
        echo "Error: " . mysqli_stmt_error($stmt);
    }
}

// Close the statement and database connection
mysqli_stmt_close($stmt);
mysqli_close($conn);

In this example, we first connect to the database using the mysqli_connect () function. Then, we define an array of arrays that contains the values to be inserted.

Next, we build the INSERT INTO statement by iterating over the values array, and appending a parameterized placeholder ? for each value to the query string using string concatenation.

We then prepare the statement using mysqli_prepare () and bind the parameters using mysqli_stmt_bind_param (). The second argument to mysqli_stmt_bind_param() specifies the data types of the parameters.

Finally, we iterate over the values array and execute the statement for each set of values using mysqli_stmt_execute (). If there's an error, we print the error message using mysqli_stmt_error ().

Note that using parameterized queries with bound parameters helps prevent SQL injection attacks by ensuring that user input is properly sanitized. This can help protect your application from malicious attacks.
Post Answer