YouTip

* * * ## Inserting Multiple Records into MySQL Using MySQLi and PDO The `mysqli_multi_query()` function can be used to execute multiple SQL statements. The following example adds three new records to the "MyGuests" table: ## Example (MySQLi - Object-Oriented) ```php connect_error) { die("Connection failed: " . $conn->connect_error); } $sql = "INSERT INTO MyGuests (firstname, lastname, email) VALUES ('John', 'Doe', 'john@example.com');"; $sql .= "INSERT INTO MyGuests (firstname, lastname, email) VALUES ('Mary', 'Moe', 'mary@example.com');"; $sql .= "INSERT INTO MyGuests (firstname, lastname, email) VALUES ('Julie', 'Dooley', 'julie@example.com')"; if ($conn->multi_query($sql) === TRUE) { echo "New records inserted successfully"; } else { echo "Error: " . $sql . "
" . $conn->error; } $conn->close(); ?> |  | Please note that each SQL statement must be separated by a semicolon. | | --- | ## Example (MySQLi - Procedural) ```php <?php $servername = "localhost"; $username = "username"; $password = "password"; $dbname = "myDB"; $conn = mysqli_connect($servername, $username, $password, $dbname); if (!$conn) { die("Connection failed: " . mysqli_connect_error()); } $sql = "INSERT INTO MyGuests (firstname, lastname, email) VALUES ('John', 'Doe', 'john@example.com');"; $sql .= "INSERT INTO MyGuests (firstname, lastname, email) VALUES ('Mary', 'Moe', 'mary@example.com');"; $sql .= "INSERT INTO MyGuests (firstname, lastname, email) VALUES ('Julie', 'Dooley', 'julie@example.com')"; if (mysqli_multi_query($conn, $sql)) { echo "New records inserted successfully"; } else { echo "Error: " . $sql . "
" . mysqli_error($conn); } mysqli_close($conn); ?> ## Example (PDO) ```php setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $conn->beginTransaction(); $conn->exec("INSERT INTO MyGuests (firstname, lastname, email) VALUES ('John', 'Doe', 'john@example.com')"); $conn->exec("INSERT INTO MyGuests (firstname, lastname, email) VALUES ('Mary', 'Moe', 'mary@example.com')"); $conn->exec("INSERT INTO MyGuests (firstname, lastname, email) VALUES ('Julie', 'Dooley', 'julie@example.com')"); $conn->commit(); echo "New records inserted successfully"; } catch (PDOException $e) { $conn->rollBack(); echo $sql . "
" . $e->getMessage(); } $conn = null; ?> * * * ## Using Prepared Statements The MySQLi extension provides another way to insert data into the database. We can prepare statements and bind parameters. With the MySQL extension, you can send statements or queries to the MySQL database without including any data. You can associate or "bind" variables with columns. ## Example (MySQLi Using Prepared Statements) ```php connect_error) { die("Connection failed: " . $conn->connect_error); } else { $sql = "INSERT INTO MyGuests(firstname, lastname, email) VALUES(?, ?, ?)"; $stmt = mysqli_stmt_init($conn); if (mysqli_stmt_prepare($stmt, $sql)) { mysqli_stmt_bind_param($stmt, 'sss', $firstname, $lastname, $email); $firstname = 'John'; $lastname = 'Doe'; $email = 'john@example.com'; mysqli_stmt_execute($stmt); $firstname = 'Mary'; $lastname = 'Moe'; $email = 'mary@example.com'; mysqli_stmt_execute($stmt); $firstname = 'Julie'; $lastname = 'Dooley'; $email = 'julie@example.com'; mysqli_stmt_execute($stmt); } } ?> As we can see in the above example, modularization is used to handle the problem. By creating code blocks, we make it easier to read and manage. Pay attention to how the parameters are bound. Let's look at the code in `mysqli_stmt_bind_param()`: ```php mysqli_stmt_bind_param($stmt, 'sss', $firstname, $lastname, $email); This function binds the parameters of the query and passes them to the database. The second parameter is `'sss'`. The following list shows the types of parameters: * `i` - integer * `d` - double-precision floating-point number * `s` - string * `b` - binary BLOB storage object Each parameter must specify its type to ensure data security. Specifying the type helps reduce the risk of SQL injection vulnerabilities.