fierdor Posted June 29, 2010 Share Posted June 29, 2010 I need to have a sequential field in one of my SQL tables such that even if i add or delete a row the other numbers should automatically adjust and make a sequence.. Autoincrement is one option but it wont work if I delete a certain row in the table... Is it possible to have such a field? Quote Link to comment https://forums.phpfreaks.com/topic/206158-sequence-in-mysql/ Share on other sites More sharing options...
trq Posted June 29, 2010 Share Posted June 29, 2010 Yes its possible but you would need to maintained yourself. May I ask what the field is to be used for? Quote Link to comment https://forums.phpfreaks.com/topic/206158-sequence-in-mysql/#findComment-1078617 Share on other sites More sharing options...
fierdor Posted June 29, 2010 Author Share Posted June 29, 2010 Yeah...basically I am using it to randomise a selection in PHP: My pseudocode: 1. Count the entries 2. rand(1,count)--Randomise a selection between 1 and the count 3. Select the content corresponding to that row number.. Hence in this algo it is necessary that my serial numbers be in sequence so that I can select the content... I am game if there is some other algo which will make my life simple. Quote Link to comment https://forums.phpfreaks.com/topic/206158-sequence-in-mysql/#findComment-1078619 Share on other sites More sharing options...
fierdor Posted June 29, 2010 Author Share Posted June 29, 2010 I stumbled upon a solution while googling: $random_row = mysql_fetch_row(mysql_query("select * from YOUR_TABLE order by rand() limit 1")); But anyways is it possible to generate a sequence field programatically? Quote Link to comment https://forums.phpfreaks.com/topic/206158-sequence-in-mysql/#findComment-1078623 Share on other sites More sharing options...
Mchl Posted June 29, 2010 Share Posted June 29, 2010 I stumbled upon a solution while googling: $random_row = mysql_fetch_row(mysql_query("select * from YOUR_TABLE order by rand() limit 1")); But anyways is it possible to generate a sequence field programatically? It's not a good solution. A better solution is presented in one of these presentations: http://www.phpfreaks.com/forums/index.php/topic,125105.msg959636.html#msg959636 Yes it is possible to generate such field, but it's usually not worth it. Especially if your table gets large, and you need to reorder thousands/millions of rows. Quote Link to comment https://forums.phpfreaks.com/topic/206158-sequence-in-mysql/#findComment-1078633 Share on other sites More sharing options...
fierdor Posted June 29, 2010 Author Share Posted June 29, 2010 Thank you! That was an interesting read...He's stated 4 alternative solutions... However i dont think my row count is going to increase beyond ,say 50 or 100... So I assume the performance benefit will not be so noticeable,right? But that said, I will keep this in mind for future projects..In fact that entire presentation seems to be quite useful.. Quote Link to comment https://forums.phpfreaks.com/topic/206158-sequence-in-mysql/#findComment-1078642 Share on other sites More sharing options...
Mchl Posted June 29, 2010 Share Posted June 29, 2010 Yeah, for 100 rows you can safelu do ORDER BY RAND() Quote Link to comment https://forums.phpfreaks.com/topic/206158-sequence-in-mysql/#findComment-1078643 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.