Nhoj Posted January 18, 2007 Share Posted January 18, 2007 I have a script that orders every user by an ever changing number descending very often and then updates their ranking starting with 1 and moving on until every user is covered... Is there a more effecient way to do it than this?[code=php:0]$query = mysql_query('SELECT `uID` FROM `users` ORDER BY `uNetWorth` DESC');$i = 0;while ($player = mysql_fetch_assoc($query)) { $i++; mysql_query('UPDATE `users_list` SET `uRanking` = '.$i.' WHERE `uID` = '.$player['uID'].' LIMIT 1');}[/code]Thanks ::) Link to comment https://forums.phpfreaks.com/topic/34681-a-more-effecient-way-to-update-a-large-table/ Share on other sites More sharing options...
anatak Posted January 18, 2007 Share Posted January 18, 2007 the only way I think to make it more efficient is by making a stored procedure.that way you don't have the trafic between php and mysql. I am not sure how much more efficient it will be thoughif I had to do this I would create a temporary table with an autoincrement.insert the uId into that table so that way you have the ranking (autoincrement) and the uId and then update the users_list tableanatak Link to comment https://forums.phpfreaks.com/topic/34681-a-more-effecient-way-to-update-a-large-table/#findComment-163457 Share on other sites More sharing options...
Nhoj Posted January 18, 2007 Author Share Posted January 18, 2007 How would I go about making that into a stored procedure? - I've never used em...http://dev.mysql.com/tech-resources/articles/mysql-storedprocedures.html (Wasn't much help >:() Link to comment https://forums.phpfreaks.com/topic/34681-a-more-effecient-way-to-update-a-large-table/#findComment-163493 Share on other sites More sharing options...
anatak Posted January 18, 2007 Share Posted January 18, 2007 well that is the big problem it has been 5 years since I used stored procedures so I am not going to be a great help.But I would post this question in the MySQL forum. I guess your chances are a lot bigger there than here.I don't think you can make it more efficient using php.an easy fix would be to use a cron job you run when your traffic is lowest (if that is ever)anatak Link to comment https://forums.phpfreaks.com/topic/34681-a-more-effecient-way-to-update-a-large-table/#findComment-163496 Share on other sites More sharing options...
Nhoj Posted January 18, 2007 Author Share Posted January 18, 2007 I'll poke around a bit, the traffic is usually always high, not always, but usually... The ranking needs to be updated every time a users information changes and that happens an incredible amount of times per day... Link to comment https://forums.phpfreaks.com/topic/34681-a-more-effecient-way-to-update-a-large-table/#findComment-163519 Share on other sites More sharing options...
anatak Posted January 18, 2007 Share Posted January 18, 2007 This may be a stupid questionwhy do you want to update the table ?you have the ranking already with the uNetWorth.if you want to display the ranking why don't you use the $query = mysql_query('SELECT `uID` FROM `users` ORDER BY `uNetWorth` DESC');$i = 0;while ($player = mysql_fetch_assoc($query)) { $i++; echo "<tr><td>rank: ".$i."username: ".$player['uNAME']."</td></tr>". }you have to build a full table around if of course Link to comment https://forums.phpfreaks.com/topic/34681-a-more-effecient-way-to-update-a-large-table/#findComment-163531 Share on other sites More sharing options...
tcollie Posted January 18, 2007 Share Posted January 18, 2007 I agree with anatak for displaying your overall ranking listing on your rankings page. Now the code below is what I use for displaying the user's rank on their stats page. It's the same concept as yours, but I don't have a designated rank field like you. I just use networth. It does add a separate call to the db, but it works for me.[code]$query = "SELECT COUNT(*) as rank FROM users WHERE networth > (SELECT networth FROM users WHERE uid = '$uid')"; $res = mysql_query($query); $stats['rank'] = mysql_result($res, 0) + 1;[/code]And the credit for that piece of code actually goes to Barand. I needed that piece of code for something I was working on for my daughter's school, and it also worked out great for my game project. Link to comment https://forums.phpfreaks.com/topic/34681-a-more-effecient-way-to-update-a-large-table/#findComment-163535 Share on other sites More sharing options...
Nhoj Posted January 18, 2007 Author Share Posted January 18, 2007 I already do the $i loop for my rankings display, the only thing I wanted to use the ranking variable for is displaying someones stats.Thanks ;D, now I can get rid of t all together Link to comment https://forums.phpfreaks.com/topic/34681-a-more-effecient-way-to-update-a-large-table/#findComment-163656 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.