MySQL is a popular relational database management system used to store and manage large amounts of data. PHP is a server-side scripting language used to create dynamic web pages. In this blog post, we will look at how to retrieve data from a MySQL database using PHP. To fully understand this, you might want to check out my post on how to install PHP, Nginx and MySQL click here.

Now let's proceed, We'd test the database using a simple Todo list as the case study.

To begin, create a new user with the mysql_native_password authentication method to be able to connect to the MySQL database from PHP.

Then, Create a database named first_database and a user named example_user, but you can replace these names with different values.

• First, connect to the MySQL console using the root account:

  sudo mysql

• To create a new database, run the following command from your MySQL console:

    CREATE DATABASE example_database;
  

• Now you can create a new user and grant him full privileges on the database you have just created

    CREATE USER 'first_user'@'%' IDENTIFIED WITH mysql_native_password BY 'Password';
  

• Replace 'Password' with your preferred 'string'

• Now we need to give this user permission over the first_database database:

    GRANT ALL ON first_database.* TO 'first_user'
  

• Now exit the MySQL shell with:

  mysql> exit

To verify if the new user has the appropriate permissions, you can log in to the MySQL console using the user credentials you have created specifically for them.

mysql -u example_user -p

mysql> SHOW DATABASES;

We are going to make a practice table called "todo_list" in the "first_database" database, with three columns: "item_id", "content", and a primary key constraint on "item_id".". You can do this by entering a specific command on the MySQL console.

CREATE TABLE first_database.todo_list (

Add some content to the sample table. You can use the following command multiple times, replacing the VALUES with different information.

INSERT INTO example_database.todo_list (content) VALUES ("My first important item");

To confirm that the data was successfully saved to your table, run:

SELECT * FROM example_database.todo_list;

Once you have made sure that your sample table contains correct information, you can leave the MySQL console. This will be indicated by the following display message.

You can now make a PHP script that connects to MySQL and retrieves your content.

• Make a new PHP file in the directory of your custom web root. From the previous post, we used "ProjectLEMP"

  vi /var/www/projectLEMP/todo_list.php

• Copy this content into your todo_list.php script:

<?php
$user = "example_user";
$password = "password";
$database = "example_database";
$table = "todo_list";

try {
  $db = new PDO("mysql:host=localhost;dbname=$database", $user, $password);
  echo "<h2>TODO</h2><ol>";
  foreach($db->query("SELECT content FROM $table") as $row) {
    echo "<li>" . $row['content'] . "</li>";
  }
  echo "</ol>";
} catch (PDOException $e) {
    print "Error!: " . $e->getMessage() . "<br/>";
    die();
}

This PHP code connects to the MySQL database and retrieves the content of the "todo_list" table, then shows the results in a list. It will produce an error if there is an issue with the database connection.

• Save the file and exit the text editor

To open the page, enter the domain name or public IP address of your website on a web browser, then add this information to the end /todo_list.php. "Public_domain_or_IP/todo_list.php"

When you view the page, you will see the content you added to your sample table, which indicates that your PHP setup can now communicate with your MySQL server.