Adding Next And Previous Function To An Edit Form

[RLAArtistry]

Hey guys and gals, it me again. I need help with my php coding.

 

OBJECTIVE

I have a form that will allow me to edit products in my inventory. I want to add a next and a prev link to browse the inventory. When the last item in the inventory is reached the next link disappears and visa versa.

 

So far I query the database

if (isset($_GET['pid'])) {// the id for the product to be edited is passed via URL
$thisID = $_GET['pid'];

$sql = "SELECT * FROM products WHERE id='$thisID' LIMIT 1";
$query = mysqli_query($Connection, $sql) or die (mysqli_error());
$productCount = mysqli_num_rows($query); // count the output amount
 if ($productCount == 1) {
 while($row = mysqli_fetch_array($query)){
		 $pid = $row["id"];
   $name = $row["name"];
   $price = $row["price"];
   $desc = $row["desc"];
   $cat = $row["cat"];
   $subcat = $row["subcat"];

    }
   } else {
 echo "There was an error processing your request";
 exit();
   }
}

 

The above I am good with... It renders the information of the product to be edited in the form and I have another bit of code that UPDATES the database. Now I just need help on the next and previous link to browse the database. I came up with this lame bit..

<a href="?pid=<?php echo ++$pid; ?>">Next</a>   <a href="?pid=<?php echo --$targetID; ?>">Prev</a>

I don't think "--$targetID" is even legal. I search php.net and haven't seen anything remotely looking like it, but strange enough it allows me to decrease "$pid" value.

 

Any idea will be appreciated.

 

Thanks in advance.

[Zane]

I don't think "--$targetID" is even legal. I search php.net and haven't seen anything remotely looking like it

You didn't search hard enough

http://php.net/manua...

Simply incrementing or decrementing your pid variable isnt safe. For instance, what if you delete a product, then that id no longer exists in a perfectly normalized database table.

 

If you want the previous and next id you will have to select them and limit your results to 3

 

SELECT * FROM products WHERE
id='$thisID' OR
id<'$thisID' OR
id>'$thisID' LIMIT 3

 

To keep the other products' info from disrupting your current while loop, you will have to check whether $row['id'] == $thisID.

Edited October 1, 2012 by Zane

[requinix]

Don't assume that the ID numbers are sequential. If you want me to go into the full rant about doing so I will, but basically: ID numbers are arbitrary numbers with the sole purpose of being unique, and when you assign this "previous is ID-1" and "next is ID+1" meanings you change the concept into something it is not.

 

I would do two more queries to grab the previous and next records:

SELECT id FROM products WHERE some condition indicating the record is before the current one ORDER BY some field DESC LIMIT 1
SELECT id FROM products WHERE some condition indicating the record is after the current one ORDER BY some field ASC LIMIT 1

If you go by a date, such as a creation or modification date, then it would be like

SELECT id FROM products WHERE modification_date < modification_date from this record ORDER BY modification_date DESC LIMIT 1
SELECT id FROM products WHERE modification_date > modification_date from this record ORDER BY modification_date ASC LIMIT 1

Plus you get to know if there is, in fact, a previous and/or next record at the same time.

[Psycho]

SELECT * FROM products WHERE
id='$thisID' OR
id<'$thisID' OR
id>'$thisID' LIMIT 3

 

To keep the other products' info from disrupting your current while loop, you will have to check whether $row['id'] == $thisID.

 

That would not work. ALL the records in the table would match one of those three conditions. Using LIMIT 3 would not pull the three records you want - it would pull the first three records by whatever the sort order it - if none is given then it is usually by the order the records were created. Therefore, that query would always pull the first three records regardless of the value of $thisID

[Zane]

Hmm.. I wondered whether that would work or not. I should have mentioned that it was not a guaranteed successful query. But for certain, the IDs will have to be specifically selected.

[RLAArtistry]

Thanks for your replies.

 

You didn't search hard enough

http://php.net/manua...

Apparently I didn't search hard enough. Here I thought I made a break-through in php coding.

 

 

I would do two more queries to grab the previous and next records:

SELECT id FROM products WHERE some condition indicating the record is before the current one ORDER BY some field DESC LIMIT 1
SELECT id FROM products WHERE some condition indicating the record is after the current one ORDER BY some field ASC LIMIT 1

If you go by a date, such as a creation or modification date, then it would be like

SELECT id FROM products WHERE modification_date < modification_date from this record ORDER BY modification_date DESC LIMIT 1
SELECT id FROM products WHERE modification_date > modification_date from this record ORDER BY modification_date ASC LIMIT 1

Plus you get to know if there is, in fact, a previous and/or next record at the same time.

The thought did came to mind if an item was deleted from the database then that would throw off the sequence. I did also thought about using another table but could not come up with something to do this = "some condition indicating the record is before the current one".

[RLAArtistry]

Once again thank you guys very much for your help.

 

I combined the suggestions of Zane and Requinix to come up with a solution. As I mentioned before I considered using a table but could not figure out how to set the condition that indicates that the record is before the current one. Zane's code gave me the idea to use the "<" and ">" to get the next and previous record.

 

Here is the code:

///////////////////////// NEXT QUERY ///////////////////////////////////////
$nextbtn = "";
$prevbtn = "";
$next = "";
$prev = "";
$browse = "SELECT * FROM products WHERE pro_name>'$pro_name' ORDER BY pro_name ASC LIMIT 1";
$browse_query = mysqli_query($Connection, $browse) or die (mysqli_error());
$result = mysqli_num_rows($browse_query); // count the output amount
 if ($result == 1) {
 while($row = mysqli_fetch_array($browse_query)){
		 $pro_name = $row["pro_name"];
   $pro_id = $row["id"];
 }
 $nextbtn = "NEXT";
}else{
 $nextbtn = "";
}
$next = "inventory_edit.php?pid=$pro_id";

/////////////////////////// PREVIOUS QUERY ////////////////////////////////
$browse = "SELECT * FROM products WHERE pro_name<'$pro_name' ORDER BY pro_name DESC LIMIT 1";
$browse_query = mysqli_query($Connection, $browse) or die (mysqli_error());
$result = mysqli_num_rows($browse_query); // count the output amount
 if ($result == 1) {
 while($row = mysqli_fetch_array($browse_query)){
		 $pro_name = $row["pro_name"];
   $pro_id = $row["id"];
 }
 $prevbtn = "PREVIOUS";
}else{
 $prevbtn = "";
}
$prev = "inventory_edit.php?pid=$pro_id";

Currently this works and even if I delete an item from the inventory it goes on to the next or previous one. The next/previous links appears and disappears when the results from the query = 0 or 1.

 

If anyone have any other suggestions or modifications please do share, otherwise this post is "SOLVED".

 

Thank you