links for next or previous in category - PHP

[LizzyFin]

Looking for a little assistance in understanding what I am missing!

Using PHP/MySql to allow users to browse through items in a gallery. Clicking 'next' and 'previous' links works fine using the item ID number to sequentially see all items.

Trouble arises when category and ID variables are passed. I'm thoroughly confused on how to let the user click a 'next' link after they have entered a category page in order to see the next item in that category. The "elseif ($ID AND $cat)" statement is the offending code. Here's my script if anyone can make suggestions. Thanks in advance for any help!

<?php
// Check the database for correct authorization.
$host = "xx;
$user = "xx";
$password = "xx";
$db = "soaps";
mysql_connect ($host, $user, $password) or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db("soaps") or die ("Could not select database");
$all = mysql_query("SELECT * FROM soap_inventory") or die ("Query failed here");
$allpix = mysql_num_rows($all);
if (isset($_GET['ID'])) {
$ID = $_GET['ID'];
} else {
$ID = "";
}
if (isset($_GET['category'])) {
$cat = $_GET['category'];
} else {
$cat = "";
}
if ($ID AND !$cat) {
//show single soap if user clicks on a picture (THIS WORKS OK)
$sql = mysql_query("SELECT * FROM soap_inventory WHERE ID=$ID");
$row = mysql_fetch_array($sql);
echo "<img class='thumb' src='images/$row[ID].jpg' width='400px' alt='$row[Name]'><br />";
echo "<br /><strong>$row[Name]</strong><br /><br />";
echo "$&nbsp;$row[Price]<br /><br />";
echo "$row[PayPal_ADD_button]<br /><br />";
echo "$row[PayPal_VIEW_button]<br /><br />";
$IDpre = 0;
$IDnex = 0;//echo "$ID . $IDpre . $IDnex . $allpix";
if ($ID==1) {$IDpre = $allpix;$IDnex=$ID+1;}
elseif ($ID==$allpix) {$IDnex=1;$IDpre=$ID-1;}
else {$IDnex=$ID+1;$IDpre = $ID-1;}
//echo "<br />$ID . $IDpre . $IDnex . $allpix";
echo "<div class='clear'>&nbsp;</div>";
echo "<div class='catnav'>";
echo "<a href='soap_catalog.php?ID=$IDpre'><<< Previous</a>";
echo "<a href='soap_catalog.php'>Soap Catalog</a>";
echo "<a href='soap_catalog.php?ID=$IDnex'>Next >>></a>";
echo "</div>";
}
elseif ($ID AND $cat) {
//(TROUBLE SPOT - NEXT/PREVIOUS LINKS DO NOT WORK)
$sql = mysql_query("SELECT soap_inventory.ID, soap_inventory.Name, soap_inventory.Price FROM soap_inventory, soap_category, soap_cat_lookup WHERE soapid=soap_inventory.ID AND categoryid=soap_category.ID AND categoryid=$cat AND Active=1");
$IDcat = mysql_fetch_array($sql);
$IDrow = $IDcat;
$allpic = mysql_num_rows($sql);
echo "<img class='thumb' src='images/$IDrow[soapid].jpg' width='400px' alt='$IDrow[Name]'><br />";
echo "<br /><strong>$IDrow[Name]</strong><br /><br />";
echo "$&nbsp;$IDrow[Price]<br /><br />";

echo "<div class='clear'>&nbsp;</div>";
echo "<div class='catnav'>";
echo "<a href='soap_catalog.php?ID=$IDrow[0]&category=$cat'>First</a>";
echo "<a href='soap_catalog.php'>Soap Catalog</a>";
echo "<a href='soap_catalog.php?ID=($IDrow[0]+1)&category=$cat'>Next </a>";
echo "</div>";
}
elseif ($cat AND !$ID){
//show soaps in category if user clicks on a link in left column (THIS IS OK)
$sql = mysql_query("SELECT * FROM soap_inventory, soap_category, soap_cat_lookup WHERE soapid=soap_inventory.ID AND categoryid=soap_category.ID AND categoryid=$cat AND Active=1");
while ($cats = mysql_fetch_array($sql)) {
echo "<div class='thumb'><a href='soap_catalog.php?ID=$cats[soapid]&category=$cat'><img class='thumb' src='thumbs/$cats[soapid]-s.jpg' alt='$cats[Name]' width='150px'></a><div class='clear'>&nbsp;</div><h5>$cats[Name]</h5><br />$$cats[Price]<br /><br /></div>";
}
echo "<div class='clear'>&nbsp;</div><a href='soap_catalog.php'>Back to Soaps Catalog</a>";
}
else {
//show complete soap catalog listing (THIS IS OK)
// Select the table to query
$result = mysql_query("SELECT * FROM soap_inventory WHERE Active=1") or die ("Query failed here");
while ($res = mysql_fetch_array($result)) {
echo "<div class='thumb'><a href='soap_catalog.php?ID=$res[ID]'><img class='thumb' src='thumbs/$res[ID]-s.jpg' alt='$res[Name]' width='150px'></a><div class='clear'>&nbsp;</div><h5>$res[Name]</h5><br />$$res[Price]<br /><br /></div>";
}
echo "<div class='clear'>&nbsp;</div>";
}
?>

[Banfa]

$host = "<snipped>";
$user = "<snipped>";
$password = "<snipped>";

I do not know if these where you actual login details (and don't tell me) but you should never under any circumstances post your actual username and password for anything to a public forum. If these were your actual login details then you should change your password immediately.

What you want is not too hard to do, however you will need to re-write the page to change how $ID is used (and possibly rename it).

Currently $ID is the actual ID of the record that you wish to load, your problem is that if you are trying to select pictures by category then the IDs are not in sequence, i.e. you might want to scroll through IDs 1, 5, 6, 50 and 89.

To get round this you should redefine $ID as the index into the list of currently required pictures, be that all pictures or all pictures in a category. Then in your SELECT statements you can use the "LIMIT $id, 1" clause to select a specific picture from the list.

Then no matter what conditions you set on your selection criteria $ID will always be sequencetial, 0 to number of results - 1.

Your select staements would become

For the entire list

SELECT * FROM soap_inventory LIMIT $id, 1

for the categoried list

SELECT soap_inventory.ID, soap_inventory.Name, soap_inventory.Price FROM soap_inventory, soap_category, soap_cat_lookup WHERE soapid=soap_inventory.ID AND categoryid=soap_category.ID AND categoryid=$cat AND Active=1 LIMIT $id, 1

I do not think you need all the tables in this query I think it could be

change how $ID is used (and possibly rename it).

Currently $ID is the actual ID of the record that you wish to load, your problem is that if you are trying to select pictures by category then the IDs are not in sequence, i.e. you might want to scroll through IDs 1, 5, 6, 50 and 89.

To get round this you should redefine $ID as the index into the list of currently required pictures, be that all pictures or all pictures in a category. Then in your SELECT statements you can use the "LIMIT $id, 1" clause to select a specific picture from the list.

Then no matter what conditions you set on your selection criteria $ID will always be sequencetial, 0 to number of results - 1.

Your select staements would become

For the entire list

SELECT * FROM soap_inventory LIMIT $id, 1

for the categoried list

SELECT soap_inventory.ID, soap_inventory.Name, soap_inventory.Price FROM soap_inventory WHERE categoryid=$cat AND Active=1 LIMIT $id, 1

but I would need better visibility of your table structure to be sure of this

[LizzyFin]

Banfa,
Thank you so much for your input. I did fix my problem using your suggestion of changing the ID variable name - that helped to cut the confusion as I now have distinct id numbers for each of my tables. I also used your "LIMIT $id, 1" and an ORDER BY clause and got my navigation to work. I did not realize that a variable could be used in the limit clause, so thanks again!!
Have a great day! :)