[RESOLVED] pagination help

yahyac

I'm trying to understand and use cgraz's very nice example on pagination, copied here from another thread:

cgraz wrote:

My these are popular. For all of you looking for Next Previous (pagination) Script, here it is

<? 

/*  Replace the following credentials with yours */ 

$server = "localhost"; 
$username = "user"; 
$password = "pass"; 
$database = "your_db"; 
$table = "your_table"; 


// Connect To MySQL Server 
@mysql_connect($server, $username, $password) or die("Couldn't Connect to Database"); 

// Select Database 
@mysql_select_db($database) or die("Couldn't Select Database"); 

// set number of results to display per page (in this case, 10 per page)
$pagelimit = "10"; 

// run query
   $strSQL = mysql_query("SELECT * FROM $table") or die(mysql_error()); 

// count number of matches 
   $totalrows = mysql_num_rows($strSQL); 

// determine how many pages there will be by using ceil() and dividing total rows by pagelimit 
   $pagenums = ceil ($totalrows/$pagelimit); 

// if no value for page, page = 1 
    if ($page==''){ 
        $page='1'; 
    } 
// create a start value 
    $start = ($page-1) * $pagelimit; 

// blank matches found 
echo "<b>" . $totalrows . " matches found</b><br>\n"; 

// Showing Results 1 to 10 (or if you're page limit were 15) 1 to 15, etc. 
$starting_no = $start + 1; 

if ($totalrows - $start < $pagelimit) { 
   $end_count = $totalrows; 
} elseif ($totalrows - $start >= $pagelimit) { 
   $end_count = $start + $pagelimit; 
} 


echo "Results $starting_no to $end_count shown.<br>\n"; 

// create dynamic next, previous, and page links 

/* lets say you're set to show 10 results per page and your script comes out with 12 results. 
this will allow your script to say next 2 if you're on the first page and previous 10 if you're on the second page. */ 

if ($totalrows - $end_count > $pagelimit) { 
   $var2 = $pagelimit; 
} elseif ($totalrows - $end_count <= $pagelimit) { 
   $var2 = $totalrows - $end_count; 
} 

$space = "&nbsp;"; 

// previous link (if you're on any page besides the first, create previous link)
if ($page>1) { 
        echo "« <a href='" . $PHP_SELF . "?page=".($page-1)."' class=main>Previous" . $space . $pagelimit . "</a>" . $space . $space . ""; 
    } 

// dynamic page number links

for ($i=1; $i<=$pagenums; $i++) { 
    if ($i!=$page) { 
        echo " <a href='" . $PHP_SELF . "?page=$i' class=main>$i</a>"; 
    } 
    else { 
        echo " <b>[".$i."]</b>"; 
    } 
} 


// next link (if the page you are on is less than the total amount of page numbers, there are more pages left)

if ($page<$pagenums) { 
    echo "" . $space . $space . $space . $space . " <a href='" . $PHP_SELF . "?page=".($page+1)."' class=main>Next " . $var2 . "</a> »"; 
} 

/* output your data wherever you'd like. 

BUT 

in order for this all to work, before outputting your data, you have to run the query over using MySQL's LIMIT. 
This will limit how many results are actually displayed on the page. */ 

   $strSQL = mysql_query("SELECT * FROM $table LIMIT $start,$pagelimit") or die(mysql_error()); 

// LIMIT 0,10 will start at 0 and display 10 results 
// LIMIT 10,5 will start at 10 and display 5 results 

/* now you can do whatever you'd like with this query. it will only output ten results per page. 
change the $pagelimit variable to whatever to output more than 10 result per page. */ 


?>

There are plenty of ways to do this; this is just my way. It's all commented so make sure you understand how it works before copying and pasting.

Cgraz

My first question is you see how he calls
echo " <a href='" . $PHP_SELF . "?page=$i' class=main>$i</a>";

I understand that $PHP_SELF refers to the current php page. However, if the "current page" was called with parameters, as it ideally would be in this pagination script, does the $PHP_SELF include those parameters.

For example, my page that has the pagination script is a viewproducts.php. One example link to this page is as follows:
http://localhost/products/viewproducts.php?catID=aa&subcatID=fl

When I run the example code shown above, the $PHP_SELF is not including those parameters. If this is how $PHP_SELF works, in order to include the parameters, do i need to change those lines of code as follows:
echo " <a href='" . $PHP_SELF . "catID=$catID&subcatID=$subcatID&page=$i' class=main>$i</a>";

Thanks.

yahyac

I went ahead and made that change, and it seems to work. But could someone please still confirm that it is correct.

Also, when I go to the link:
http://localhost/products/viewproducts.php?catID=aa&subcatID=fl

I get the following:
11 matches found
Results 1 to 3 shown.
[1] 2 3 4 Next 3 »

(just for the sake of an example, I set pagelimit to 3)

So it shows that it is on page [1] and 2, 3, 4 and Next 3 are all links.

However, when I click on 2, for example, although it I do link to this page (http://localhost/products/viewproducts.php?catID=aa&subcatID=fl&page=2), the results still print out as follows:
11 matches found
Results 1 to 3 shown.
[1] 2 3 4 Next 3 »

It should say:
Results 4 to 6 shown.
1 [2] 3 4 Next 3 >>

Can someone explain why this is happening? Is it something I am missing in cgraz's code? Perhaps something wrong?

Thanks.

TheDefender

I think the best palce to start is to post the code you have, in it's modified form... SOmething to consider is... How is Register Globals set in your environment? I suspect it's OFF, so your $page variable doesn't exist... Therefore, when it does this check here:

// if no value for page, page = 1 
    if ($page==''){ 
        $page='1'; 
    }

It's always defaulting your page variable to 1...

Just a guess though, since I haven't seen your code specifically...

yahyac

Thanks for your help.

TheDefender

No problem. Be sure to post back if you need further assistance with this issue, or resolve the thread if you get it figured out.

yahyac

as a side issue, since you happen to be online right now, maybe you can help with a query.

I need to query a products_pricing table (shown below) and and return ONLY one row per product, where that row is the lowest price for that product. Each product has several prices (rows) in this table.

So, of course, in my query i will ORDER BY price. But how do i only return the ONE, and ONLY, lowest price for each product?

Thanks.
Jonathan

Here's the table:

#Create products_pricing table.  Pricing is a multi-valued attribute of
#the products table and thus has its own table shown below.
#item_num is a foreign key in this table and refers to the products table.
CREATE TABLE products_pricing (
	item_num VARCHAR(20) NOT NULL,
	quantity INT,
	price DECIMAL(6,2),
	code CHAR(1),
	PRIMARY KEY (item_num, price, code, quantity),
	INDEX (item_num),
	FOREIGN KEY (item_num) REFERENCES products (item_num)
	ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=INNODB;

TheDefender

Somthing like this perhaps:

SELECT *, MIN(price) FROM products_pricing GROUP BY item_num ORDER BY price

Just shooting from the hip, and it's untested, but it should get you going in the right direction.

yahyac

pretty cool! Thanks much. Didn't know of a MIN command in sql.

TheDefender

The MySQL manual is your friend...
http://dev.mysql.com/doc/

Incidentally, I threw together a very quick DB with an item_num, price, and quantity to mimic your setup, and it worked fine for me... (at least as I understand what you are wanting to do).

Two solves for one thread... Leave the quarter on the table on your way out... ;-) Actually, if you have any other unrelated questions, go ahead and start a new thread... Someone will find it and help you out. ;-)
Have a good day!