Fetching MySQL Data with PHP and Storing in localStorage
Updated: 10-Jan-2024 / Tags: Javascript and Local Storage / Views: 463 - Author: George
Introduction
In this short tutorial, we are going to fetch data from a MySQL database with PHP and store it in the browser's local storage using JavaScript.
This is a technique that we use to ensure a fast user experience. When we store data in the local storage, we don't need to send a server request every time to retrieve it, instead, we can fetch it from the local storage, saving time. However, be aware that we can only save data up to 5MB. Nevertheless, 5MB is still a substantial amount of data.
Now in this tutorial we assume that we have a database table named products. We will write PHP code to retrieve all products from the table. Then, we will use an AJAX request with JavaScript to fetch the data from the PHP file and store it in the local storage.
The PHP code
We are going to write the php code in a file named script.php.
This PHP code connects to a MySQL database, retrieves data from the 'products' table, and outputs the data in JSON format if the connection is successful. If there's a connection error, it stops and displays the error message.
// Create a new mysqli object to establish
// a connection to the MySQL database
$mysqli = new mysqli('localhost', 'mysql_username', 'mysql_password', 'db_name');
// Check if the connection was successful
if ($mysqli->connect_errno != 0) {
// If there's an error, stop the script and
// output the error message
die($mysqli->connect_error);
} else {
// If the connection is successful, proceed with data retrieval
// Initialize an empty array to store the fetched products
$products = array();
// Execute a SELECT query to fetch data
// from the 'products' table
$result = $mysqli->query("SELECT * FROM `products`");
// Loop through the result set and store
// each row as an associative array in the $products array
while ($row = $result->fetch_assoc()) {
$products[] = $row;
}
// Set the response header to indicate
// that the content is in JSON format
header('Content-Type: application/json');
// Encode the $products array as JSON and output it
echo json_encode($products);
}
The Javascript code
Now that we have written the php code to fetch the data from the products table, we are going to write the javascript code in a file named script.js.
This JavaScript code uses the Fetch API to make an asynchronous request to the script.php server-side script. It handles the response, converts it to JSON, stores the data in the browser's localStorage, and logs messages to the console for success or error. This pattern is commonly used in web development for fetching data from a server and handling the results.
// Use the Fetch API to make an asynchronous request to 'script.php'
fetch('script.php')
// Handle the response from the server
.then(response => response.json())
// Process the JSON data returned from the server
.then(data => {
// Store the fetched data in the browser's localStorage
// under the key 'products'
localStorage.setItem('products', JSON.stringify(data));
// Log a success message to the console
console.log('Data fetched and stored in localStorage:', data);
})
// Handle any errors that occur during the fetch operation
.catch(error => console.error('Error fetching data:', error));
Now, let's explain each part of the code:
-
Fetch API:
The fetch function is part of the Fetch API, which is used to make network requests. It initiates an asynchronous request to the 'script.php' server-side script.
-
Promise Chain:
.then(response => response.json()): This chain of .then callbacks is used to handle the response from the server.
The first .then callback converts the response to JSON using response.json(). This assumes that the server is returning a response with a JSON content type.
.then(data => { /* ... */ }): This callback processes the JSON data returned from the server. It stores the fetched data in the browser's localStorage under the key 'products' using localStorage.setItem.
It also logs a success message to the console using console.log..catch(error => console.error('Error fetching data:', error)): This part catches and handles any errors that may occur during the fetch operation. If an error occurs, it logs an error message to the console using console.error.
The local storage
After running the above code open the dev tools and inspect the browser's local storage. You will see the data from your database table.
The data that you see here are from the products table that i used for this tutorial.

If you like to use the same database table click and download it from the link below:
Download the products.sql file.
Summary
In this short tutorial we saw how to retrieve data from the database and store them in the browser's local storage.
If you want to learn more on how the local storage works check out the articles below.
How browser's local storage works
How to save products in the browsers local storage