PHP and MySQL are commonly used together to build dynamic and database-driven web applications.
PHP handles server-side logic while MySQL stores application data such as users, products, and orders.
What is MySQL?
MySQL is an open-source relational database management system used to store structured data in tables.
It is widely used with PHP because it is fast, reliable, and easy to integrate.
Why Connect PHP with MySQL?
Connecting PHP with MySQL allows applications to save, retrieve, update, and delete data dynamically.
Most modern web applications depend on databases for managing user and business information.
Requirements for Database Connection
To connect PHP with MySQL, developers need a MySQL server, database credentials, and PHP installed on the server.
Tools like XAMPP, WAMP, or MAMP provide an easy local development environment.
Understanding Database Credentials
A database connection requires a hostname, username, password, and database name.
<?php
$host = 'localhost';
$username = 'root';
$password = '';
$database = 'company_db';
?>Connecting Using MySQLi
MySQLi is a PHP extension used for interacting with MySQL databases.
It supports both procedural and object-oriented programming styles.
<?php
$conn = mysqli_connect('localhost', 'root', '', 'company_db');
?>Checking Database Connection
After creating a connection, developers should check whether the connection was successful.
<?php
if (!$conn) {
die('Connection Failed: ' . mysqli_connect_error());
}
echo 'Connected Successfully';
?>Object-Oriented MySQLi Connection
MySQLi also supports object-oriented syntax for cleaner and more structured code.
<?php
$conn = new mysqli('localhost', 'root', '', 'company_db');
if ($conn->connect_error) {
die('Connection Failed');
}
?>Using PDO for Database Connection
PDO, or PHP Data Objects, is another method for connecting PHP with databases.
PDO supports multiple database systems and provides better flexibility.
<?php
try {
$conn = new PDO('mysql:host=localhost;dbname=company_db', 'root', '');
echo 'Connected Successfully';
} catch(PDOException $e) {
echo $e->getMessage();
}
?>Creating a Database
PHP can execute SQL queries to create databases dynamically.
<?php
$sql = 'CREATE DATABASE company_db';
mysqli_query($conn, $sql);
?>Creating Tables
Tables store structured records inside a database.
CREATE TABLE employees (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100)
);Executing SQL Queries
PHP executes SQL queries using functions such as mysqli_query() or PDO methods.
<?php
$sql = "SELECT * FROM employees";
$result = mysqli_query($conn, $sql);
?>Fetching Database Records
Query results can be fetched and displayed using loops.
<?php
while($row = mysqli_fetch_assoc($result)) {
echo $row['name'];
}
?>Closing Database Connections
Closing unused database connections helps improve application performance.
<?php
mysqli_close($conn);
?>Handling Connection Errors
Database connection errors can occur because of incorrect credentials or unavailable database servers.
Proper error handling helps developers debug problems effectively.
Security Best Practices
Developers should never expose database credentials publicly in source code repositories.
Prepared statements should be used to prevent SQL injection attacks.
Prepared Statements Example
<?php
$stmt = $conn->prepare('SELECT * FROM employees WHERE email = ?');
$stmt->bind_param('s', $email);
$stmt->execute();
?>Common Beginner Mistakes
Using incorrect database credentials and forgetting to check connection errors are common mistakes.
Failing to sanitize user input can also lead to serious security vulnerabilities.
Real-World Applications
PHP and MySQL are used together in content management systems, e-commerce platforms, dashboards, and authentication systems.
Summary
Connecting PHP with MySQL allows developers to build powerful database-driven applications.
Understanding database connections, queries, and security practices is essential for every PHP developer.