PHP offer to establish connection to the popular databases. Here, PHP can integrate MySQL database easily for querying data from database.
MySQL is a popular and open-source relational database management system. It is a reliable, efficient, and flexible database that widely used with PHP to handle a wide range of applications.
Common things for MySQL is Preferable with PHP:
- Community Support: It has an active and large community to provide supports and resources.
- Ease of Use: It is relatively easy to learn and use.
- Open-Source: It is freely available and it can be customized to fit specific requirements.
- Performance: It is known for high performance and scalability.
- Security: It has robust security features to protect sensitive data.
Learn MySQL basics from this MySQL Notes repository.
To connect to a MySQL database in PHP, use either the MySQLi
extension or PDO
(PHP Data Objects).
- MySQLi:
// Database credentials
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "test";
// Establish database connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";
// Close the database connection after end of the script
$conn->close();
- PDO:
// Database credentials
$dsn = "mysql:host=localhost;dbname=test";
$username = "username";
$password = "password";
// Establish database connection
try {
$pdo = new PDO($dsn, $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connected successfully";
} catch (PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}
- MySQLi:
// SQL Query
$sql = "SELECT * FROM staff";
// Executing Query
$result = $conn->query($sql);
// Checking for results
if ($result->num_rows > 0) {
// output the result of each row
while($row = $result->fetch_assoc()) {
echo "staff_id: " . $row["staff_id"]. " - First Name: " . $row["first_name"] . "<br>";
}
} else {
echo "No record found";
}
- PDO:
// SQL Query
$sql = "SELECT * FROM staff";
$stmt = $pdo->prepare($sql);
// Executing Query
$stmt->execute();
// Fetching results
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
// output the result of each row
foreach ($result as $row) {
echo "staff_id: " . $row["staff_id"]. " - First Name: " . $row["first_name"] . "<br>";
}
- MySQLi:
$sql = "UPDATE staff SET email='user.name@host.com' WHERE staff_id=3";
$conn->query($sql);
$sql = "DELETE FROM users WHERE id=3";
$conn->query($sql);
- PDO:
$sql = "UPDATE staff SET email=:email WHERE staff_id=:id";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':email', $newEmail);
$stmt->bindParam(':id', $userId);
$stmt->execute();
$sql = "DELETE FROM staff WHERE staff_id=:id";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':id', $userId);
$stmt->execute();