Php/sql Find Current Row Position Within Db?

[shadowx 0]

Ok the story is im making an image gallery, so far so good. Currently im working on the page that shows the larger version of a single image. My gallery is database driven using the DB to store things like image title and description as well as the thumbnail file source and full size file source.I want a previous/next function on this page (already have one on the thumbnail view page). that in itself is easy! But my problem is this: Say i have 10 images. the user clicks on image 7 and that image is shown in the large view. How does my PHP script know if this image is the first, last or neither? the obvious answer is through the unique ID field of my database. However i am allowing the user (or developer really as i will be the one customizing this gallery) to change the ORDER BY value to any field in the DB (through editing a central settings file) So for example we could have this set up where the data returned via an sql query with "ORDER BY Image_Name ASC" is sorted by image name in ASCENDING order: (yet the DB remains untouched and is still sorted by default using the ID field.)ID Image_Name3 A_image1 B_image2 C_image4 D_imageSo if i am showing "C_image" how does my PHP know its position within the database? It cant go by the ID value, as that is 2 but this is the third row. There is no current_row(); function or similar in PHP. And the DB is still ordered by ID. Im really stumped by this one... If i knew the current row position i could do a simply query to search the DB for all rows AFTER the current one. If that returns ANY result it means the current row is NOT the last (as there are rows after it) and do the same but opposite for any previous rows. But for that i need to have a LIMIT clause with a START value which would be the row number of the current row. Anyone have any ideas? If i can get this to work then i dont know what ill do... i would need some sort of field in the DB that is automatically re-indexed when the gallery is loaded to order the DB by the user selected field (eg image name) and then automatically assign the new field and then i could work out if there are any rows after the current one by using the new field as a reference, but thats way too complex!

[jlhaslip 4]

It would be a good idea to have that auto-incremental "id" number anyway. Use it as the principal identifier for the row. Index the pictures based on that, then you can modify all of the other elements of the row at will. If you use the 'name' of the picture and change it, you will be altering the index, which will present a whole set of issues you need to deal with.Specific to your question, perform the query, place the resuklts into an array and use the array index to know the pictures placement in the list of available images. ???

[shadowx 0]

I have an auto increment ID field anyway, always have and always do!

I suppose i could use the array version as they would be in the order i want. This might sound a stupid question but how would i get the entire DB into an array at once? I generally use something like:

while($rows = mysql_fetch_array($result)){Do something....;}

But as i understand it that simply takes one row at a time and puts it into the $rows variable.

I was sure there was a function to return the current row number or something but i couldnt find it online. Or is there a query that can return the last row in the DB (as well as use an ORDER BY clause so i could have something like SELECT LAST FROM table ORDER BY name ASC and it would order the query by name ascending and then find the last result. Then i can simply compare its unique ID to the ID i have for the current row. If they match i am working with the last row, if they dont then i know im not working with the last row. Id still need a way to reference the next row in the DB though for the "next" link to find....

Either that or i drop the ability to use next/previous links in the big view and work on that later

[Nabb 0]

I'm pretty sure that $rows is an array, hence the name 'mysql_fetch_array'. You'd want to retrieve from the array the value referenced by the pointer 'inc' (name of your incremental column)... _{heh, I hope it sounds like I know what I'm takling about}

$rows['inc']

Replace 'inc' with whatever your incremental column is called and you're set to go

[truefusion 3]

I think you may be making it appear harder than what it really is. Consider the following code:

while($rows = mysql_fetch_array($result)){$images[] = $rows;}if ((intval($_GET['id'])-1) < 0){$prev_id = 0;} else {$prev_id = intval($_GET['id']);}if ((intval($_GET['id'])+1) > count($images)){$next_id = count($images);} else {$next_id = intval($_GET['id']);}/* Post current image */echo "<img src=\"get_image.php?id=".$images[$_GET['id']]['id']."\"/>";/* Post Previous and Next */echo "<a href=\"image.php?id=".$prev_id."\">Previous</a> <a href=\"image.php?id=".$next_id."\">Next</a>";

Do note that i have not tested this piece of code and it obviously has a couple of flaws, but you should be able to get the idea nonetheless.

[shadowx 0]

ah yeh i get it! I think! Make a new array and compare the current position + 1 with the last position in the array. If the current + 1 is more than the last position then we are at the end and vice versa with the first position. Thanks for that, its given me something to think about, ill have a look at the DB structure and see which field is easiest to use etc... Midswing in the admin upload/add images part at the moment. Dont you love it when things just work?!

[sonesay 7]

Another PHP topic I somehow missed. Is there a way to subscribe to certain forums or threads? OK back to the topic on hand. I am working on a similar problem but not exactly the type of ordering you are doing. How did you get along with it by the way? I think the responses above already give a good solution that would appear to work.

[Pankyy 0]

Another PHP topic I somehow missed. Is there a way to subscribe to certain forums or threads? OK back to the topic on hand. I am working on a similar problem but not exactly the type of ordering you are doing. How did you get along with it by the way? I think the responses above already give a good solution that would appear to work.

Look, I don't know how you want it exactly to work, but I'll give you an example that could help you as well.

if ($_GET['o']){if ($_GET['o'] == asc){$orderby = "ORDER BY id ASC";} // Don't EVER set the order by the $_GET itself, they could do mysql injection hackif ($_GET['o'] == desc){$orderby = "ORDER BY id DESC";}				   } else { $orderby = "ORDER BY id DESC";} // Order by DESC as default$q = mysql_query("SELECT * FROM images $orderby");::::ETCETCETC::::<a href='viewimage.php?o=asc'>Click here to order them from first to last</a><a href='viewimage.php?o=desc'>Click here to order them from last to first</a>

Hope that helped you, if you need further help just tell me.

PD: to suscribe click on the options box near the rating and you'll be able to 'suscribe to topic' or forum.
Edited February 26, 2009 by Pankyy (see edit history)

[sonesay 7]

Umm, no I wasn't exactly after that problem. You really want to know ? OK I am needing to build an interface that lets you add/delete records in then move them around in different orders. any help ?

[Pankyy 0]

That would fair easy.

First create a table 'tablerecords' with 2 necessary things.(if there are more, that's depending what you want to do). The first is called id, give it INT value, and put it up as an auto-incrementing value, the other one called positionid, giving it INT value mode.

Then, with that interface, you create a record, let's say:

mysql_query("INSERT INTO tablerecords (informationrow) VALUES (informationrowvalue)");

And then, if you want a record to move up or down, just do the following:

<?if ($_GET['a'] && $_GET['i']){if ($_GET['a'] == 'up'){$string = "positionid = positionid + 1";} else if ($_GET['a'] == 'down'){$string = "positionid = positionid - 1";}mysql_query("UPDATE tablerecords SET $string WHERE id = {$_GET['i']}");										 }<a href='changerecord.php?i=X&a=up'>Click here to move this record one place up</a><a href='changerecord.php?i=X&a=down'>Click here to order this record one place down</a>

I think that would fit what you are asking for, doesn't it?

[sonesay 7]

That is somewhat where I was heading but your code does not handle the swapping of position id values. Remember I said I needed it so users can be able to move items(records) around in position and they may also be able to delete entries.

so If I have

item1 position id = 1item2 position id = 2item3 position id = 3item4 position id = 4

if you moved item3 up one position with your code you will have two items (item2 and item3 with position of 2), also you should not be able to move item1 up anymore since its the first in the list. I do appreciate your input but I'm still trying to cover all the bases here so I'm still thinking it through.

Also consider if you were to delete item3 and then try to move item4 up. It would still be the last item on the list with a new position id of 3. I think the only way to do it is to get the previous or next items id and swap them around. That way gaps are ignored where items used to be.

[Quatrux 4]

The way I do it, is I SELECT the last sorting method to a session, for example if he is browsing the Gallery by date ascending, SELECT all the IDs to s $_SESSION and you'll have an array of position IDs there, it might be 2,3,4,6,8,1,6 and whatever, if he changes the sorting to lets say alphabetical/by category, reselect the position IDs to that session array.The bad side about this is if new pictures appear in real time (somebody uploads them), you won't see them in the next, previous links of the gallery. [so for this to not happen, you always need to do a SELECT to get positioning and another SELECT to get your data in every request]it looks something like this:Gallery_images tablefield_ID, field_Data, Date_uploaded1, something1, 2009-02-012, something2, 2009-02-023, something3, 2009-02-03SELECT field_ID From Gallery_images ORDER BY Date_uploaded ASC;You'll get an array with values: $Array = {1,2,3}so when you'll be seeing picture nr2, you'll know that the next one is 3 the previous one is 1 and so on..When you'll change the ORDER BY to something else, you'll lets say get $Array = {2,1,3}so when you'll be seeing picture nr1, you'll know that the next one is 3 and the previous one is 2..I think you got the idea? ;]And to continue, when you know positioning in current request for printing the links to next/previous, get your Picture Data by SELECT * From `Gallery_images` WHERE `field_ID` = '2'; // to Select picture nr2 for example.. and show it..

Edited February 27, 2009 by Quatrux (see edit history)

[Pankyy 0]

That is somewhat where I was heading but your code does not handle the swapping of position id values. Remember I said I needed it so users can be able to move items(records) around in position and they may also be able to delete entries.
so If I have

item1 position id = 1 item2 position id = 2 item3 position id = 3 item4 position id = 4

if you moved item3 up one position with your code you will have two items (item2 and item3 with position of 2), also you should not be able to move item1 up anymore since its the first in the list. I do appreciate your input but I'm still trying to cover all the bases here so I'm still thinking it through.

Also consider if you were to delete item3 and then try to move item4 up. It would still be the last item on the list with a new position id of 3. I think the only way to do it is to get the previous or next items id and swap them around. That way gaps are ignored where items used to be.

I think I've missed that because I was just doing a light version of it until I got to know that was concretely what you wanted.

Look, for fixing that with my code just do the following:

<?if ($_GET['a'] && $_GET['i']){if ($_GET['a'] == 'up'){$string = "positionid = positionid + 1"; $string2="positionid = positionid - 1";} else if ($_GET['a'] == 'down'){$string = "positionid = positionid - 1"; $string2="positionid = positionid + 1";}mysql_query("UPDATE tablerecords SET $string WHERE id = {$_GET['i']}");$r = mysql_fetch_array(mysql_query("SELECT id,positionid FROM tablerecords WHERE id = {$_GET['i']}"));$q = mysql_fetch_array(mysql_query("SELECT id FROM tablerecords WHERE positionid = {$r['positionid']} AND id != {$r['id']}"));mysql_query("UPDATE tablerecords SET $string2 WHERE id = {$q['id']}");										 }?><a href='changerecord.php?i=X&a=up'>Click here to move this record one place up</a><a href='changerecord.php?i=X&a=down'>Click here to order this record one place down</a>

Current code will first move up or down the position of the record and after that it will put 1 place down or 1 place up depending in where it is. If you want to make it a dynamic position movement:

<?if ($_GET['a'] && $_GET['i'] && $_GET['di']){if ($_GET['a'] == 'up'){$string = "positionid = positionid + {$_GET['di']}"; $string2="positionid = positionid - {$_GET['di']}";} else if ($_GET['a'] == 'down'){$string = "positionid = positionid - {$_GET['di']}"; $string2="positionid = positionid + {$_GET['di']}";}$r = mysql_fetch_array(mysql_query("SELECT id,positionid FROM tablerecords WHERE id = {$_GET['i']}"));if (($r['positionid'] < $_GET['di']) <= 0){die("You can't reduce more.");}mysql_query("UPDATE tablerecords SET $string WHERE id = {$_GET['i']}"); $r = mysql_fetch_array(mysql_query("SELECT id,positionid FROM tablerecords WHERE id = {$_GET['i']}")); $q = mysql_fetch_array(mysql_query("SELECT id FROM tablerecords WHERE positionid = {$r['positionid']} AND id != {$r['id']}")); mysql_query("UPDATE tablerecords SET $string2 WHERE id = {$q['id']}");										 }

Just add this one below the other one and indicate in a get statemtn the 'di' value (being this one the dynamic number of positions you're going to move it).

[sonesay 7]

Add in some handling for the first and last items and that's what I've got so far. I'm more asking about ideas rather then code. how one would handle records being removed. You will obviously have gaps in the in the position list. Would you reorder the position right away on delete so that no gap exist. I think I gave a clear description of what I intended to build on the previous posts. There is so many ways to do it right, I'm just trying to figure out the best possible solution. I think after an items is deleted the records after it will need to be shifted back 1 in position id. That way my existing add function still works where it will insert the position id correctly of number of records plus 1. What about if I wanted to be able to choose where I inserted new records later on. I will obviously need to reorder the proceeding records with new position id's +1. I know this is getting a bit more complicated but I sort of had this idea of what I wanted from the start. I still want to be able to add the ability to edit records directly on the same page listing without a refresh so I still haven't thought about how I am going to do that. Not to mention I am considering to make it Ajax based. Hm I would even want to be able to drag my records and move them into positions interactively . If anyone has come across something similar and it has been coded before that would save a lot of time let us know. Other wise it seems that I will have to write it all from scratch. I've seen drag gable user interfaces in ext js library but I am not too keen to using such a big JavaScript library. So if anyone would like to work on it lol I can give you the source code and sql table I have so far.

[Pankyy 0]

Add in some handling for the first and last items and that's what I've got so far. I'm more asking about ideas rather then code. how one would handle records being removed. You will obviously have gaps in the in the position list. Would you reorder the position right away on delete so that no gap exist. I think I gave a clear description of what I intended to build on the previous posts. There is so many ways to do it right, I'm just trying to figure out the best possible solution.
I think after an items is deleted the records after it will need to be shifted back 1 in position id. That way my existing add function still works where it will insert the position id correctly of number of records plus 1. What about if I wanted to be able to choose where I inserted new records later on. I will obviously need to reorder the proceeding records with new position id's +1.

I know this is getting a bit more complicated but I sort of had this idea of what I wanted from the start. I still want to be able to add the ability to edit records directly on the same page listing without a refresh so I still haven't thought about how I am going to do that. Not to mention I am considering to make it Ajax based. Hm I would even want to be able to drag my records and move them into positions interactively .

If anyone has come across something similar and it has been coded before that would save a lot of time let us know. Other wise it seems that I will have to write it all from scratch. I've seen drag gable user interfaces in ext js library but I am not too keen to using such a big JavaScript library.

So if anyone would like to work on it lol I can give you the source code and sql table I have so far.

In fact with the code I've written down when you delete one record, the order keeps still the same and it won't mess anything.
Maybe you're confusing with something, but if you do a mysql_fetch_array with an 'ORDER BY positionid' you'll still have the same positions, unchanged, nothing will be messed up, only thing you would actually need to change from my code is

$q = mysql_fetch_array(mysql_query("SELECT id FROM tablerecords WHERE positionid = {$r['positionid']} AND id != {$r['id']}"));if ($q){mysql_query("UPDATE tablerecords SET $string2 WHERE id = {$q['id']}");}

instead of

$q = mysql_fetch_array(mysql_query("SELECT id FROM tablerecords WHERE positionid = {$r['positionid']} AND id != {$r['id']}"));mysql_query("UPDATE tablerecords SET $string2 WHERE id = {$q['id']}");

You don't have to worry when you have 1,2,3,7,9,12 as positionids, after all, what you want is for them to be ordered in a way, and not for them to have numbers as a ladder, don't you?

I actually think you're overcomplicating the things (and I know that, there were times when I wanted to code something the way I started coding it, even if I knew it would take out more time or wasn't as efficient as other way; the best thing in these cases is to start over).

It's not I'm not giving out ideas, I'm giving out code because if you understand it (I suppose you do, else ask) you'll get my idea, and then you'll be able to perform your script.

About the AJAX based script thing, you can check out the tutorial thread I created at the tutorial forum about MooTools 1.2 AJAX request, it's what I think you want to do.