Previous and Next

[darryncooke]

Here's a link -

http://www.darryncooke.com/dcremix/darryn-cooke-work.php

- If you click one of the items you will get to that item details section.

Above it you will see a previous and next text for what I want the user to go to the next or previous item.

right now the dbase has 10 instances and I tried the ++ and -- operators - however that would take me past 10 and the -- was not working.

How do I get this to work when my recordset result is only 1. I essentially want to add 1 or subtract one from the proj_id of the existing page. But I want it to stop when it hits the last (or highest) number in the table.

Any help - and my PHP knowledge is very basic to beginner just and FYI.

Thank you,

Darryn Cooke

http://www.darryncooke.com

| Marketing and Creative Services

 

[jpadie]

basically you need to know how many records (and thus pages) exist. you can either do this with two queries or with a single query and two fetches, depending on the database. google SQL_FOUND_ROWS.

there is also an FAQ posted on data paging generally. faq434-5244

 

[darryncooke]

Ok so I think i would like to see if there is a different approach.

Can I create a Select statement that selects the whole table but has whatever proj_id is be the one that shows up first?

This would then allow me to just paginate through the data (not sure if that makes sense).

Darryn Cooke

http://www.darryncooke.com

| Marketing and Creative Services

 

[darryncooke]

I went a different route

I made 2 more select statements - 1 for each previous and next using the LIMIT function. Then I used PHP to show or hide the navigation based on whether or not the query was empty or not.

Im sure there is an easier way but I just don't have the PHP expertise to tackle it.

Darryn Cooke

http://www.darryncooke.com

| Marketing and Creative Services

 

[Geates]

Min
SELECT proj_id FROM table ORDER BY ASC LIMIT 1

Max
SELECT proj_id FROM table ORDER BY DESC LIMIT 1

-Geates

"I hope I can chill and see the change - stop the bleed inside and feel again. Cut the chain of lies you've been feeding my veins; I've got nothing to say to you!"
-Infected Mushroom

"I do not offer answers, only considerations."
- Geates's Disclaimer

 

[darryncooke]

Previous Button

SELECT proj_id
FROM projects
WHERE proj_id < $var
ORDER BY proj_id DESC LIMIT 0, 1

Next Button

SELECT proj_id
FROM projects
WHERE proj_id > $var
ORDER BY proj_id  ASC LIMIT 0, 1

For the Navigation

$data1 = ($row_prev);
$data = ($row_next);

if(empty($data1))
{
echo "";
}
else {
echo "<a href=\"darryn-cooke-work-detail.php?proj_id=$row_prev[proj_id]\">previous project</a>";
}

if(empty($data1) || empty($data))
{
echo "";
}
else {
echo "&nbsp\&nbsp";
}


if(empty($data))
{
echo "";
}
else {
echo "<a href=\"darryn-cooke-work-detail.php?proj_id=$row_next[proj_id]\">next project</a>";
}

For some reason I feel the navigation part can be a bit cleaner but this works.

Darryn Cooke

http://www.darryncooke.com

| Marketing and Creative Services

 

[jpadie]

i'm not sure why you are getting stuff for previous and next etc. isn't that unnecessary?

better, perhaps to set the current page as a session variable and then just have links that tell your server to display the previous page or the next page. you can suppress the previous link by reference to the current page number. a bit more effort to suppress the 'next' link as you'd need to know the total number of pages. but not a lot.

session_start();
$numPages = isset($_SESSION['numPages']) ? $_SESSION['numPages'] : 1000; //arbitrarily large number
$offset = isset($_SESSION['curPage']) ? $_SESSION['curPage'] : 0;
$offset = isset($_REQUEST['nextPage']) && $offset < $numPages ? $offset++ : $offset;
$offset = isset($_REQUEST['prevPage']) && $offset > 0 ? $offset++ : $offset;
$maxItems = 10;
$sql = "
SELECT SQL_CALC_FOUND_ROWS *
FROM table name
LIMIT $maxItems
OFFSET " . $offset * $maxItems;
//run main query
$result = mysql_query($sql);

//run max rows query
$sResult = mysql_query("SELECT FOUND_ROWS()");
$row = mysql_fetch_array($sResult);
//update session variable
$_SESSION['numPages'] = $numPages = ceil($rows[0] / $maxItems); 
//iterate over recordset as normal
while ($row = mysql_fetch_assoc($result)):
 // do stuff
endwhile;

 

[darryncooke]

The reason why I want next and previous is so that the user doesnt have to go back to see all the projects. I just want them to page through rather than go back and forth.

Secondly, while I am positive there are many ways to skin this cat, even way more efficient than I did I am in no means an intermediate, let alone expert, of PHP/MySQL. I can get by with a decent amount of requests and I understand the logic. I just don't know either well enough to execute on such methodologies.

I do appreciate the suggestion and maybe one day I will be able to do this, but at the moment its a bit over my head.

Darryn Cooke

http://www.darryncooke.com

| Marketing and Creative Services

 

[jpadie]

the point is that it is not necessary to execute separate queries for previous and next and current for each page. all you need is current. then just store the current value for manipulation if you later get a next or previous request.

 

[darryncooke]

jpadie i did that. problem was when i ran out of records it would return an empty result. i didnt know how to work around that.

Darryn Cooke

http://www.darryncooke.com

| Marketing and Creative Services

 

[jpadie]

if ($offset  > $_SESSION['numPages']):
  //beyond the end of the dataset
else if($offset == $_SESSION['numPages']):
  //on the last page
else:
  //not at the end of the dataset
endif;

 

[darryncooke]

I appreciate your help. i tried for about 2 hours and couldnt get it to work. i follow your logic but i wasn't getting any values for any of the session variables.

I tried many ways to skin the cat that you handed me but no luck. The only way this would work is if you did all the work for me and I cannot ask that. But I do appreciate your help.

Darryn Cooke

http://www.darryncooke.com

| Marketing and Creative Services