table comparison" .
"table comparison
" .
"source";
$good_user = mysql_connect(
"samba2.ipm.virginia.edu", "selectuser", "")
or unset_user_password();
mysql_select_db( "test");
$sortstr = "race1.place";
$selstring =
"select race1.year, race1.place from race1 order by year,place";
//*********************************
//*** get list of rows to compare
//*********************************
$query_handle = mysql_query( $selstring);
if (mysql_errno() > 0)
{
echo "\n" . mysql_errno(). ": " . mysql_error() . "
";
}
$num_rows = mysql_num_rows ($query_handle);
//*********************************
//*** get column names
//*********************************
$col_handle = mysql_query( "show columns from race1");
if (mysql_errno() > 0)
{
echo "\n" . mysql_errno(). ": " . mysql_error() . "
";
}
echo "
| " .
"This program compares the race table with 11000 rows versus a " .
"test race table with 33 rows. Three fields were intentionally changed." .
"The program finds the column names in the table, then finds the unique " .
"keys for the shorter table and compares the matching rows column by " .
"column. It also detects missing rows. " .
"I have provided some debugging info so you can see what is " .
"happening. |
";
$num_cols = mysql_num_rows ($col_handle);
echo "
columns found = " . $num_cols. "
\n";
$fieldname = array();
for( $j = 0; $j < $num_cols; $j++)
{
$colname = mysql_fetch_assoc ($col_handle);
$fieldname[] = $colname['Field'];
echo " " . $colname['Field'] . "\n";
}
if (mysql_errno() > 0)
{
echo "\n" . mysql_errno(). ": " . mysql_error() . "
";
}
//*********************************
//*** compare each pair
//*********************************
for ( $i = 0; $i < $num_rows; $i++)
{
echo "
\n";
$row = mysql_fetch_array ($query_handle);
$place = $row['place'];
$year = $row['year'];
$bigcmd = "select * from race where place =" .
'"' . $place . '" and year = "' . $year . '"';
$littlecmd = "select * from race1 where place =" .
'"' . $place . '" and year = "' . $year . '"';
$bigQH = mysql_query( $bigcmd);
$rows_found = mysql_num_rows ($bigQH );
if (mysql_errno() > 0)
{
echo "\n" . mysql_errno(). ": " . mysql_error() . "
";
}
$littleQH = mysql_query( $littlecmd);
if (mysql_errno() > 0)
{
echo "\n" . mysql_errno(). ": " . mysql_error() . "
";
}
$rowbig = mysql_fetch_assoc ( $bigQH);
$rowlittle = mysql_fetch_assoc ( $littleQH);
echo "year = " . $year . " place = " . $place . "
\n";
//*******************************************
//*** do the column by column comparison
//*******************************************
if( $rows_found == 0)
{
echo "no matching record found.
";
}
else
{
for ($j = 0; $j < $num_cols; $j++)
{
$fname = $fieldname[$j];
if( $rowbig[$fname] <> $rowlittle[$fname])
{
echo $fieldname[$j] . " == " .
$rowbig[$fname] . " ---- " . $rowlittle[$fname] . " ---
\n";
}
}
}
} // for all runners values
echo "debug ==============
";
print_r( $fieldname);
echo "\n\n========\n\n ";
print_r( $colname);
echo "
";
?>