8 Replies - 1507 Views - Last Post: 25 August 2008 - 03:46 AM Rate Topic: -----

#1 megglz  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 11
  • View blog
  • Posts: 414
  • Joined: 22-August 08

php/mysql project navigation help

Post icon  Posted 22 August 2008 - 08:25 AM

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 :(

Is This A Good Question/Topic? 0
  • +

Replies To: php/mysql project navigation help

#2 pemcconnell  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 54
  • View blog
  • Posts: 472
  • Joined: 05-August 08

Re: php/mysql project navigation help

Posted 22 August 2008 - 09:01 AM

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

Was This Post Helpful? 1
  • +
  • -

#3 megglz  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 11
  • View blog
  • Posts: 414
  • Joined: 22-August 08

Re: php/mysql project navigation help

Posted 22 August 2008 - 10:04 AM

Thanks so much for your reply. I'll try my best with it :)
Was This Post Helpful? 0
  • +
  • -

#4 megglz  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 11
  • View blog
  • Posts: 414
  • Joined: 22-August 08

Re: php/mysql project navigation help

Posted 22 August 2008 - 01:02 PM

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>


Was This Post Helpful? 0
  • +
  • -

#5 JBrace1990  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 109
  • View blog
  • Posts: 760
  • Joined: 09-March 08

Re: php/mysql project navigation help

Posted 22 August 2008 - 03:40 PM

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 =)
Was This Post Helpful? 1
  • +
  • -

#6 megglz  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 11
  • View blog
  • Posts: 414
  • Joined: 22-August 08

Re: php/mysql project navigation help

Posted 23 August 2008 - 02:57 AM

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

Was This Post Helpful? 0
  • +
  • -

#7 JBrace1990  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 109
  • View blog
  • Posts: 760
  • Joined: 09-March 08

Re: php/mysql project navigation help

Posted 23 August 2008 - 09:34 AM

"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?
Was This Post Helpful? 0
  • +
  • -

#8 pemcconnell  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 54
  • View blog
  • Posts: 472
  • Joined: 05-August 08

Re: php/mysql project navigation help

Posted 25 August 2008 - 12:54 AM

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

Was This Post Helpful? 0
  • +
  • -

#9 megglz  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 11
  • View blog
  • Posts: 414
  • Joined: 22-August 08

Re: php/mysql project navigation help

Posted 25 August 2008 - 03:46 AM

View Postpemcconnell, 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
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1