Day 37: Integrating PHP and MySQL

Overview of MySQL as a popular database management system

MySQL is one of the most popular database management systems (DBMS) used in web development. It is a free, open-source software that provides a reliable and efficient way to manage large amounts of data. MySQL is used by many of the world’s largest and most successful websites, including Facebook, Twitter, and Wikipedia.

MySQL supports a wide range of features, including:

  • High-performance, multi-threaded architecture
  • Robust transaction support
  • Scalability and high availability
  • Full-text search and indexing
  • Advanced security features

Creating a MySQL database and tables

Before you can start using MySQL with PHP, you need to create a database and tables in MySQL. Here are the basic steps to do this:

  1. Log in to MySQL using a command-line tool like the MySQL shell or a graphical tool like phpMyAdmin.
  2. Create a new database using the CREATE DATABASE command. For example:sqlCopy codeCREATE DATABASE mydatabase;
  3. Select the database you just created using the USE command. For example:Copy codeUSE mydatabase;
  4. Create a new table using the CREATE TABLE command. For example:sqlCopy codeCREATE TABLE users ( id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, username VARCHAR(30) NOT NULL, email VARCHAR(50), password VARCHAR(30) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); This creates a new table called users with columns for id, username, email, password, and created_at.
  5. Insert some data into the table using the INSERT INTO command. For example:sqlCopy codeINSERT INTO users (username, email, password) VALUES ('john', 'john@example.com', 'secret'), ('jane', 'jane@example.com', 'password'); This inserts two new rows into the users table with data for the username, email, and password columns.

Connecting to a MySQL database from PHP

To connect to a MySQL database from PHP, you can use the mysqli or PDO extensions, which provide an object-oriented interface to MySQL. Here’s an example of how to connect to a MySQL database using mysqli:

$host = 'localhost';
$username = 'myusername';
$password = 'mypassword';
$database = 'mydatabase';

$conn = new mysqli($host, $username, $password, $database);

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

echo "Connected successfully";

This creates a new mysqli object and connects to the MySQL server at localhost with the given username, password, and database. If the connection is successful, it prints a message to the screen.

Querying data from MySQL with PHP

To query data from a MySQL database using PHP, you need to connect to the database using the appropriate credentials, select the database you want to work with, and then execute a SQL query.

Here is an example PHP script that connects to a MySQL database and retrieves data from a table:

<?php
// Database credentials
$host = 'localhost';
$user = 'username';
$password = 'password';
$database = 'database_name';

// Connect to the database
$conn = mysqli_connect($host, $user, $password, $database);

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

// Execute a SQL query
$sql = "SELECT * FROM users";
$result = mysqli_query($conn, $sql);

// Check if any rows were returned
if (mysqli_num_rows($result) > 0) {
    // Loop through the rows and output the data
    while ($row = mysqli_fetch_assoc($result)) {
        echo 'ID: ' . $row['id'] . ', Name: ' . $row['name'] . ', Email: ' . $row['email'] . '<br>';
    }
} else {
    echo 'No records found';
}

// Close the database connection
mysqli_close($conn);
?>

In this example, we first define the database credentials and then use the mysqli_connect() function to connect to the database. If the connection fails, the script will terminate with an error message.

Next, we execute a SQL query using the mysqli_query() function and retrieve the result using the mysqli_fetch_assoc() function. We then loop through the rows and output the data.

Finally, we close the database connection using the mysqli_close() function.