PHP/MySQL Functions
PHP provides several built-in functions for interacting with MySQL databases. These functions allow developers to perform various database operations like connecting to a database, querying data, inserting, updating, and deleting records. There are two primary ways to interact with MySQL in PHP:
- MySQLi (MySQL Improved)
- PDO (PHP Data Objects)
1. MySQLi Functions (Procedural & Object-Oriented)
MySQLi functions are specifically designed for MySQL databases. MySQLi can be used in both procedural and object-oriented styles.
1.1 Connecting to MySQL (Procedural)
<?php
$servername = “localhost”;
$username = “root”;
$password = “”;
$database = “my_db”;
// Create a connection
$conn = mysqli_connect($servername, $username, $password, $database);
// Check connection
if (!$conn) {
die(“Connection failed: ” . mysqli_connect_error());
}
echo “Connected successfully”;
?>
1.2 Connecting to MySQL (Object-Oriented)
<?php
$conn = new mysqli(“localhost”, “root”, “”, “my_db”);
// Check connection
if ($conn->connect_error) {
die(“Connection failed: ” . $conn->connect_error);
}
echo “Connected successfully”;
?>
2. MySQL Query Functions
2.1 Performing SQL Queries (SELECT)
<?php
$sql = “SELECT id, name, email FROM users”;
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
while ($row = mysqli_fetch_assoc($result)) {
echo “ID: ” . $row[“id”] . ” – Name: ” . $row[“name”] . ” – Email: ” . $row[“email”] . “<br>”;
}
} else {
echo “No records found”;
}
?>
2.2 Fetching Data
- mysqli_fetch_assoc($result): Fetches a row as an associative array.
- mysqli_fetch_row($result): Fetches a row as a numeric array.
- mysqli_fetch_array($result): Fetches a row as both associative and numeric array.
- mysqli_fetch_object($result): Fetches a row as an object.
Example using mysqli_fetch_object():
<?php
$sql = “SELECT id, name FROM users”;
$result = mysqli_query($conn, $sql);
while ($row = mysqli_fetch_object($result)) {
echo “ID: ” . $row->id . ” – Name: ” . $row->name . “<br>”;
}
?>
3. Inserting Data into MySQL
<?php
$sql = “INSERT INTO users (name, email) VALUES (‘John Doe’, ‘john@example.com’)”;
if (mysqli_query($conn, $sql)) {
echo “New record created successfully”;
} else {
echo “Error: ” . mysqli_error($conn);
}
?>
4. Updating Data in MySQL
<?php
$sql = “UPDATE users SET email=’newemail@example.com’ WHERE id=1”;
if (mysqli_query($conn, $sql)) {
echo “Record updated successfully”;
} else {
echo “Error updating record: ” . mysqli_error($conn);
}
?>
5. Deleting Data from MySQL
php
CopyEdit
<?php
$sql = “DELETE FROM users WHERE id=1”;
if (mysqli_query($conn, $sql)) {
echo “Record deleted successfully”;
} else {
echo “Error deleting record: ” . mysqli_error($conn);
}
?>
6. Prepared Statements (Prevent SQL Injection)
Prepared statements prevent SQL injection by separating SQL commands from user input.
<?php
$stmt = $conn->prepare(“INSERT INTO users (name, email) VALUES (?, ?)”);
$stmt->bind_param(“ss”, $name, $email);
$name = “Alice”;
$email = “alice@example.com”;
$stmt->execute();
echo “New record inserted successfully”;
?>
7. Closing the MySQL Connection
It is important to close the database connection to free up resources.
<?php
mysqli_close($conn);
?>
8. PHP PDO (PHP Data Objects)
PDO provides a more flexible and secure way to connect to databases.
8.1 Connecting to MySQL using PDO
<?php
try {
$conn = new PDO(“mysql:host=localhost;dbname=my_db”, “root”, “”);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo “Connected successfully”;
} catch (PDOException $e) {
echo “Connection failed: ” . $e->getMessage();
}
?>
8.2 Executing Queries using PDO
<?php
$stmt = $conn->query(“SELECT * FROM users”);
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
echo “ID: ” . $row[“id”] . ” – Name: ” . $row[“name”] . “<br>”;
}
?>
8.3 Inserting Data with PDO
<?php
$stmt = $conn->prepare(“INSERT INTO users (name, email) VALUES (:name, :email)”);
$stmt->bindParam(‘:name’, $name);
$stmt->bindParam(‘:email’, $email);
$name = “John Doe”;
$email = “john@example.com”;
$stmt->execute();
?>
8.4 Updating Data with PDO
<?php
$stmt = $conn->prepare(“UPDATE users SET email=:email WHERE id=:id”);
$stmt->bindParam(‘:email’, $email);
$stmt->bindParam(‘:id’, $id);
$email = “newemail@example.com”;
$id = 1;
$stmt->execute();
?>
8.5 Deleting Data with PDO
<?php
$stmt = $conn->prepare(“DELETE FROM users WHERE id=:id”);
$stmt->bindParam(‘:id’, $id);
$id = 1;
$stmt->execute();
?>
Comparison: MySQLi vs PDO
| Feature | MySQLi | PDO |
| Database Support | Only MySQL | Supports multiple databases (MySQL, PostgreSQL, SQLite, etc.) |
| Prepared Statements | Yes | Yes |
| Object-Oriented | Yes | Yes |
| Security | Good | Excellent |
| Performance | Faster for MySQL | Slightly slower but flexible |
| Recommended for | MySQL-only projects | Multiple database support |
Conclusion
- Use MySQLi if working only with MySQL.
- Use PDO for better security, flexibility, and cross-database compatibility.
- Always use prepared statements to prevent SQL injection.
- Always close database connections to optimize performance.