How to use PHP to interact with MySQL and Query the Database Tables
Connecting to MySQL using PHP and querying the database can seem overwhelming at first, but it becomes easy with practice. By following the steps outlined in this article, you should be able to get an insight on how to use PHP to interact with MySQL and query the database tables to perform basic operations. Once you have mastered these techniques, you can move on to more complex queries and build powerful web applications that can store and manage large amounts of data.
Introduction
Storing information in a database is an essential aspect of modern web development. By using a database, you can store and manage large amounts of data efficiently and securely. MySQL is one of the most popular relational database management systems used today. In this article, we will explain how to connect to MySQL using PHP and perform basic database operations such as inserting, selecting, updating, and deleting records.
What is MySQL
MySQL is a free, open-source relational database management system that uses Structured Query Language (SQL) to manage data. It was first released in 1995 and is now widely used in web development and other applications. MySQL is owned by Oracle Corporation and is available in two editions: Community Edition and Enterprise Edition.
MySQL Queries
Before you can connect to MySQL using PHP, you must first have MySQL installed on your system. You can download and install MySQL from the official MySQL website. Once you have installed MySQL, you can use PHP to connect to it and perform database operations.
Creating a Database and Tables with Test Data: To get started with MySQL and PHP, you will need to create a database and some tables with test data. Here is an example of how to create a “users” table with some test data:
CREATE DATABASE mydatabase;
USE mydatabase;
CREATE TABLE users (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
INSERT INTO users (firstname, lastname, email)
VALUES ('John', 'Doe', 'john@example.com'),
('Jane', 'Doe', 'jane@example.com'),
('Bob', 'Smith', 'bob@example.com');Connecting to MySQL using PHP
To connect to MySQL using PHP, you can use the mysqli_connect() function. Here is an example of how to connect to MySQL using PHP
$servername = "localhost";
$username = "root";
$password = "yourpassword";
$dbname = "mydatabase";
// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";Replace “yourpassword” with the password you set during the MySQL installation process and “mydatabase” with the name of your database.
Querying the Database using PHP: Now that you have established a connection to your MySQL database using PHP, you can perform SQL queries on it.
Inserting data into the table
Below a small example for inserting a record into the database table, as you might notice we have to pass to the mysqli_query function the connection handler ($conn). The function mysqli_query returns a bool (true/false) that will represent the result of the operation performed.
$sql = "INSERT INTO users (firstname, lastname, email) VALUES ('John', 'Doe', 'john@example.com')";
if (mysqli_query($conn, $sql)) {
echo "New record created successfully";
} else {
echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}This code inserts a record into the “users” table with the name “John”, last name “Doe”, and email “john@example.com“.
Selecting data from the table
This is an example of how to select a record from the database. What we are doing here is to run the query function and then scan the $result by checking the number of rows returned using the mysqli_num_rows and then (if this condition has been met) we will loop through the rows.
$sql = "SELECT id, firstname, lastname 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["firstname"] . " " . $row["lastname"] . "<br>";
}
} else {
echo "0 results";
}This code selects all the records from the “users” table and displays the results on the webpage.
Updating data in the table
Same as above in the insert example we are updating a record and check the mysqli_query result
$sql = "UPDATE users SET email='johndoe@example.com' WHERE firstname='John'";
if (mysqli_query($conn, $sql)) {
echo "Record updated successfully";
} else {
echo "Error updating record: " . mysqli_error($conn);
}This code updates the email address of the user with the name “John” in the “users” table to “johndoe@example.com“.
Deleting data from the table
This is an example on how to delete a record from the database table
$sql = "DELETE FROM users WHERE lastname='Doe'";
if (mysqli_query($conn, $sql)) {
echo "Record deleted successfully";
} else {
echo "Error deleting record: " . mysqli_error($conn);
}This code deletes all the records from the “users” table with the last name “Doe”.
Joining tables
The join example is nothing more or nothing less than a select statement
$sql = "SELECT users.firstname, orders.product_name FROM users INNER JOIN orders ON users.id = orders.user_id";
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
while ($row = mysqli_fetch_assoc($result)) {
echo "Name: " . $row["firstname"] . " - Product: " . $row["product_name"] . "<br>";
}
} else {
echo "0 results";
}This code joins the “users” and “orders” tables on the “id” and “user_id” fields, respectively, and selects the first name of each user and the product name from the orders table.
Conclusion
In this article, we explained how to connect to MySQL using PHP and perform basic database operations such as inserting, selecting, updating, and deleting records. By using PHP and MySQL together, you can create powerful web applications that can store and manage large amounts of data.
