Understanding MySQLi query syntax is essential for effective database management and manipulation. This article explores the structure and syntax of MySQLi queries, covering connection setup, basic query operations, and best practices.
Setting Up a Connection with MySQLi
Before executing any MySQLi queries, you need to establish a connection to the MySQL database. This is the first step in working with MySQLi. Here is a basic example of how to connect to a MySQL database using MySQLi:
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database_name";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";
?>
In this code:
localhost is the server where the database is hosted.
username and password are the credentials to access the database.
database_name is the name of the database you want to connect to.
The new mysqli function initializes a new connection to the MySQL server, and the $conn->connect_error checks if the connection was successful.
Basic Query Operations
MySQLi supports a wide range of SQL operations. Here, we will discuss the basic operations: SELECT, INSERT, UPDATE, and DELETE.
SELECT Query
The SELECT statement is used to retrieve data from a database. The basic syntax of a SELECT query is:
SELECT column1, column2, ...
FROM table_name
WHERE condition;
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database_name";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// SQL SELECT query
$sql = "SELECT id, username, email FROM users WHERE username='john_doe'";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
echo "id: " . $row["id"]. " - Name: " . $row["username"]. " - Email: " . $row["email"]. "<br>";
}
} else {
echo "0 results";
}
$conn->close();
?>
INSERT Query
The INSERT statement is used to add new records to a table. The basic syntax of an INSERT query is:
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
Example of an INSERT query using MySQLi:
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database_name";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// SQL INSERT query
$sql = "INSERT INTO users (username, email, password) VALUES ('john_doe', 'john@example.com', 'securepassword')";
if ($conn->query($sql) === TRUE) {
echo "New record created successfully";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close();
?>
UPDATE Query
The UPDATE statement is used to modify existing records in a table. The basic syntax of an UPDATE query is:
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
Example of an UPDATE query using MySQLi:
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database_name";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// SQL UPDATE query
$sql = "UPDATE users SET email='john_new@example.com', password='newpassword' WHERE username='john_doe'";
if ($conn->query($sql) === TRUE) {
echo "Record updated successfully";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close();
?>
DELETE Query
The DELETE statement is used to remove existing records from a table. The basic syntax of a DELETE query is:
DELETE FROM table_name WHERE condition;
Example of a DELETE query using MySQLi:
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database_name";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// SQL DELETE query
$sql = "DELETE FROM users WHERE username='john_doe'";
if ($conn->query($sql) === TRUE) {
echo "Record deleted successfully";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close();
?>
Prepared Statements
Prepared statements are used to execute the same or similar SQL statements repeatedly with high efficiency. They are also safer than executing direct queries because they help prevent SQL injection attacks.
Prepared SELECT Query
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database_name";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Prepare and bind
$stmt = $conn->prepare("SELECT id, username, email FROM users WHERE username=?");
$stmt->bind_param("s", $username);
// Set parameters and execute
$username = "john_doe";
$stmt->execute();
// Bind result variables
$stmt->bind_result($id, $username, $email);
// Fetch values
while ($stmt->fetch()) {
echo "id: " . $id . " - Name: " . $username . " - Email: " . $email . "<br>";
}
$stmt->close();
$conn->close();
?>
Prepared INSERT Query
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database_name";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Prepare and bind
$stmt = $conn->prepare("INSERT INTO users (username, email, password) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $username, $email, $password);
// Set parameters and execute
$username = "jane_doe";
$email = "jane@example.com";
$password = "jane_secure_password";
$stmt->execute();
echo "New record created successfully";
$stmt->close();
$conn->close();
?>
Prepared UPDATE Query
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database_name";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Prepare and bind
$stmt = $conn->prepare("UPDATE users SET email=?, password=? WHERE username=?");
$stmt->bind_param("sss", $email, $password, $username);
// Set parameters and execute
$email = "jane_new@example.com";
$password = "new_jane_password";
$username = "jane_doe";
$stmt->execute();
echo "Record updated successfully";
$stmt->close();
$conn->close();
?>
Prepared DELETE Query
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database_name";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Prepare and bind
$stmt = $conn->prepare("DELETE FROM users WHERE username=?");
$stmt->bind_param("s", $username);
// Set parameters and execute
$username = "jane_doe";
$stmt->execute();
echo "Record deleted successfully";
$stmt->close();
$conn->close();
?>
Best Practices
1. Use Prepared Statements
Always use prepared statements to prevent SQL injection and to enhance performance, especially for repeated queries.
2. Validate and Sanitize Inputs
Always validate and sanitize user inputs to ensure data integrity and prevent security vulnerabilities.
3. Handle Errors Gracefully
Handle errors gracefully by providing meaningful messages to the users without exposing sensitive information. Use proper error logging mechanisms to keep track of issues.
4. Close Connections
Always close database connections to free up resources and prevent potential memory leaks.
5. Use Transactions
For complex operations involving multiple queries, use transactions to ensure data consistency and integrity.
Conclusion
Understanding the query syntax in MySQLi is crucial for effective database management. This article covered the basic MySQLi query operations, including SELECT, INSERT, UPDATE, and DELETE, along with the use of prepared statements for enhanced security and performance. By following the best practices outlined, you can write efficient and secure MySQLi queries for your PHP applications. The provided code examples serve as a practical guide to implementing these operations in real-world scenarios.