PHP MySQLi
Connect and interact with MySQL databases
🗄️ What is PHP MySQLi?
MySQLi (MySQL Improved) is a PHP extension that allows you to connect and interact with MySQL databases. It provides both procedural and object-oriented interfaces for database operations.
<?php
// Simple database connection
$conn = new mysqli("localhost", "username", "password", "database");
echo "Connected successfully!";
?>
Key MySQLi Features
Connection
Connect to MySQL databases
$conn = new mysqli($host, $user, $pass, $db);Queries
Execute SQL statements
$result = $conn->query("SELECT * FROM users");Prepared Statements
Secure SQL execution
$stmt = $conn->prepare("SELECT * FROM users WHERE id=?");Fetch Data
Retrieve query results
$row = $result->fetch_assoc();🔹 Connecting to Database
Establish a connection to your MySQL database using MySQLi. Always check for connection errors to ensure your application handles failures gracefully.
<?php
// Database credentials
$servername = "localhost";
$username = "root";
$password = "";
$database = "myDB";
// Create connection
$conn = new mysqli($servername, $username, $password, $database);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";
?>Output:
Connected successfully
🔹 Selecting Data
Retrieve data from your database tables using SELECT queries. Use fetch methods to access the returned rows and display information.
<?php
$sql = "SELECT id, name, email FROM users";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
// Output data of each row
while($row = $result->fetch_assoc()) {
echo "ID: " . $row["id"] . " - Name: " . $row["name"] . " - Email: " . $row["email"] . "<br>";
}
} else {
echo "0 results";
}
?>Output:
ID: 1 - Name: John Doe - Email: [email protected]
ID: 2 - Name: Jane Smith - Email: [email protected]
🔹 Inserting Data
Add new records to your database tables using INSERT statements. Always validate and sanitize user input before inserting data.
<?php
$sql = "INSERT INTO users (name, email) VALUES ('Mike Johnson', '[email protected]')";
if ($conn->query($sql) === TRUE) {
echo "New record created successfully";
echo "<br>Last inserted ID: " . $conn->insert_id;
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
?>Output:
New record created successfully
Last inserted ID: 3
🔹 Prepared Statements (Secure)
Use prepared statements to prevent SQL injection attacks. This method separates SQL logic from data, making your queries much safer against malicious input.
<?php
// Prepare statement
$stmt = $conn->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
$stmt->bind_param("ss", $name, $email);
// Set parameters and execute
$name = "Sarah Williams";
$email = "[email protected]";
$stmt->execute();
echo "New record created successfully";
$stmt->close();
?>Output:
New record created successfully
🔹 Updating Data
Modify existing records in your database using UPDATE statements with WHERE clauses to target specific rows.
<?php
$sql = "UPDATE users SET email='[email protected]' WHERE id=1";
if ($conn->query($sql) === TRUE) {
echo "Record updated successfully";
} else {
echo "Error updating record: " . $conn->error;
}
?>Output:
Record updated successfully
🔹 Deleting Data
Remove records from your database using DELETE statements. Be careful with DELETE operations as they permanently remove data.
<?php
$sql = "DELETE FROM users WHERE id=3";
if ($conn->query($sql) === TRUE) {
echo "Record deleted successfully";
} else {
echo "Error deleting record: " . $conn->error;
}
// Close connection
$conn->close();
?>Output:
Record deleted successfully