Jump to content

Show number of products per page.

Recommended Posts

I need to come up with a script that will get the number of products under a category and return the number of products shown on each page in


Showing 1-20 Products out of (total number)


I have this MYSQL which I'm also using to generate pagination.

$query5 = "SELECT COUNT(*) as num FROM $tbl_name WHERE product_category='$cat'";
$total_pages = mysql_fetch_array(mysql_query($query5));
$total_pages = $total_pages[num];

/* Setup vars for query. */
$targetpage = "store.php?cat=".$cat; 	//your file name  (the name of this file)
$limit = 20; 								//how many items to show per page
$page = $_GET['page'];
$start = ($page - 1) * $limit; 			//first item to display on this page
$start = 0;								//if no page var is given, set start to 0


The products are pulled from the database here:

$sql30 = "SELECT * FROM $tbl_name WHERE product_category='$cat' LIMIT $start, $limit";
$result30 = mysql_query($sql30);


I came up with one way, but it will only work when there are 20 products on the page. If there's less it won't calculate that amount.

Link to comment
Share on other sites

I'm not understanding the exact problem. Are you saying that the pages where there are less than 20 records are not displaying the records or that the total pages isn't being calculated correctly?


Although, based on your variable names I think the problem may be here:

$query5 = "SELECT COUNT(*) as num
           FROM $tbl_name
           WHERE product_category='$cat'";
$total_pages = mysql_fetch_array(mysql_query($query5));
$total_pages = $total_pages['num'];

That would NOT be the total pages, that would be the total records. To get total pages you take the total records divided by the $limit, rounded up.


Something like

$query = "SELECT COUNT(*) as num
           FROM $tbl_name
           WHERE product_category='$cat'";
$result = mysql_query($query);
$total_records = mysql_result($result, 0);
$total_pages = ceil($total_records/$limit); //Be sure limit is defined before this


Also, there is no need to define a bunch of variables such as $query1, $query2, $query3, etc. Once a query is run you can simply redefine the previous variable. I typically use $query and $result. It is very rare that I need to maintain a query or result when I run the second query. If you DO need to maintain those variables, at least give them descriptive names.

Link to comment
Share on other sites

I guess I didn't explain myself well enough.



I'm trying to generate something like this:


"Showing 1 - 3 out of 3 products" or "Showing 1 - 20 out of 25 products"


I have no idea how to do that... I posted the MySQL in my previous post to show what existing code I have (which is working for pagination) to use to generate the above text.

Link to comment
Share on other sites

OK, well, you are still using the wrong variable name of "$total_pages" which actually represents the total_records, but I'll provide some sample code based upon what you have. This uses the variables you defined above: $start, $limit, $total_pages, and $result30

$records_on_page = mysql_num_rows($result30);
$start_record = ($start * $limit) + 1;
$end_record = $start_record + $records_on_page -1;
echo "Showing {$start_record} to {$end_record} out of {$total_pages} products";

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.