This article explores how to perform basic INSERT operations using MySQLi, covering both the object-oriented and procedural approaches. We will also discuss the importance of prepared statements for secure data insertion.
Prerequisites
Before we begin, ensure you have the following:
1. A web server with PHP installed (such as Apache or Nginx).
2. MySQL server installed and running.
3. Basic knowledge of PHP and MySQL.
4. A sample database and table to work with.
For demonstration purposes, let’s assume we have a database named test_db with a table users:
CREATE DATABASE test_db;
USE test_db;
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
Basic INSERT Queries Using MySQLi
1. Object-Oriented Approach
Step 1: Establish a Connection
First, establish a connection to the MySQL database. Create a config.php file to store your database credentials:
<?php
// config.php
define('DB_SERVER', 'localhost');
define('DB_USERNAME', 'root');
define('DB_PASSWORD', '');
define('DB_NAME', 'test_db');
?>
Include this configuration file and create a connection in your script:
<?php
require_once 'config.php';
$mysqli = new mysqli(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_NAME);
if ($mysqli->connect_error) {
die("Connection failed: " . $mysqli->connect_error);
}
?>
Step 2: Perform a Basic INSERT Query
To insert data into the users table, use the following code:
<?php
require_once 'config.php';
$mysqli = new mysqli(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_NAME);
if ($mysqli->connect_error) {
die("Connection failed: " . $mysqli->connect_error);
}
$username = 'john_doe';
$email = 'john@example.com';
$sql = "INSERT INTO users (username, email) VALUES ('$username', '$email')";
if ($mysqli->query($sql) === TRUE) {
echo "New record created successfully";
} else {
echo "Error: " . $sql . "<br>" . $mysqli->error;
}
$mysqli->close();
?>
Explanation
$sql: The SQL query string for inserting data.
$mysqli->query($sql): Executes the query.
$mysqli->error: Returns the error description if the query fails.
2. Procedural Approach
Step 1: Establish a Connection
Create a connection using mysqli_connect():
<?php
require_once 'config.php';
$conn = mysqli_connect(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_NAME);
if ($conn === false) {
die("ERROR: Could not connect. " . mysqli_connect_error());
}
?>
Step 2: Perform a Basic INSERT Query
Use mysqli_query() to execute the query:
<?php
require_once 'config.php';
$conn = mysqli_connect(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_NAME);
if ($conn === false) {
die("ERROR: Could not connect. " . mysqli_connect_error());
}
$username = 'jane_doe';
$email = 'jane@example.com';
$sql = "INSERT INTO users (username, email) VALUES ('$username', '$email')";
if (mysqli_query($conn, $sql)) {
echo "New record created successfully";
} else {
echo "ERROR: Could not execute $sql. " . mysqli_error($conn);
}
mysqli_close($conn);
?>
3. Using Prepared Statements
Prepared statements are essential for preventing SQL injection, especially when dealing with user input.
Object-Oriented Approach
<?php
require_once 'config.php';
$mysqli = new mysqli(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_NAME);
if ($mysqli->connect_error) {
die("Connection failed: " . $mysqli->connect_error);
}
$stmt = $mysqli->prepare("INSERT INTO users (username, email) VALUES (?, ?)");
$stmt->bind_param("ss", $username, $email);
$username = 'john_doe';
$email = 'john@example.com';
$stmt->execute();
if ($stmt->affected_rows > 0) {
echo "New record created successfully";
} else {
echo "Error: " . $stmt->error;
}
$stmt->close();
$mysqli->close();
?>
Procedural Approach
<?php
require_once 'config.php';
$conn = mysqli_connect(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_NAME);
if ($conn === false) {
die("ERROR: Could not connect. " . mysqli_connect_error());
}
$sql = "INSERT INTO users (username, email) VALUES (?, ?)";
$stmt = mysqli_prepare($conn, $sql);
mysqli_stmt_bind_param($stmt, "ss", $username, $email);
$username = 'jane_doe';
$email = 'jane@example.com';
mysqli_stmt_execute($stmt);
if (mysqli_stmt_affected_rows($stmt) > 0) {
echo "New record created successfully";
} else {
echo "ERROR: Could not execute query. " . mysqli_stmt_error($stmt);
}
mysqli_stmt_close($stmt);
mysqli_close($conn);
?>
Best Practices for INSERT Queries
Use Prepared Statements: Always use prepared statements to protect against SQL injection.
Check Connection Errors: Ensure your script properly handles connection errors.
Close Connections: Always close your database connections to free up resources.
Validate and Sanitize Inputs: Always validate and sanitize user inputs before using them in your queries.
Inserting Multiple Rows
You can insert multiple rows in a single query by using a multi-row INSERT statement.
Object-Oriented Approach
<?php
require_once 'config.php';
$mysqli = new mysqli(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_NAME);
if ($mysqli->connect_error) {
die("Connection failed: " . $mysqli->connect_error);
}
$sql = "INSERT INTO users (username, email) VALUES
('john_doe', 'john@example.com'),
('jane_doe', 'jane@example.com')";
if ($mysqli->query($sql) === TRUE) {
echo "New records created successfully";
} else {
echo "Error: " . $sql . "<br>" . $mysqli->error;
}
$mysqli->close();
?>
Procedural Approach
<?php
require_once 'config.php';
$conn = mysqli_connect(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_NAME);
if ($conn === false) {
die("ERROR: Could not connect. " . mysqli_connect_error());
}
$sql = "INSERT INTO users (username, email) VALUES
('john_doe', 'john@example.com'),
('jane_doe', 'jane@example.com')";
if (mysqli_query($conn, $sql)) {
echo "New records created successfully";
} else {
echo "ERROR: Could not execute $sql. " . mysqli_error($conn);
}
mysqli_close($conn);
?>
Conclusion
Performing INSERT operations in MySQLi is a fundamental task in PHP development. By following the examples and best practices outlined in this article, you can efficiently and securely insert data into your MySQL database. Whether you prefer the object-oriented or procedural approach, MySQLi provides the tools you need to interact with your database effectively. Remember to use prepared statements to safeguard your application against SQL injection and always handle connection errors gracefully. Additionally, closing your database connections and validating user inputs are essential practices for maintaining the performance and security of your application.