PHP: MySQL Database

Downloading MySQL Database

To use MySQL on your system, you first need to download and install it. Follow these simple steps:

Visit the Official Website
Go to the MySQL official website to download MySQL.

Choose the Installer
Select the installer suitable for your operating system (Windows, macOS, or Linux).

Install MySQL
Run the installer and follow the steps in the setup wizard. Choose a server configuration that fits your requirements.

Set Up a Root Password
During installation, create a strong root password to secure your database.

Verify Installation
Open the MySQL Command Line Client or Workbench and log in using your root password.

After installation, you are all set to create, manage, and interact with your databases.

How to Connect PHP with MySQL Database?

To connect PHP with MySQL, you need to use one of the two main extensions provided by PHP: MySQLi (MySQL Improved) or PDO (PHP Data Objects). Both allow you to interact with MySQL databases, but they have differences in functionality.

Difference Between MySQLi and PDO

If your project is specific to MySQL, you can use MySQLi. However, if you may need to switch databases in the future, PDO is the better choice.

There Are Three Ways of Working with MySQL and PHP

1. MySQLi (Object-Oriented): Allows object-oriented interactions with MySQL databases.
    
2. MySQLi (Procedural): Offers procedural methods to interact with MySQL.
    
3. PDO: A more flexible approach, supporting multiple databases.

Connecting to MySQL Database Using PHP

Using MySQLi (Procedural)

<?php
// Database credentials
$servername = "localhost";
$username = "root";
$password = "";
$database = "test_db";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $database);

// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

echo "Connected successfully";
?>

Explanation:

• mysqli_connect() establishes the connection.
   
• The if statement checks if the connection is successful.
   
• If successful, the script outputs "Connected successfully."

Using MySQLi (Object-Oriented)

<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "test_db";

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

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

echo "Connected successfully";
?>

Explanation:

• Uses the new mysqli object to create the connection.
   
• The $conn->connect_error property checks for errors.

Using PDO

<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "test_db";

try {
    $conn = new PDO("mysql:host=$servername;dbname=$database", $username, $password);
    // Set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully";
} catch (PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}
?>

Explanation:

• The try block attempts to establish the connection.
   
• The catch block handles exceptions, showing an error message if the connection fails.

Closing a Connection

Closing a database connection is crucial to free up resources. Use the following commands for each method:

MySQLi (Procedural)

mysqli_close($conn);

MySQLi (Object-Oriented)

$conn->close();

PDO

$conn = null;

Note: In PDO, setting the connection object to null closes the connection.

Frequently Asked Questions

What is the difference between MySQLi and PDO? 

MySQLi works only with MySQL databases, while PDO supports multiple database types and offers better flexibility.

How do I check if my PHP code is successfully connected to MySQL? 

Use conditional checks like mysqli_connect_error() or try-catch blocks with PDO to confirm the connection.

Is it necessary to close the database connection? 

Yes, closing the connection ensures that resources are freed, preventing potential memory leaks.

Conclusion

In this article, we discussed how to work with MySQL databases using PHP, MySQL’s features, installation steps, and how to connect PHP with MySQL using MySQLi and PDO. Practical examples were covered to understand the connection process, ensuring you’re ready to start building your own dynamic web applications.