Probably simple > Previous & Next

[siwis]

I guess there's a first time to request help rather than dole it out 🙁

I have a MySQL db containing product information.

Table - products contains the actual product detail with joins to:
Table - products_manufacturers (manufacturer info)
Table - products_categories (Primary categories)
Table - products_subcategories (subcategory information)

The app is currently functioning perfectly, traversing down through category > manufacturer > subcategory > product OR category > subcategory > product but what I want to achieve is that once the script has arrived at the end product, I want a previous and next link to pull out the next or previous record which matches the criteria for category/manu/subcat (or) category/subcat.

I'm assuming I will need to find the key from the subcategory query for the viewed product then query to find the next key and previous key in that query and extract the relevant product_id to match to each of those keys. Easy I think! d'oh not so quick!

Anybody point me in the right direction?

[remote2]

let say you have a query that pull out what you wanted...
then you can use LIMIT in the statement to restrict your query result..

let say you have

SELECT * FROM products

but this may return 100 products but you want 10 products on 1 page...

then do this...

SELECT * FROM products LIMIT 0,10

this will return the first 10 items... start from row 0 and include the next 10 items..

so use a variable to see which page you are on...

$start = $page_num 10;
SELECT FROM products LIMIT $start, 10

hope it helps...

[siwis]

sorry remote2 but you've misunderstood what I'm after:

I'm looking for a way to show a link for the previous and next products on the page for an individual product.

Lets say my subcategory page holds 10 products, ORDERED by price and their respective ID's are 77, 62, 279, 99, 34, 1043, 1012, 1, 74, 106 (in their respective price order).
Now when I'm viewing product id 1043, I want to create a link for the previous product (ie product id 34) and a link for the next product (ie product id 1012) from the page for product id 1043.

This quite obviously relies on the query from the subcategory page because product 1043 is in key position 5, 34 is key 4 and 1012 is key 6 BUT how the hell do I go about retrieving that information. Would me counting through the keys of the previous page query until I reach the key containing my current product id and then re-running the query with the LIMIT set to $key_true - 1,3 work? or is there a better less ugly and db intensive method via a simple query?

[buraks78]

i think mysql_row_tell is what you are looking for.

from the mysql manual

Returns the current position of the row cursor for the last mysql_fetch_row(). This value can be used as an argument to mysql_row_seek().

for detailed info go to http://www.mysql.com/doc/en/mysql_row_tell.html

hope that helps...

[siwis]

Thanks. I shall check out that reference, although I've now achieved it the "dirty" way ;-)

[sfullman]

Seems to me this is quite simple,

If your initial query was
[select * from products, etc. where CONDITION] limit n, z

as we loop through the recordset we increment $i++ (starts off at a value of 1).

Then, the query for the PREVIOUS record will be:
[select * from product, etc. where CONDITION] limit (n + $i - 2), 1

and the query for the NEXT record will be:
[select * from product, etc. where CONDITION] limit (n + $i), 1

provided these numbers make sense AND that no products are added in between times. As long as the condition stays the same throughout each PREV/NEXT query, of course.

Let me know if there's a more fundamental thing I'm missing.

Sam Fullman
Compass Point Media

[siwis]

Thanks for the help sfullman. Although your solution doesn't appear to take averything into account, it is at least the basics of what I've done:
This follows my standard query

$pr_query = mysql_query("SELECT * FROM $prodtable WHERE (prod_id = $prodid)", $db);

followed by check to ensure an empty row hasn't been returned then

list($prod_id, $manu_id, $prod_cat_id, $prod_subcat_id, $prod_status, $prod_offer_status, $prod_rrp, $prod_price, $prod_offer_price, $prod_model, $prod_year, $prod_size, $prod_colour, $prod_image, $prod_image_dim, $prod_image_thumb, $prod_image_thumb_dim, $prod_custval_0, $prod_custval_1, $prod_custval_2, $prod_custval_3, $prod_custval_4, $prod_custval_5, $prod_custval_6, $prod_custval_7, $prod_custval_8, $prod_custval_9, $prod_custval_10, $prod_custval_11, $prod_custval_12, $prod_custval_13, $prod_custval_14, $prod_custval_15, $prod_custval_16, $prod_custval_17, $prod_custval_18, $prod_custval_19, $prod_custval_20, $prod_custval_21, $prod_custval_22, $prod_custval_23, $prod_custval_24, $prod_custval_25, $offer_comments) = mysql_fetch_row($pr_query);
// Previous/Next Discovery (perform query for parent subcategory)
				$pevnext_query = mysql_query("SELECT prod_id FROM $prodtable WHERE (prod_manu_id = $brand AND prod_cat_id = $mcat AND prod_subcat_id = $scat AND prod_status = 1) ORDER BY prod_price $disp_prod_order", $db);
//Set a key position and count through it to discover the key of the product we're viewing
				$key_pos = "0";
					while ($pevnext = mysql_fetch_array($pevnext_query))	{
						$product_id = $pevnext['prod_id'];
						if ($product_id == $prod_id)	{
							$prevkey = $key_pos - 1;
						};
						$key_pos++;
					};
//Cater for the first record which would return a $key_pos of -1
					switch($prevkey)	{
						case "-1":
							$limit = 2;
							$prevkey = 0;
							break;
						default:
							$limit = 3;
					}
// Query to get 3 records, prev/current/next unless record is first in which case return current/next
				$prevnext_query = mysql_query("SELECT prod_id FROM $prodtable WHERE (prod_manu_id = $brand AND prod_cat_id = $mcat AND prod_subcat_id = $scat AND prod_status = 1) ORDER BY prod_price $disp_prod_order LIMIT $prevkey, $limit", $db);
				$prevcount = "1";
					while ($prevnext = mysql_fetch_array($prevnext_query))	{
						$prevnext_id = $prevnext['prod_id'];
							switch($prevcount)	
							{
								case "1": 
									$prev_id = $prevnext_id;
									if ($prev_id == $prod_id)	{
										$prev_id = "";
									};
									break;
								case "2": 
									if ($prevnext_id != $prod_id)	{
										$next_id = $prevnext_id;
									};
									break;
								case "3": 
									$next_id = $prevnext_id;
									break;
							}
						$prevcount++;
					};
				if ($prev_id != "")	{
					$prev_link = "&lt;&lt;<a href=\"" . $PHP_SELF . "?mcat=" . $prod_cat_id . "&brand=" . $manu_id . "&scat=" . $prod_subcat_id . "&prodid=" . $prev_id . "\">PREVIOUS PRODUCT</a>";
				};
				if ($next_id != "")	{
					$next_link = "<a href=\"" . $PHP_SELF . "?mcat=" . $prod_cat_id . "&brand=" . $manu_id . "&scat=" . $prod_subcat_id . "&prodid=" . $next_id . "\">NEXT PRODUCT</a>&gt;&gt;";
				};

Then the links are displayed using

<p align=right><?PHP echo $prev_link; ?> <?PHP echo $next_link; ?></p>

Like I said, its a bit "dirty" but it works! If anybody can see a way of tidying it up or making it simpler, please make any suggestions 😃