moving from id to id php db querys

[Original Poster]

ok hi havent been here in a while as i havent realy needed it much yay me mostly stand on my own two feet thanks to you guys all teaching me... but now im back for another lesson...

basicly i been building a website (duh LOOL ) and its just a basic photo album kind of script im working on at the momment... basicly we cant quite wrap our heads around how to get it to move to the next id... we thought of the obvious $id = $id ++1 ect.... but then i thought what if a person deletes a photo leaving a hop... e.g. photo id 1, photo id 2, photo id 4. if you get me ? so any way any ideas would be great ..

[Kool Box]

How is the data being stored, MySQL database for example. Usually your query would involve extract all matching photos based on a particular criteria. When the information is extracted you can add a line like $photoid = $row["id"]. This basically states for every row that your SQL query retrieves, it will see what the id is for your photo and places it into $photoid variable for which you can manipulate as you please.

To be honest I guess it does depends how your data is being extracted, e.g., within a for loop. Maybe a code snippet is advised :)

[Original Poster]

How is the data being stored, MySQL database for example. Usually your query would involve extract all matching photos based on a particular criteria. When the information is extracted you can add a line like $photoid = $row["id"]. This basically states for every row that your SQL query retrieves, it will see what the id is for your photo and places it into $photoid variable for which you can manipulate as you please.

To be honest I guess it does depends how your data is being extracted, e.g., within a for loop. Maybe a code snippet is advised :)

it is basicly collected like tht... it is decided using user id ... e.g. userid=1 ... and pid=123 (user id and pic id) and it will be grabbed in using the snippet u put and the to limit it to one result so not to flood the page with pictures obviously its limited :p... but on the selection page they can slect what photo icon to start from.. so they could start at photo 5 and i want them the have the ability to move back and forth ..

[Original Poster]

the first snip shows all the photos being displayed and giving u the chocie... the second is the individual .. :) its plain with no design at mo but im a phper not a csser :p

<?php

session_start();

?>



<?php



$_GET['id'];



include('conalt.php');



$query = "SELECT * FROM `pics` WHERE `userid` = $id";



$result = mysql_db_query($db, $query); 







if ($result) 



{



while ($r = mysql_fetch_array($result)) { // Begin while 



$uid = $r["userid"]; 



$pid = $r["photoid"]; 



$aid = $r["albumid"]; 



$location = $r["location"]; 



$un = $r["username"]; 



$upda = $r["id"];



echo "<a href='/site/photo.php?pic=$pid'><img src='$location' width='123' height='124'></a>";

}

}



?>

<?php

session_start();

?>



<?php



$_GET['id'];



$pic = $_GET['pic'];



include('conalt.php');



$query = "SELECT * FROM `pics` WHERE `photoid` = $pic";



$result = mysql_db_query($db, $query); 







if ($result) 



{



while ($r = mysql_fetch_array($result)) { // Begin while 



$uid = $r["userid"]; 



$pid = $r["photoid"]; 



$aid = $r["albumid"]; 



$location = $r["location"]; 



$un = $r["username"]; 



$upda = $r["id"];



echo "<img src='$location'>";

}

}



?>

[AnthonySterling]

Take a look into the LIMIT clause. :)

<?php
#http://www.server.com/?id=3&pg=3
$result = mysql_query(
	sprintf(
		'SELECT * FROM pics WHERE user_id = %d LIMIT %d, 1',
		isset($_GET['id']) ? $_GET['id'] : 0,
		isset($_GET['pg']) ? $_GET['pg'] : 0
	)
);
?>

[Original Poster]

ok i can see mostly what thts doing :/ but thts a format of php i've never done before could your break it down a lil bit ? no point me using what i cant use :p cheers for your help :) its mainly the %d i know its a wild card making it more wide of a search kind of thing but im still confused on how tht allows me to create a next feature :/

Edited August 23, 2009 by hackncrap

[Kool Box]

If you wish to add "navigation" into a query so the user can effectively go to the next image or previous image you will have to code in an offset into your SQL query. This means if you want them to see the nth picture in the list you will code your PHP to add the offset into the SQL query like:

$query = "select * from pictures limit ".$offset.",25";

Or in your case:

$query = "SELECT * FROM `pics` WHERE `photoid` = $pic limit ".$offset.",$NumberOfPicturesPerPage";

Offset will have to be calculated on what the user input was. This is because you might numerically show image 3 but most database systems start from 0, so the 3rd image would actually be 2. Sorry if this is a bit tongue twisty in description, but I have to make sure I explain some basic stuff so that the code will make a little more sense later ;)

[Original Poster]

ahh ok im getting this a bit more, ye our data base is set to auto increment... so if a lets say id 5 has been deleted it will not create another 5 just skip it..

so lets say .... user input is the "next" button... what would the code hav to be... to move from picture 146537 to picture 43985438

[Kool Box]

Well this is it, your code if written correctly handles this directly. What I am saying is that you don't have to hard code for that scenario, the nature of SQL, the array that stores the results will handle this. So as you say, if I SQL query all fruits in a basket, and someone takes out apples, I don't have to write my code differently, the SQL query will show all remaining fruits in basket and I will navigate through those results.

[Original Poster]

=D ok i know what u mean... stores it all and pics through the data thts all far and good. so u saying tht code will allow to move on to the next result? its just really moving on to the next result... sorry if im sounding like a complete retard... but its just i have never done anything like this... :p lool im just sayay gahh i understand whats happening now but just not how to get ahhh! lool il figure somthing out i suppose :/

[Kool Box]

Yes it will move onto the next result -> it will keep looking for next id's or pictures until the loop is over. That is the part you will code to say how the loop stops, e.g., cause the "results array" is empty or the offset has been matched so if you were showing 25 images per page, if there are only 23 then theoretically you must be on the "last page" of images.

[Original Poster]

ok cool cheers il get installing tht ... sorry for late reply i was out getting wasted like a professional :p wow have i really been gone for 3 days