I'm trying to match rows in two tables with similar table structure, and update the tables with primary key of the matching row of the other table. The program works perfectly when both the table have small number of rows ( I've tried with 10 rows each)... but seems to enter an infinite loop when both tables have approx 10,000 rows each. Further, what could be the reason that the progress bar does not updates when the tables are big.
<div id="progress" style="width:500px;border:1px solid #ccc;padding:10px;margin:10px"></div>
<h1>Successfully Matched Entries</h1>
<div id="prog_succcess" style="width:500px;border:1px solid #ccc;padding:10px; margin:10px"></div>
<h1>Entries Not found</h1>
<div id="prog_fail" style="width:500px;border:1px solid #ccc;padding:10px;margin:10px"></div>
<!-- Progress information -->
<div id="information" style="width"></div>
<?php
// db connection statements here ..
$sqlA="select pk, truckno, station, weight, matchid from vendor";
$sqlB="select pk_id, truckno, station, weight, matchid from truck";
$resultA=mysqli_query($con,$sqlA);
$resultB=mysqli_query($con,$sqlB);
$totalRows = mysqli_num_rows(mysqli_query($con, "select * from vendor"));
while ($i<= $totalRows-1){
$sqlA="select pk, truckno, station, weight, matchid from vendor";
$sqlB="select pk_id, truckno, station, weight, matchid from truck";
$resultA=mysqli_query($con,$sqlA);
$resultB=mysqli_query($con,$sqlB);
$objA= mysqli_fetch_all($resultA,MYSQLI_NUM);
$objB= mysqli_fetch_all($resultB,MYSQLI_NUM);
if ($objA[$i][1]== $objB[$j][1] &&
$objA[$i][2]== $objB[$j][2] &&
$objA[$i][3]== $objB[$j][3] &&
($objB[$j][4]==0) ) {
$str = $objB[$j][0];
$str2 = $objA[$i][0];
$updateA = "UPDATE `vendor` SET `does_match`=1, `matchid`='$str' WHERE pk = " . $str2;
mysqli_query($con,$updateA) or die(mysqli_error($con));
$updateB = "UPDATE `truck` SET `matchid`='$str2' WHERE `truck`.`pk_id` = " . $str;
mysqli_query($con,$updateB) or die(mysqli_error($con));
$suc++;
$i++; // set row-being-matched to next row
$j=0; // reset truck's row number to 0
}
else
{
//echo
//"Line ".$i. " not matched with line ".
//$i."--".$j." | ";
$j++; // set truck's row to next row number
if ($j>=mysqli_num_rows($resultB)) { // check if end reached
$fail++; // set query failed
$j=0; // reset rows
$i++; // set table's next row
//set line i as unmatched entry.
}
}
$percent = intval($i/$totalRows * 100)."%";
$percents= intval($suc/$totalRows * 100)."%";
$percentf= intval($fail/$totalRows * 100)."%";
// print javascript progress bar ( just an html <div> with a width specified :)
echo '<script language="javascript">
document.getElementById("progress").innerHTML="<div style=\"width:'.$percent.';background-color:#ddd;\"> </div>";
document.getElementById("prog_succcess").innerHTML="<div style=\"width:'.$percents.';background-color:green;\"> </div>";
document.getElementById("prog_fail").innerHTML="<div style=\"width:'.$percentf.';background-color:red;\"> </div>";
document.getElementById("information").innerHTML="'.$i.' row(s) processed.
'.$suc.' Entries Successfully Matched.
" ;
</script>';
}
// Free result set
mysqli_free_result($resultA);
mysqli_free_result($resultB);
mysqli_close($con);