the next or previous valid sql value

[Zeradin]

I'm trying to make a yearbook type of thing where if you're looking at an image it's getting data about it from an sql table. i want a [previous] [back to main] [next] on the top to skip to the next image. is there an easy way to make the previous and next links populate with the next valid sql id value?

 

for instance i'm at yearbook.php?id=6 and i want the next to be a link to yearbook.php?id=7 and the previous to be a link to yearbook.php?id=5

 

also if this is possible, is there a way to make it loop back to the biggest id if it's on 1? and back to 1 if it's at the end?

 

thanks

[Maq]

It may be worth taking a look at pagination.

 

But in your case you can probably get away with using some simple logic.

 

Try it yourself and come back with questions.

[Zeradin]

I will check out pagination, but if you mean what I think you do by logic, it'd matter if there were sql id values that weren't used, yes?

[Maq]

What I mean by,

 

But in your case you can probably get away with using some simple logic.

 

is this:

 

If you're only showing 1 image per page you can create the next, previous, and main by just doing a simple query by ID.

 

For example, if someone is on the first image and clicks next you should pass the ID (?ID=1) and when the page refreshes you do (in pseudo):

 

$new_id = $_GET['ID'] + 1;
SELECT image_id FROM table WHERE ID = $new_id;
//display image
//create new links
Next>

 

Pagination is much better because it's more dynamic in case you want to alter how many images are on the page or if you want to do something like:'

>

[Zeradin]

What I mean by,

 

But in your case you can probably get away with using some simple logic.

 

is this:

 

If you're only showing 1 image per page you can create the next, previous, and main by just doing a simple query by ID.

 

For example, if someone is on the first image and clicks next you should pass the ID (?ID=1) and when the page refreshes you do (in pseudo):

 

$new_id = $_GET['ID'] + 1;
SELECT image_id FROM table WHERE ID = $new_id;
//display image
//create new links
<a href="../this_page?ID=$new_id">Next></a>

 

Pagination is much better because it's more dynamic in case you want to alter how many images are on the page or if you want to do something like:'

<< First  1 2 3 4 5 6 7 8 9  Last >>

 

This is great advice, but it doesn't solve the problem if sql ids are missing. how's that work?

[Zeradin]

nor the circular thing

[Maq]

You're right!  Here is the solution for this problem.  It will get the next biggest ID that's in the table.

 

$prev_id = $_GET['ID'];
$sql = "SELECT id FROM table WHERE id > $prev_id ORDER BY id LIMIT 1";

[SammyP]

Well, one suggestion I'd make would be to add an extra optional parameter to the page URL structure.

 

So if you ask for yearbook.php?id=6&cmd=prev then you do this, in pseudo, before you show anything:

 

$id = ID parameter

if cmd exists

{

  if cmd=prev

  {

    $newid = SELECT Max(ID) WHERE ID < $id and whatever else you need for this set of images

    if $newid

      $id = $newid

    else

      $id = MAX(ID)

  }

  else do similarly for cmd=next

}

 

Do this before showing anything, so you can show the correct ID which is now in $id, and then you can simply use the following URLs:

 

yearbook.php?id=$id&cmd=prev

yearbook.php?id=$id&cmd=next

 

This method will at least not mean any extra processing if the user never clicks Next or Prev.

 

Hope that helps.

 

 

 

[Zeradin]

I don't really understand that.

 

What would happen on the initial view of an image?

 

also... well i'm getting through the rest of it now. Thanks, also wondering why this isn't working?

	$next_id = $id;
$previous_id = $id -1;
while(!isset($nextinfo) && $next_id < 100){
$nextquery = 'SELECT * FROM yearbook WHERE id = '.$next_id;
$nextresult = mysql_query($nextquery);
$nextinfo = mysql_fetch_assoc($nextresult);
$next_id++;
}

 

i think i am going to use your method, I'm just curious as to why that while loop only increments it once when it gets to the end of the results.

[DarkerAngel]

this is a possible solution: Just off the top of my head so I don't know how well its going to work.

 

SELECT * FROM yearbook ORDER BY id ASC LIMIT {$page}, 1

 

In this way it is not page dependent rather the method used to sort it. if you use an id it will not matter but with this way pages can be changed when something is removed.

 

Just a thought remember: LIMIT (starting record), (number of records)

 

EDIT: just a note the first starting record is always 0 (zero)

[Zeradin]

actually, that last question, it wasn't smart, how about why this:

if(!isset($cmd))
{
  if ($cmd == 'prev')
  {
    $newidresult = mysql_query('SELECT MAX(id) FROM yearbook WHERE id < $id');
$newid = mysql_fetch_assoc($newidresult);
    if(!isset($newidresult)){
$previd = $newid['id'];
}
    else {
      $previd = mysql_query('SELECT MAX(id) FROM yearbook');
}
  //else do similarly for cmd=next
}
}

is giving me a null result?

[Zeradin]

this is a possible solution: Just off the top of my head so I don't know how well its going to work.

 

SELECT * FROM yearbook ORDER BY id ASC LIMIT {$page}, 1

 

In this way it is not page dependent rather the method used to sort it. if you use an id it will not matter but with this way pages can be changed when something is removed.

 

Just a thought remember: LIMIT (starting record), (number of records)

 

EDIT: just a note the first starting record is always 0 (zero)

 

interesting. i'll digest that later.

[Zeradin]

actually, that last question, it wasn't smart, how about why this:

if(!isset($cmd))
{
  if ($cmd == 'prev')
  {
    $newidresult = mysql_query('SELECT MAX(id) FROM yearbook WHERE id < $id');
$newid = mysql_fetch_assoc($newidresult);
    if(!isset($newidresult)){
$previd = $newid['id'];
}
    else {
      $previd = mysql_query('SELECT MAX(id) FROM yearbook');
}
  //else do similarly for cmd=next
}
}

is giving me a null result?

 

I still can't figure this out. I guess the syntax is a little foreign to me, but i'm pretty sure it should work.

[Maq]

Have you tried my suggestion?

[Zeradin]

Have you tried my suggestion?

 

I didn't even see that. I am pretty confused right now though...

 

  if ($cmd == 'prev')
  {
$newidquery = "SELECT id FROM table WHERE id > $id ORDER BY id LIMIT 1";
    $newidresult = mysql_query($newidquery);
//mysql_query('SELECT MAX(id) FROM yearbook WHERE id < $id');
$newid = mysql_fetch_assoc($newidresult);
    if(!isset($newidresult)){
$previd = $newid['id'];
}
    else {
      $previd = mysql_query('SELECT MAX(id) FROM yearbook');
}

gives a null result but...

the query->result->id-> fetch stuff is kinda driving me crazy, when you query just an id can you use the result or is it still treated as an array?

[SammyP]

This won't give you an id.

$previd = mysql_query('SELECT MAX(id) FROM yearbook');

 

That gives you a result object and you have to use that to fetch a row, which will be an array. As you did above. There is no great shortcut for this, though I've written my own and have it in a globals page which I always call, so I can always use it.

 

function getsqlval($sql, $default = null) {
  $res = mysql_query($sql);
  if ($row = mysql_fetch_row($res)) return $row[0];
  return $default;
}

 

So whenever I need to run a query just to get one number I can use this:

$previd = getsqlval('SELECT MAX(id) FROM yearbook');

 

Use that if you like.

 

[Zeradin]

Yeah but i still get a null value when do the fetch assoc on it?

[Zeradin]

that is i'm doing

  if ($cmd == 'prev')
  {
$newidquery = "SELECT id FROM table WHERE id > $id ORDER BY id LIMIT 1";
    $newidresult = mysql_query($newidquery);
//mysql_query('SELECT MAX(id) FROM yearbook WHERE id < $id');
$newid = mysql_fetch_assoc($newidresult);
    if(!isset($newidresult)){
$previd = $newid['id'];
}
    else {
      $newidresult = mysql_query('SELECT MAX(id) FROM yearbook');
  $newid = mysql_fetch_assoc($newidresultid);
  $previd = $newid['id'];
}

and then <a href="prpyearbook.php?id='.$previd.'&cmd=prev"> is giving me <a href="prpyearbook.php?id=&cmd=prev">

[Maq]

1) Always put or die(mysql_error()) at the end of your query calls to make sure you don't have an error.

2) You need to get $cmd via $_GET first.

3) Let's start with previous...

 

$cmd = $_GET['cmd'];

if ($cmd == 'prev') {
   $newidquery = "SELECT id FROM table WHERE id > $id ORDER BY id LIMIT 1";
   $newidresult = mysql_query($newidquery) or die(mysql_error());
   $newid = mysql_fetch_assoc($newidresult);
   $previd = $newid['id'];
} else {
   echo "it's not previous ;(";
}

?>

Previous

[Zeradin]

Ok now I'm even more confused.

first of all when someone initially clicks on an image there'll be no value for previous because it's not processed by the "if cmd = prev" - that only happens if they click back

 

and then also the next page won't process the  'if cmd = next' because it'll have prev in its url

 

also, it still doesn't work

 

	//$id = ID parameter
$cmd = $_GET['cmd'];
if(isset($cmd))
{

if ($cmd == 'prev') {
   $newidquery = "SELECT id FROM table WHERE id > $id ORDER BY id LIMIT 1";
   $newidresult = mysql_query($newidquery) or die(mysql_error());
   $newid = mysql_fetch_assoc($newidresult);
   $previd = $newid['id'];
} else {
   echo "it's not previous ;(";
}

 

right? still the same null link

what the crap

[Maq]

I echoed out $newidquery, let me know what it says.

 

$id = $_GET['id'];
$cmd = $_GET['cmd'];
if(isset($cmd))
{

if ($cmd == 'prev') {
   $newidquery = "SELECT id FROM table WHERE id > $id ORDER BY id LIMIT 1";
   echo $newidquery;
   $newidresult = mysql_query($newidquery) or die(mysql_error());
   $newid = mysql_fetch_assoc($newidresult);
   $previd = $newid['id'];
} else {
   echo "it's not previous ;(";
}

[Zeradin]

SELECT id FROM yearbook WHERE id > 25 ORDER BY id LIMIT 1

[Zeradin]

Ok, i took out all the if statements and i think i got it to work:

 

   $newidquery = "SELECT id FROM yearbook WHERE id < $id ORDER BY id DESC LIMIT 1";
   $newidresult = mysql_query($newidquery) or die(mysql_error());
   $newid = mysql_fetch_assoc($newidresult);
   $previd = $newid['id'];

 

i just gotta get the go to end if it's the first etc down. thanks for getting me to think about it.

[Maq]

I think you can check to see if it's the first or last by simply checking to see if your query returned anything:

 

if($newidresult) {
   $newid = mysql_fetch_assoc($newidresult);
   $previd = $newid['id'];
}

[Zeradin]

Yeah that works. Thanks! I'll keep this unsolved until i actually get it all working. but it's looking good=)