How to connect MySQL Database with PHP Website

Integrating a MySQL database with a PHP website in web development is an important step in creating a dynamic and interactive web-based application properly combining these two features not only increases the performance of the website but also contributes greatly to its overall function. When it comes to installing WordPress websites which are also written in PHP use MySQL databases to store and manage content, such as articles, pages, and images. E-commerce websites also use MySQL databases to store and manage product information, customer information, and order information.

In this post, we will discuss the basic understanding of PHP language with MySQL database, and explore the process of connecting MySQL database with the PHP website.

Understand PHP language and MySQL database

PHP Language

PHP (Hypertext Preprocessor) is a general-purpose scripting language that is especially suited for web development. It is a server-side language, meaning that the code is executed on the server before the web page is sent to the client’s browser. This makes PHP ideal for creating dynamic and interactive web applications. Here are some of the things that PHP can do:

• Generate dynamic page content
• Collect form data
• Send and receive cookies
• Add, delete, and modify data in databases
• Control user access
• Encrypt data
• Create, open, read, write, delete, and close files on the server

MySQL Database

MySQL is an open-source relational database management system (RDBMS). It is used by a wide variety of applications, including websites, web applications, and e-commerce platforms. MySQL stores data in tables, which are made up of rows and columns.MySQL uses a standard database query language called SQL (Structured Query Language) to interact with data. SQL is a powerful language that allows you to perform a wide variety of tasks on data, such as:

• Creating and deleting tables
• Inserting, updating, and deleting data
• Querying data

Connection MySQL database in PHP language

PHP is a popular server-side scripting language that is often used to develop dynamic web applications. MySQL is a popular open-source relational database management system (RDBMS). To create dynamic web applications, PHP needs to be able to communicate with a database, such as MySQL. There are two main ways to connect a MySQL database to a PHP website:

Using the MySQLi extension

The MySQLi extension is a newer and improved version of the original MySQL extension. It is more object-oriented and offers a number of other advantages, such as support for prepared statements. To connect to a MySQL database using the MySQLi extension, you can use the following code:

<?php
$hostname = "localhost";
$username = "root";
$password = "";
$database = "my_database";
$conn = new mysqli($hostname, $username, $password, $database);
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully!";

Once you have connected to the database, you can use the $conn object to execute SQL queries and retrieve data.

Using PHP Data Objects

PDO (PHP Data Objects) is a database abstraction layer that allows you to connect to and interact with different database systems using a single API. This makes it easy to switch between different database systems without having to rewrite your code. To connect to a MySQL database using PDO, you can use the following code:

<?php
$dsn = "mysql:host=localhost;dbname=my_database";
$username = "root";
$password = "";
$conn = new PDO($dsn, $username, $password);
if (!$conn) {
    die("Connection failed!");
}
echo "Connected successfully!";

Which method you choose to use is up to you. If you are only going to be working with MySQL databases, then the MySQLi extension is a good choice. However, if you think you might need to switch to a different database system in the future, then PDO is a better choice.

Example of using PHP to connect to a MySQL database and retrieve data:

The following code shows how to use PHP to connect to a MySQL database and retrieve data from a table:

<?php

// Connect to the database
$hostname = "localhost";
$username = "root";
$password = "";
$database = "test_db";

$conn = new PDO("mysql:host=$hostname;dbname=$database", $username, $password);

if (!$conn) {
    die("Connection failed!");
}

// Prepare the SQL statement
$sql = "SELECT * FROM users";

// Execute the SQL statement
$result = $conn->query($sql);

if (!$result) {
    die("Query failed!");
}
// Fetch the data from the result set
$users = $result->fetchAll();
// Close the database connection
$conn = null;
// Display the data
foreach ($users as $user) {
    echo $user["name"] . "<br>";
}

This code will connect to the MySQL database named test_db and retrieve all of the data from the user’s table. The data will then be displayed to the user.

Steps to connect MySQL with PHP

• Create a MySQL database: If you working as a local host, you can use the GUI tool provided such as XAMPP to create a database.
• Create a PHP file to connect to the database: Create a new PHP file and name it db_connection.php. In this file, add the code that is described above in the MySQLi extension section.
• Include the database connection file in your PHP script: In the PHP script that you want to connect to the database, add the following code at the top of the script:

include 'db_connection.php';

• Use the database connection object to perform database operations: Once you have connected to the database, you can use the $conn object to perform database operations such as SELECT, INSERT, UPDATE, and DELETE.
• Close the database connection: When you are finished using the database, it is important to close the connection. You can do this by calling the close() method on the $conn object.

Conclusion

Connecting a MySQL database to a PHP website is a relatively straightforward process. By following the steps in this article, you can easily connect your database to your website and start developing dynamic web applications.