Limit Data Selections From a MySQL Database

#1

hi everyone,
I try to get data limited from table into database, I tried to change and add this code
“$sql = “SELECT * FROM Orders LIMIT 30”;”
but to no avail.
What I mean by limited data, for example I have 8 tables (data), I don’t want to call them from the beginning, I want to call them from the third (data) table. what changes will I make to the code?, below are the codes to be modified.

Thanks in advance for giving me a part of your precious time and helping you to me, my regards.

<?php 
     if (isset($_GET['pageno'])) {
            $pageno = $_GET['pageno'];
        } else {
            $pageno = 1;
        }
        
		$no_of_records_per_page = 6;
        $offset = ($pageno-1) * $no_of_records_per_page;


        $total_pages_sql = "SELECT COUNT(*) FROM tblposts";
        $result = mysqli_query($con,$total_pages_sql);
        $total_rows = mysqli_fetch_array($result)[0];
        $total_pages = ceil($total_rows / $no_of_records_per_page);


$query=mysqli_query($con,"select tblposts.id as pid,tblposts.PostTitle as posttitle,tblposts.PostImage,tblcategory.CategoryName as category,tblcategory.id as cid,tblsubcategory.Subcategory as subcategory,tblposts.PostDetails as postdetails,tblposts.PostingDate as postingdate,tblposts.PostUrl as url from tblposts left join tblcategory on tblcategory.id=tblposts.CategoryId left join  tblsubcategory on  tblsubcategory.SubCategoryId=tblposts.SubCategoryId where tblposts.Is_Active=1 order by tblposts.id desc  LIMIT $offset, $no_of_records_per_page");
while ($row=mysqli_fetch_array ($query)) {
?>

ADMIN EDIT: Added Code Tags

#2

you have 8 db-tables and you want to select what db-table to select max 30 rows from?

#3

Maybe you look for this combination of OFFSET and LIMIT?

SELECT * FROM Orders LIMIT 15, 10

This example will select records 16 - 25 (when available)

#4

No. 30 is not important,
I want to bring data from the third table to the end of the tables

#5

Sorry enarco but you have databases which consist of one or more tables. Each table has one or more columns and the data is stored as a record (or row) in one of the tables. So make up your mind, be sure to know the difference between database table column and record en then clarify your question.

#6

Thank you everyone for respond,

The code above brings up 6 data per page, right?

I want each page to get 6 data, but not to start over, so 1 and 2 don’t fetch data, I want it to start with third data.

#7

The two queries in your current code are not correct. If your desire to start the pagination at the 3rd page is to get the correct result, the solution is to fix the queries.

Pagination requires two queries. The count query and the data retrieval query must have the same FROM … JOIN, WHERE, GROUP BY, and HAVING conditions, so that they operate on the same set of data. You should actually form this part of the query in a common php variable, then use this variable when building both queries. You should also limit the maximum pageno to the total number of pages (if it’s greater than the total number of pages, due to a programming mistake, someone bookmarking a link to data that no longer exists, or nefarious use, the query won’t match anything, and running the query will be a waste of resources.)

The things that are different between the two queries are what you SELECT and the data retrieval query has ORDER BY and LIMIT terms in it.

Edit: you should also use table alias names in your query so that it is less verbose.

#8

If you use the above suggestions, you should end up with code that looks like this (untested) -

<?php

// define records per page
$no_of_records_per_page = 6;

// define common part of the query
$common_sql = "FROM tblposts p
		LEFT JOIN tblcategory c on c.id=p.CategoryId
		LEFT JOIN tblsubcategory sc on sc.SubCategoryId=p.SubCategoryId
		WHERE p.Is_Active=1";
// note: if any of the values being put into any sql query are from an external/unknown source, use a prepared query.

// build the count query
$count_query = "SELECT COUNT(*) $common_sql";

// execute the count query and calculate the total number of pages
$result = mysqli_query($con,$count_query);
$total_rows = mysqli_fetch_array($result)[0];
$total_pages = ceil($total_rows / $no_of_records_per_page);

// condition the pageno input. default to page 1
$pageno = isset($_GET['pageno']) ? (int)$_GET['pageno'] : 1;
// note: pageno can be any plus or minus integer at this point

// limit the maximum (do this first since total pages can be zero) and minimum pageno
if($pageno > $total_pages)
{
	$pageno = $total_pages;
}
if($pageno < 1)
{
	$pageno = 1;
}

// calculate the LIMIT offset
$offset = ($pageno-1) * $no_of_records_per_page;

// build the data retrieval query
$data_query = "SELECT p.id as pid, p.PostTitle as posttitle, p.PostImage, c.CategoryName as category,
		c.id as cid, sc.Subcategory as subcategory, p.PostDetails as postdetails, 
		p.PostingDate as postingdate, p.PostUrl as url
		$common_sql
		ORDER BY p.id desc
		LIMIT $offset, $no_of_records_per_page";

// execute the data retrieval query
$result = mysqli_query($con,$data_query);
#10

Thank you for helping me
I think I didn’t explain the information well, because my English seems to be weak.
I’ve drawn exactly what I want,
I hope I have expalined the information well.

I tryed this SELECT * FROM Orders LIMIT 2, 6 but not work
Thank you again.

2056×2388 354 KB

#11

Wanting to do this indicates there’s something wrong with your data design. You cannot guarantee and should not rely on specific data existing or being in any particular order in a database table. If you delete data or backup/restore your database, the position of the data in the table can be different.

Why do you want to skip the first two pages of data? What is specific about the data that tells you (as a human) that you want to start with the 3rd page instead of the 1st page or any other page?

Stated another way, if a human cannot understand why you want to do this, what chance is there of being able to write code or a query that does what you want?

BTW - google’s online translator does a good job with word-order and word-form.

#12

Thanks for your persistent help me,
I am a beginner in programming and specifically in php, I try to add Div into publications, as the picture shows in the attachments.

Why do I do this because I am not a professional in programming, I try to repeat the code after div.

I hope I have explained the idea well.

906×2913 185 KB

#13

You would use a counter variable and a conditional statement testing that variable, inside the loop where you are producing the output.

Sponsor our Newsletter | Privacy Policy | Terms of Service