PHP Lesson 20 – Display Data From a MySQL Database – Free Web Programming Tutorials for Beginners

Last updated on December 11th, 2023

How to display data stored in MySQL on a page using XAMPP?

You already know how to connect to the MySQL database and how to store data in it. Now let’s do something more complicated.

Let’s store information in a database and then retrieve and display the information in a page. For this purpose we will use the SQL SELECT statement.

The SELECT statement is used to retrieve data from a database.

For example, if you have a database table named “posts” with columns such as “title” and “content”, you can use the $sql_select variable to store a query like:

$sql_select = “SELECT * FROM posts”;

This statement will select all columns (*) from the “posts” table. You can also specify particular columns by replacing * with the specific column names you want to retrieve, like this:

$sql_select = “SELECT title, content FROM posts”;

So, the syntax of the SELECT statement is:

SELECT table_column FROM table_name;
(selects one column)

SELECT table_column1, table_column2… FROM table_name;
(selects multiple columns)

To select all the columns from the table, the syntax is:

SELECT * FROM table_name;

Start Apache and MySQL from the XAMPP control panel. Open phpMyAdmin and create database my_test_db. In the database, create a table named ‘posts’. In the table, create two columns, name the first column ‘title’ and name the second column ‘content’.

After the column Name field, there is a Type field and a Lenght/Value field.

For the column ‘title’ set type VARCHAR, and in the field Lenght/Value write the number 255. Leave the other fields with default values.

For the ‘content’ column, set the type to TEXT. Leave the other fields with their default values. Click on the Save button.

MySQL title and content columns

In MySQL, when you create a table, you define the structure of the data that will be stored in it by specifying the columns and their data types. Two common data types for storing textual information are VARCHAR and TEXT.

VARCHAR is short for Variable Character, i.e. VARCHAR is a variable-length character string. It can store a variable number of characters up to a specified maximum length. This type is suitable for storing short to medium-length strings, such as names, addresses, titles or other textual data. You need to specify a maximum length when defining the column. In our example the title column can store up to 255 characters.

TEXT is a data type used for storing large amounts of text. It can store variable-length strings with a much larger capacity than VARCHAR, and you don’t need to specify a maximum length.

Now create an HTML page named mypost.html and put the following code in it:

<!DOCTYPE html>
<html>
<head>
    <title>Form to Store Title and Text</title>
</head>
<body>

<div style="width:50%; margin:auto;">
  <h1>Message form</h1>
    <form action="store_data.php" method="post">
        <label for="title">Title:</label><br>
        <input type="text" id="title" name="my_title"><br><br>
        <label for="content">Content:</label><br>
        <textarea id="content" name="my_text" rows="4" cols="50">  </textarea><br><br>
        <input type="submit" value="Submit">
    </form>
</div>

</body>
</html>

The above code generates a simple web page with a form where users can input a title and some text content. When the user clicks the “Submit” button, the data will be sent to the “store_data.php” file using the HTTP POST method.

In detail:

<!DOCTYPE html>: This is the document type declaration that specifies the version of HTML used in the document. In this case, it indicates that the document is written in HTML5.
<html>: This tag represents the root of an HTML document.
<head>: This section contains meta-information about the document, such as the title, character set, and other metadata. In this specific case, it sets the title of the web page to “Form to Store Title and Text”.
<title>: This tag specifies the title of the web page, which appears in the browser’s title bar or tab.
<body>: This section contains the visible content of the web page that users see and interact with.
<div>: This tag defines a division or a section in the HTML document. In this case, it is used to create a container with a specific style that is 50% of the page width and centered horizontally on the page.
<h1>: This is a heading tag that defines the most important heading. In this case, it displays the text “Message form” as a prominent heading on the page.
<form>: This tag is used to create an HTML form for user input. The action attribute specifies the URL to which the form data will be sent when the form is submitted, and the method attribute specifies the HTTP method to be used, which is “post” in this case.
<label>: This tag represents a label for an input element. It helps users understand what kind of input is expected in the corresponding input field.
<input>: This tag creates an input field that allows users to enter text. In this form, there are two input fields: one for the title and one for the content. Both have specific id attributes and name attributes to identify them in the backend.
<textarea>: This tag creates a multiline text input control (a text area) where users can input multiple lines of text. It has attributes for rows and columns to define its size, in this case: rows=”4″ cols=”50″.
<input type=”submit”>: This input element represents a button that, when clicked, submits the form data to the URL specified in the action attribute of the form.

Now create a PHP page store_data.php and put the following code in it:

<!DOCTYPE html>
<html>
<head>

<style>

table {
  border: 2px solid #000000;
  width: 50%;
  margin: auto;
  border-collapse: collapse;
  text-align: center;
}

</style>

</head>
<body>

<?php

// Database connection parameters
$servername = "localhost"; // server name, usually 'localhost'
$username = "root"; // default username for XAMPP is 'root'
$password = ""; // default password is blank
$dbname = "my_test_db"; // the name of the database you created

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

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

// Handle the form submission
if ($_SERVER["REQUEST_METHOD"] == "POST") {
    $title = $conn->real_escape_string($_POST['my_title']); // Retrieve the title and sanitize it
    $text = $conn->real_escape_string($_POST['my_text']); // Retrieve the text and sanitize it

    // SQL query to insert data into the 'posts' table, columns title and content
    $sql = "INSERT INTO posts (title, content) VALUES ('$title', '$text')";

    if ($conn->query($sql) === TRUE) {
        echo "Data stored successfully<br>";
    } else {
        echo "Error: " . $sql . "<br>" . $conn->error;
    }
}

// SQL query to retrieve data from the 'posts' table
$sql_select = "SELECT title, content FROM posts";
$result = $conn->query($sql_select);

if ($result->num_rows > 0) {
    // Output data of each row
    while ($row = $result->fetch_assoc()) {
        echo "<table><tr><td>"."<h1>"."Title: " . $row["title"] ."</h1>"."<br>";
        echo "Content: " . $row["content"] . "</td></tr></table>"."<br><br>";
    }
} else {
    echo "No data found in the database.";
}

// Close the database connection
$conn->close();

?>

</body>
</html>

The above code snippet connects to a MySQL database, handles the form submission, inserts form data into a database table, retrieves data from the same table, and displays the data in a table format on the web page.

The code is made up of two parts – HTML/CSS and PHP.

HTML and CSS Part:

PHP Part:

1. $servername, $username, $password, and $dbname: These variables store the connection parameters for the database. Here, the code is attempting to connect to a MySQL database using the provided credentials.

2. new mysqli(): This function attempts to create a new MySQL database connection using the provided parameters.

3. if ($conn->connect_error): This checks if there was an error in the database connection.

4. if ($_SERVER[“REQUEST_METHOD”] == “POST”): This checks whether the form has been submitted using the HTTP POST method.

5. $conn->real_escape_string(): This function is used to escape special characters in a string to make it safe to insert into a database.

6. $sql = “INSERT INTO posts (title, content) VALUES (‘$title’, ‘$text’)”;: This SQL query attempts to insert data into a table called ‘posts’ with columns ‘title’ and ‘content’.

7. if ($conn->query($sql) === TRUE): This checks if the SQL query was executed successfully.

8. $sql_select = “SELECT title, content FROM posts”;: This SQL query attempts to retrieve data from the ‘posts’ table.

9. if ($result->num_rows > 0): This checks if there are rows returned from the database query.

10. while ($row = $result->fetch_assoc()): This loop fetches each row of the result as an associative array.

11. echo: This PHP function is used to output text or variables.

12. $conn->close();: Closes the database connection

Now put the two pages mypost.html and store_data.php in the htdocs folder.

Open the page mypost.html at address localhost/mypost.html. Type some title in the title field, for example “This is the first test title”. Type some content in the content field, for example “This is the first test content.” Click the Submit button.

Message form

If all is well with the code and database, a store_data.php page should open and you should see the entered title and its content in a black frame. Open the HTML page again at localhost/mypost.html and write a second title and content to it, then click the Submit button. Now in the store_data.php page you should see the two titles and their content in separate frames, like this:

Store data result