PHP applications are capable of interacting with databases to store and retrieve data. This guide will walk you through the basics of working with databases in PHP, focusing on database connectivity.
MysSQLi and Database Connectivity in PHP
Before diving into the code, it’s crucial to understand the concept of database connectivity. PHP relies on several extensions to interact with databases, and the most commonly used one is MySQLi.
MySQLi, short for MySQL Improved, is a PHP extension specifically designed to work with the MySQL database. It provides an improved and modernized interface for interacting with MySQL databases in PHP applications. MySQLi was introduced as an enhancement over the original MySQL extension to address certain limitations and offer additional features.
On Linux, installing the MySQLi extension for PHP involves installing the necessary packages through the package manager. The specific commands may vary slightly depending on the Linux distribution you’re using. Here are instructions for a few popular distributions:
Ubuntu/Debian
sudo apt-get update
sudo apt-get install php-mysqlCentOS/RHEL
sudo yum install php-mysqlndFedora
sudo dnf install php-mysqlndArch Linux
sudo pacman -S php-mysqlOpenSUSE
sudo zypper install php-mysqlGeneric Installation (for custom configurations)
If you have a custom PHP installation or need to configure PHP with MySQLi manually, you can use the pecl command to install the MySQLi extension:
sudo pecl install mysqliAfter installing or enabling the MySQLi extension, you need to restart the web server to apply the changes. The following commands can be used to restart Apache or Nginx:
- For Apache:
sudo service apache2 restart # or systemctl restart apache2- For Nginx
sudo service nginx restart # or systemctl restart nginxTo confirm that MySQLi has been successfully installed, you can create a PHP file (e.g., info.php) with the following content and access it through a web browser:
<?php
phpinfo();
?>Look for the MySQLi section on the PHP information page, which should indicate that MySQLi is enabled and configured.
The above instructions assume you already have PHP installed on your Linux system. If PHP is not installed, you can install it along with the MySQLi extension using the appropriate package manager commands.
Establishing Connection
To work with a database in PHP, you need to establish a connection first. The mysqli_connect() function is used for this purpose. Here’s an example:
<?php
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$database = "your_database";
// Create connection
$conn = mysqli_connect($servername, $username, $password, $database);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";
?>The above code begins by defining four variables: $servername, $username, $password, and $database. These represent the server where the database is hosted (localhost in this case), the username and password to access the database, and the name of the specific database to connect to.
The mysqli_connect() function is then used to create a connection to the MySQL database. It takes the server name, username, password, and database name as parameters and returns a connection object ($conn). This object will be used for executing queries and interacting with the database.
The code then checks if the connection was successful using an if statement. If $conn is not true (meaning the connection failed), it executes the die function, which terminates the script and prints an error message indicating the reason for the connection failure, retrieved using mysqli_connect_error().
If the connection is successful, the script echoes “Connected successfully.” This message indicates that the PHP application has successfully connected to the MySQL database.
You’re free to replace “your_username,” “your_password,” and “your_database” with your actual database credentials.
Executing SQL Queries
Once the connection is established, you can execute SQL queries to interact with the database. Here’s an example of inserting data into a table:
<?php
$sql = "INSERT INTO users (username, email, password) VALUES ('john_doe', '[email protected]', 'hashed_password')";
if (mysqli_query($conn, $sql)) {
echo "Record inserted successfully";
} else {
echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}
?>This example inserts a new record into the “users” table. Replace the values with your data and modify the SQL query accordingly.
Retrieving Data
Fetching data from the database is a common operation. The mysqli_query() function is used to execute SELECT queries. Here’s an example:
<?php
$sql = "SELECT username, email FROM users";
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
while ($row = mysqli_fetch_assoc($result)) {
echo "Username: " . $row["username"] . " - Email: " . $row["email"] . "<br>";
}
} else {
echo "0 results";
}
mysqli_close($conn);
?>This script retrieves usernames and emails from the “users” table and displays them. Customize the SQL query based on your database structure.
Deleting Data
This script deletes the user with the username “john_doe” from the “users” table.
<?php
$sql = "DELETE FROM users WHERE username='john_doe'";
if (mysqli_query($conn, $sql)) {
echo "Record deleted successfully";
} else {
echo "Error deleting record: " . mysqli_error($conn);
}
?>
Closing the Connection
Finally, it’s good practice to close the database connection using the mysqli_close() function:
<?php
mysqli_close($conn);
?>Closing the connection is essential to free up resources and avoid potential security risks.
