Sandeep Jakhar Sandeep Jakhar - 1 year ago 66
MySQL Question

How to check one multi-dimensional array values against another multi-array and update bulk data depending on results, using PHP and MySQLi?

I've a table that has three columns id, points, rank. Timely I update data for all fields so points go up and down but old rankings remains same, so I'm trying to find out a way that entitles each id its deserving rank based on points earned.

I've got more than 2000 rows in this table. I wish to do it in php5+ with mysqli? I think I've a solution but it times out even with 1200 seconds timeout setting and memory gets exhausted.

I think my solution works accurately but any of the loops needs some doctor. Here my rough target is 'update' query to go accurate that takes all points in desc order, and awards id a rank against the points earned:

require_once $_SERVER['DOCUMENT_ROOT'].'/includes/';

$a2= mysqli_query($link, "SELECT COUNT(*) as count FROM p1");
$b2 =mysqli_fetch_array($a2);
$count = $b2['count'];

$a1= mysqli_query($link, "SELECT points FROM p1 ORDER BY points DESC LIMIT $i");
echo mysqli_error($link);
while($po = mysqli_fetch_array($a1)){

$ross[] = $po;
foreach($ross as $pot){

$a5a= mysqli_query($link, "SELECT id FROM p1 WHERE points = '$points'");

while($popo = mysqli_fetch_array($a5a)){

foreach($idi as $idm){

$rank = $i;
$update = mysqli_query($link,"UPDATE p1 SET rank = '$rank' WHERE points = '$points' AND id ='$id'");
echo "Error updating Rank".mysqli_error($link);
} else {
echo "Succuessul for where id = '$id' and points = '$points' set rank = '$rank'<br/>";

Answer Source

I have replaced my original answer with much leaner and shorter code, you can of course include modification to the rank counter if consecutive users have same points but you can figure this yourself

This code have just one loop and is conserving memory and your DB as well

require_once $_SERVER['DOCUMENT_ROOT'].'/includes/';
$a = mysqli_query($link, "SELECT id, points, rank FROM p1 ORDER BY points DESC ");    // lets get users in new ordering
$rank = 1;                                  // new ranks
while($line = mysqli_fetch_array($a)){
  if ($rank != $line["rank"]) {          //if old rank is different  we will hit db with new value
    echo "updating id ".$line["id"]." from rank ".$line["rank"]." to rank ".$rank." <br>";
    if(!mysqli_query($link,"UPDATE p1 SET rank = '".mysqli_real_escape_string($link,$rank)."' WHERE id ='".mysqli_real_escape_string($link,$line["id"])."'")) {
      echo "Error updating Rank".mysqli_error($link);
  }else {  //if its the same we just leave the message for now
    echo "ignoring id ".$line["id"]." previous rank ".$line["rank"]." , new rank ".$rank." <br>";
  $rank++;    // next user so lets increment the counter
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download