Pauls74462 Posted July 4, 2010 Share Posted July 4, 2010 I have a db and when I delete a row then my Id numbers get out of sink. How to I keep the rows in numeral order after delete of a rod have been done?I need the Id to be in numerical so I can do a random function.Paul Link to comment Share on other sites More sharing options...
thescientist Posted July 4, 2010 Share Posted July 4, 2010 that's just how it works. if you're trying to get random information just check to make there's data being returned (which you should be checking for anyway) from the random id picked, if there isn't then just run the random function again. Link to comment Share on other sites More sharing options...
Pauls74462 Posted July 4, 2010 Author Share Posted July 4, 2010 that's just how it works. if you're trying to get random information just check to make there's data being returned (which you should be checking for anyway) from the random id picked, if there isn't then just run the random function again.Here is how I choose a random users, is there a better way?$result = mysql_query("SELECT * FROM MyTable"); $Choose_rows = mysql_num_rows($result); $Num2Use = rand(1, $Choose_rows);$result = mysql_query("SELECT * FROM Members where id = $Num2Use"); Link to comment Share on other sites More sharing options...
thescientist Posted July 5, 2010 Share Posted July 5, 2010 if it gives you the random result number that suits your needs then yes. My only point is you should be checking for the data being returned by the query in the event the id doesn't exist. Link to comment Share on other sites More sharing options...
dsonesuk Posted July 5, 2010 Share Posted July 5, 2010 say you have a 100 records, you can't guarantee that the id ref is between 1-100, could be 1 to whatever.I found this, i don't know if it will work for youmysql_query("SELECT * FROM Members WHERE RAND() ORDER BY RAND() LIMIT 1");other optionAdd the id ref to an array, get random number as you have, then the random number will relate to the id ref position in the array. Link to comment Share on other sites More sharing options...
Pauls74462 Posted July 5, 2010 Author Share Posted July 5, 2010 say you have a 100 records, you can't guarantee that the id ref is between 1-100, could be 1 to whatever.I found this, i don't know if it will work for youmysql_query("SELECT * FROM Members WHERE RAND() ORDER BY RAND() LIMIT 1");other optionAdd the id ref to an array, get random number as you have, then the random number will relate to the id ref position in the array.The array sounds like a good option, but what is the code for it? Link to comment Share on other sites More sharing options...
dsonesuk Posted July 5, 2010 Share Posted July 5, 2010 code is to look it up.mt_rand() for random number.how to create arrayswhile loop to store record id values in arrayproduce a sql query that will match the id ref in $id_array[$Num2Use]another while loop to show selected random record Link to comment Share on other sites More sharing options...
Synook Posted July 6, 2010 Share Posted July 6, 2010 For small data sets, the ORDER BY RAND() method is probably the easiest: SELECT * FROM Members ORDER BY RAND() LIMIT 1 Otherwise, you may need to look at another method, like dsonesuk's. Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.