[megglz]

Looking for advice on how to have gallery navigation with php.
Currently I have the gallery set up dynamically so that each gallery reloads on the same page but with a different id (which i get from the mysql database) for example, gallery.html?id=5. At the end of each page i'd like a gallery navigation system with previous ( for ex. gallery.html?id=4) and next (for ex. gallery.html?id=6).

This is my code

<div class="scroll-gallery">
			 <?
			 mysql_connect("$DBHost","$DBUser","$DBPass");
				$result=mysql("$DBName","SELECT * FROM project where id = '$projid'");
	   while($row=mysql_fetch_row($result)) {
			   $pid=$row[0];
			   $pid1 = $pid+1;
			   $pid2 = $pid-1;
	   $result2=mysql("$DBName","SELECT COUNT(id) FROM project");
	   while($row=mysql_fetch_array($result2)) {
	   $totalNum = $row['COUNT(id)'];
	   }
	   }


		  if ($pid>1){ echo"<a href=\"project.html?id=$pid2\"
class=\"prev\">Prev Project</a><span class=\"separ\">"; }
		  if ($pid<$totalNum){ echo"<span class=\"separ\"><a
href=\"project.html?id=$pid1\" class=\"next\">Next Project</a><span
class=\"scr-numb\">"; }


			?>


	   </div>

But as its going by the id in the database, if a record is erased from the database i need it to skip to the next available id number otherwise a blank page will show. I cant get it to do this please help

[pemcconnell]

The best way to do it would be as follows:

1. Add an extra column into your table - this will be the id that you will use to reference the page, as a pose to the primary key. As it will be a highly used search column I would index it, but 'tis up to you.

2. When inserting a page / image into your table, before the insert satement run something like this:

$sql = "SELECT sortOrder FROM project ORDER BY sortOrder DESC LIMIT 0,1";
$result = mysql_query($sql);
$row = mysql_fetch_assoc($result);
$sortorder = intval($row['sortOrder']) + 1;

that means that you now have the next highest sort order ready to insert into your new record (the same way as an auto-increment works)

3. Run your insert statement, inserting your new 'next highest sort number' in your new column.

// INSERTS OVER, GRAND, NOW FOR THE DELETE AND UPDATE

4. When you delete an item, pass the sort number aswell as the id :

mysql_query('DELETE FROM projects WHERE projectId = '.(int)$_REQUEST['id']);
//BELOW IS THE CODE THAT WILL UPDATE THE REST OF YOUR ITEMS
mysql_query('UPDATE projects SET sortOrder = (sortOrder - 1) WHERE sortOrder > '.(int)$_REQUEST['sortorder']);

Now, that means you can go from 1 -> number of projects you've got without missing any numbers and you'll always have a record for it, even if the row id's are all over the place.

Reason for doing all this: It's easier than trying to controll the auto-increment on a primary key.

I'm away to get pished now so I won't be back on till monday, but there's plenty of top coders in this forum that would be glad to help ya if you're still stuck.

Good Luck

This post has been edited by pemcconnell: 22 August 2008 - 09:02 AM

[megglz]

Thanks so much for your reply. I'll try my best with it

[megglz]

I tried doing it using an array, anyone think this would work?

$numbers = array();
	   <div class="scroll-gallery">
					<?
					mysql_connect("$DBHost","$DBUser","$DBPass");
				$result=mysql("$DBName","SELECT * FROM project where id = '$projid'");
				 while($row=mysql_fetch_row($result)) {
				 
				 array_push($numbers,$row[0]); 
				 
				 }
				 
				 sort($numbers, [SORT_NUMERIC]);
				 array_count_values($numbers);
				 $total = array_count_values($numbers);
				 
	foreach ($numbers as $number) {
	
			  if ($number>1){ echo"<a href=\"project.html?id=$number\"
	class=\"prev\">Prev Project</a><span class=\"separ\">"; }
			  if ($number<$total){ echo"<span class=\"separ\"><a
	href=\"project.html?id=$number\" class=\"next\">Next Project</a><span
	class=\"scr-numb\">"; }
	
	
				?>
	
	
	   </div>

[JBrace1990]

that would work pemconnel, BUT, then the IDs change, and any bookmarked pages are messed up because they point to the wrong id =)

$sql = mysql_query("SELECT * FROM project WHERE id >= $id_to_search_for LIMIT 1")or die(mysql_error());

=P

irishgirl, no, I don't think it would... I can see a few errors...

anyway, if you're going to check all of them, why don't you just check them in the while loop instead of adding an extra foreach? you're doing more work then you need to =)

[megglz]

I've tried something else which seems to work perfectly apart from one tiny problem:

	<?
					mysql_connect("$DBHost","$DBUser","$DBPass");
$result=mysql("$DBName","SELECT MAX(id) FROM project where id < '$projid' LIMIT 0,1");
while($row=mysql_fetch_row($result)) {
$previous = $row[0];
			}

$result2=mysql("$DBName","SELECT MIN(id) FROM project where id > '$projid' LIMIT 0,1");
 while($row2=mysql_fetch_row($result2)) {
$next=$row2[0];
		}

$result3=mysql("$DBName","SELECT COUNT(id) FROM project"); while($row3=mysql_fetch_array($result3)) {
 $totalNum = $row3['COUNT(id)'];
	   }

  if ($previous>1){echo"<a href=\"project.html?id=$previous\"
	class=\"prev\">Prev Project</a><span class=\"separ\">"; }

   if ($next<$totalNum) { echo "<span class=\"separ\"><a
	href=\"project.html?id=$next\" class=\"next\">Next Project</a><span
	class=\"scr-numb\">"; };
				?>

I think my problem here is

if ($previous>1){echo"<a href=\"project.html?id=$previous\"
	class=\"prev\">Prev Project</a><span class=\"separ\">"; }

It works fine for all id's but as the first id in database is 1, when it moves to the next id (3), it doesnt give a "previous" link on this page back to the project id 1. I've it set to if ($previous>1), could this be why? :s

This post has been edited by irishgirl: 23 August 2008 - 02:58 AM

[JBrace1990]

"SELECT MAX(id) FROM project where id < '$projid' LIMIT 0,1"

your statement is off... try this:

SELECT id FROM project WHERE id < '$projid' ORDER BY id DESC LIMIT 1

this will get the highest id that is less then the project id... right now you're getting the first record from the databasae (IE: id 1), making your if statement never work...

You're using while statements, and limiting it by one... why not just get rid of the while statements?

[pemcconnell]

Quote

that would work pemconnel, BUT, then the IDs change, and any bookmarked pages are messed up because they point to the wrong id =)

I didn't think about looking at it from the point of bookmarks, so good point, however you could have a custom 'add this page as bookmark' and save the url with the ID as a pose to the sortOrder.

This post has been edited by pemcconnell: 25 August 2008 - 12:59 AM

[megglz]

pemcconnell, on 25 Aug, 2008 - 12:54 AM, said:

Quote

that would work pemconnel, BUT, then the IDs change, and any bookmarked pages are messed up because they point to the wrong id =)

I didn't think about looking at it from the point of bookmarks, so good point, however you could have a custom 'add this page as bookmark' and save the url with the ID as a pose to the sortOrder.

thanks everyone i got this sorted