Setting Up a MySQL Database and Connecting with PHP

Introduction

MySQL is a widely used open-source relational database management system. PHP is a popular server-side scripting language. This tutorial will guide you through setting up a MySQL database and connecting to it using PHP.

Prerequisites

• A server with MySQL and PHP installed.
• Access with sudo privileges.

Step 1: Install MySQL and PHP (if not already installed)

sudo apt update
sudo apt install mysql-server php php-mysql -y

Step 2: Secure MySQL Installation

Run the security script:

sudo mysql_secure_installation

Follow the prompts to set the root password and secure the installation.

Step 3: Create a MySQL Database and User

Log in to MySQL:

sudo mysql -u root -p
Create a new database and user:

CREATE DATABASE mydatabase;
CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypassword';
GRANT ALL PRIVILEGES ON mydatabase.* TO 'myuser'@'localhost';
FLUSH PRIVILEGES;
EXIT;

Step 4: Create a PHP Script to Connect to the Database

Create a file named db_connect.php in your web server’s root directory (e.g., /var/www/html):

<?php
$servername = "localhost";
$username = "myuser";
$password = "mypassword";
$dbname = "mydatabase";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";
?>

Step 5: Test the Database Connection

Open a web browser and navigate to http://your_server_ip/db_connect.php. You should see “Connected successfully”.

Step 6: Handling Database Operations

Modify db_connect.php to perform database queries:.

// Perform a query
$sql = "SELECT * FROM mytable";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // Output data
    while($row = $result->fetch_assoc()) {
        echo "id: " . $row["id"]. " - Name: " . $row["name"]. "<br>";
    }
} else {
    echo "0 results";
}
$conn->close();

Step 7: Preventing SQL Injection

Always use prepared statements or parameterized queries to prevent SQL injection attacks.

Conclusion

You have set up a MySQL database and connected to it using PHP. You can now build dynamic web applications that interact with your database.