I am trying to print the results of the database little by little, for that I am selecting them 5 by 5, so as not to bring them all at once, the problem is that I grab them by the id, with the BETWEEN query, that is to say SELECT * FROM TABLE WHERE id BETWEEN 1 and 5, this will select from 1 to 5, and in js I save the last id that is 5, I add 1 and 5, leaving the following query SELECT * FROM TABLE WHERE id BETWEEN 6 and 10, the problem is when in the DB I have 16 id, it will select up to 15, but when selecting 16 it gives an error since there is no more, how could I solve that?
here the code:
php:
public static function getfinishedproducts($begin, $end) {
if($begin == "" && $end == "") {
$begin = 1;
$end = 5;
}else {
$begin = $begin;
$end = $end;
}
$query = self::connect()->prepare("SELECT * FROM `finished_products` WHERE `id` BETWEEN :begin AND :end");
$query->execute([":begin"=>$begin,":end"=>$end]);
return $row = $query->fetchAll(PDO::FETCH_ASSOC);
}
js:
mounted() {
this.showFinishedproducts();
},
methods: {
showFinishedproducts: function() {
axios.get("index.php?url=get_finished_products").then((response) => {
console.log(response.data);
this.begin = parseInt(response.data[5-1].id) + this.BEGIN;
this.end = parseInt(response.data[5-1].id) + this.END;
console.log("Actualizado: " + this.begin + " " + this.end);
//this.listFinishedproducts = response.data;
//this.filterFinishedproducts = response.data;
});
},
hola: function() {
axios.post("index.php?url=get_finished_products",{'begin':this.begin,'end':this.end}).then((response) => {
this.begin = parseInt(response.data[5-1].id) + this.BEGIN;
this.end = parseInt(response.data[5-1].id) + this.END;
console.log("Actualizado: " + this.begin + " " + this.end);
console.log(response.data);
//console.log(this.length);
//this.listFinishedproducts = response.data;
//this.filterFinishedproducts = response.data;
});
}
You should use the LIMIT clause, this way you can limit the number of records you will collect. Also apply an ORDERBY, since the data can become out of order. Use the following statement to collect the data:
There is an easy way to solve this through LIMIT in MySQL, the first thing you should know is that the Limit command which many use only with the first parameter which limits the number of records to fetch eg:
But if we use it with 2 parameters, the first will be the start record ($start) and the second the number of records to return ($limit)
We set the limit value defined by you:
Then determine the number of records in the table:
Once you know the total value of the records, you must know how many pages you are going to show, for that you only have to calculate.
Once we have all these parameters we can make the query in the database which will bring us only the required records.
The last step is to create an ajax that reads an input field of your page which will be the page indicator, you can also leave this field as read only and use this value to load the php script, remember that you must add 1 to the value that you are reading and in addition to here the $start variable will come out which you will multiply by 5 to see the next page, first page (1x5), second page (2x5)....etc, you must also put 2 buttons or links through of which to move forward or backward between the pages, the creation of the ajax is fairly simple, I leave that step to you because you must adapt your code to the call through ajax, I enclose the JQgrid link in which the sample of records is a basic part of its interface. I hope I have helped you, greetings.
http://trirand.com/blog/jqgrid/jqgrid.html