Php/sql Find Current Row Position Within Db?

[sonesay 7]

I think that is true in the sense that it would not matter that you have items ordered in 1,2,3,,6,8,10,100. It is still ordered.. I agree on that part but you will still need to determine a value for a newly inserted record depending on where you want to insert it. Having them ordered just looks cleaner and the code you write to do other functions may or maybe benefit from having it nicely ordered. I think you are totally right though that it doesn't matter and it would probably create more overhead than more benefit to the program. I will take a look at the MooTools library but I think that will be later on when I finally decide on these early steps. Thank you for your insight in to this discussion.Update: ORDEREDOK this just popped up in my head. What if the say I had position id's of 1,2,3,....100 (1 - 100) I add a new record after 3 so everything else after it needs to shift up 1. That is going to alot of overhead on updating those records. UNORDEREDNow what if I had position id's of 1,3,7,8,21,57,89. I add a new record in after the 3rd item (position id 7), new position id = 8 for the newly inserted record. Now Checks have to be made for the next item which is position id = 8, If they are the same then you need to increase by 1 and check the next one. You have to keep checking to see until the current record position id is not equal to the next. This seems like its going to get complex either way. Do you see any thing I have missed or got wrong let me know.

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

[Pankyy 0]

I think that is true in the sense that it would not matter that you have items ordered in 1,2,3,,6,8,10,100. It is still ordered.. I agree on that part but you will still need to determine a value for a newly inserted record depending on where you want to insert it. Having them ordered just looks cleaner and the code you write to do other functions may or maybe benefit from having it nicely ordered. I think you are totally right though that it doesn't matter and it would probably create more overhead than more benefit to the program.
I will take a look at the MooTools library but I think that will be later on when I finally decide on these early steps. Thank you for your insight in to this discussion.

Aha, I see where you're heading from, what you can do is, depending in which position you want to put the new record, the following:

//if in top$q = mysql_fetch_array(mysql_query("SELECT textrowwhatever,MAX(positionid) FROM tablerecords"));$positionid = $q['MAX(positionid)'] + 1;$q = mysql_query("INSERT INTO tablerecords (positionid,textrowwhatever) VALUES ($positionid, 'textrowwhatever')");//if in top//if at start$q = mysql_fetch_array(mysql_query("SELECT textrowwhatever,MIN(positionid) FROM tablerecords")); $positionid = $q['MIN(positionid)'] - 1; $q = mysql_query("INSERT INTO tablerecords (positionid,textrowwhatever) VALUES ($positionid, 'textrowwhatever')");//if at start

and I thought an extra minute about what you said about 'white spaces' in the positionid, and I may have messed up with that in the code, i guess you were right. Do check if this one seems ok to you:

if ($_GET['a'] && $_GET['i']){$gettable = mysql_fetch_array(mysql_query("SELECT id,positionid FROM tablerecords WHERE id = {$_GET['i']}"));if ($_GET['a'] == 'up'){$q = mysql_fetch_array(mysql_query("SELECT * FROM tablerecords WHERE positionid > {$gettable['positionid']} ORDER BY positionid ASC LIMIT 1")); $cnt = $q['positionid'] - $gettable['positionid']; $string = "positionid = positionid + $cnt"; $string2="positionid = positionid - $cnt";							} else if ($_GET['a'] == 'down'){$q = mysql_fetch_array(mysql_query("SELECT * FROM tablerecords WHERE positionid < {$gettable['positionid']} ORDER BY positionid ASC LIMIT 1"));  $cnt = $gettable['positionid'] - $q['positionid'];  $string = "positionid = positionid - $cnt"; $string2="positionid = positionid + $cnt";								 }mysql_query("UPDATE tablerecords SET $string 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']}");										 }

[iGuest 3]

thanksPhp/sql Find Current Row Position Within Db?

thats a very useful bit of code. Thanks!

-reply by david