How to cache in PHP by Record set

In many cases, we need to  load information from a database in a server and show it to the user. We can cache data by recordsets in PHP easily. Some databases are extensive, and it's better to display their information in a page-by-page form. We can do this by using PHP commands too.

Cache data by recordsets in PHP:

Each row of information stored in database tables is called a record. A record set is an array that contains records read from a database. In other words, we put the records read from the database in an array, which is called the record set.  You can cache data by recordsets in PHP. The record set can contain all or part of the information in the database. Caching  information for a web page  means that after loading data from the server and placing it in the user's system, it is placed on his system. After moving between different pages, the page information Instead of downloading from the server, will be read from the user system and displayed. Caching  can speed up the display of web page content.

To create recordsets in PHP, you must first communicate with the database.

Creating a connection to the database:

As mentioned before, it is better to put the instructions related to communicating with the database in a separate file. Wherever it is necessary to use the database, enable that relation by using the include command at the beginning of code. The advantage of this is that we apply these changes only once in that file in case of changes in the database settings.

Put the above commands in a file called  dbConnection.php. These commands communicate with a database called students.

Creating a SQL Query to read records from a database:

In this step, we first establish the necessary connection to the database by including the dbConfig.php file. Then we use SQL statements to read the required information from the database. There are several ways to create queries from database tables and cache data by recordsets in PHP. We need to use special SQL statements depending on what method we used to connect to the database.

One of the most important commands used to create a query is the mysql_query command. Note that in the above versions of PHP, you should use the mysqli_query command instead of this command.

Display database table information using the query:

Assuming we have a table in the student database called properties that is similar to the following figure, we want to display the information in this table using a query:

As shown in the figure in this table, there are 5 fields named "stu_id", "fname", "family", "tel" and "address". Note the following code:

At the beginning of the code, using the include 'dbConfig.php' command, the necessary connection is established with the student's database using the codes in the dbConfig.php file.

A query is then executed on the database using the mysqli_query command. Note the general form of this command:

After creating the desired query, we can display the information in the query using the while loop and the mysqli_fetch_array function. This function reads the information of a record each time it is executed.

The result of executing the above commands can be seen in the following figure:

Reading part of the database information using recordsets:

Databases used in web pages may contain a large amount of information, and if we want to read all this information and place them in a record set, we have wasted useful server time. Therefore, it is better to load the required part of the information from the database.

The easiest way to do this is to use the following command to create a query:

In the above command, index1 shows the start record number, and the num specifies the number of records to be read. Note that the number of records starts from zero.

Note the following example:

By placing this command in the code snippet above, only records 1 to 3 are read and displayed instead of the following command.

The result of execution can be seen in the following figure:

In this method, queries are executed faster, and we will have more efficient use of server resources.

Display database information page by page by using recordsets:

When the number of records in the database is large, it is better to show them to the user page by page in the browser. We can do this by using the same LIMIT command when selecting records in the query.

We need to know the number of records in the database to do this. Also, decide that we want to display how many records are on each browser page. We put this number of records in a variable called $ results_per_page. Based on the available information, we now have to determine how many pages we will need.

We have placed the commands needed to connect to the database in the dbconfig.php file. The image of the file can be seen in the following figure:

commands required to  cache data by recordsets in PHP  and display them page by page are given in the following section. We have saved this code in a file called final.php.

After executing the above code, the output is seen as shown below. Clicking on any number will show the records related to that page.

After the include command, the current page number is checked using $ _GET [["page"] and placed in the $ page variable.

The following line specifies the next record number to be displayed.

Look at the code carefully. You will notice that its principles are similar to the previous section and are explained.