note 80870 added to ref.mysql
| From: | dhirendrak at yahoo dot com | Date: | Tue, 05 Feb 2008 07:03:50 +0000 |
| Subject: | note 80870 added to ref.mysql | ||
| Groups: | php.notes | ||
| Request: | Send a blank email to php-notes+get-135781@lists.php.net to get a copy of this message | ||
<?php
# Created by dhirendra can be reached at dhirendrak at yahoo dot com
# This script is created to check the data difference between two tables
# when the structure of both tables are same.
# Limitation :
# 1) Structure of both tables should be same.
# 2) Name of both table should be different but if same than obviously
# second table should be if different database.
# 3) If use two database than both database permission should be same
# as i am using aliases to get the information.
#
# USES::
# 1) This may be useful when you did some changes in your existing
# script and you expect the certain output. So with the help of this
# function you may compare the impact due to your changes in script.
#
#
$host=""; # host name or ip address
$user=""; # database user name
$pass=""; # database password
$database=""; # dateabase name with which you want to connect
# get connection with mysql
$dblink = @mysql_connect($host,$user,$pass);
# select and open database
mysql_select_db($database,$dblink);
$db1="< your db1 >"; // first database
// second database if database are same for both tables than use the same as db1
$db2="< your db2 >";
$table1="< your table1 >"; // first table
// second table if database is same for both tables than table name
# must be different but fields name are same and order of the fields are same.
$table2="< your table2 >";
// function starts here
function table_data_difference($first,$second)
{
global $dblink;
$sql1 = "SHOW FIELDS FROM $first";
$result = mysql_query($sql1,$dblink) or die("Having error in execution 1
==".mysql_error());
while($row = mysql_fetch_object($result))
{
$from_fields[]=$row->Field;
}
$sql="select * from $first";
$res=mysql_query($sql,$dblink) or die("Having error in execution 2==".mysql_error());
$j=1;
while($row=mysql_fetch_array($res))
{
$num=count($from_fields);
$sql_next="select $second.* from $second where";
for($i=0;$i < $num;$i++)
{
$sql_next=$sql_next."
".$second.".".$from_fields[$i]."='".$row[$from_fields[$i]]."'
and ";
}
$sql_next=substr($sql_next,0,strlen($sql_next)-5);
$res_next=mysql_query($sql_next,$dblink) or die("Having error in execution
3==".mysql_error());
$num1=mysql_num_rows($res_next);
if($num1==0)
{
for($i=0;$i < count($from_fields);$i++)
{
$val=$val."<br>".$from_fields[$i]."=".$row[$from_fields[$i]];
}
// Display the record which are not matched.
echo "<br>\n".$j.".".$val;
echo "<br>-----------------------------------------------------";
$j++;
}
}
}
$first=$db1.'.'.$table1;
$second=$db2.'.'.$table2;
table_data_difference($first,$second);
?>
----
Server IP: 69.147.83.197
Probable Submitter: 61.246.2.206
----
Manual Page -- http://www.php.net/manual/en/ref.mysql.php
Edit -- https://master.php.net/note/edit/80870
Del: integrated -- https://master.php.net/note/delete/80870/integrated
Del: useless -- https://master.php.net/note/delete/80870/useless
Del: bad code -- https://master.php.net/note/delete/80870/bad+code
Del: spam -- https://master.php.net/note/delete/80870/spam
Del: non-english -- https://master.php.net/note/delete/80870/non-english
Del: in docs -- https://master.php.net/note/delete/80870/in+docs
Del: other reasons-- https://master.php.net/note/delete/80870
Reject -- https://master.php.net/note/reject/80870
Search -- https://master.php.net/manage/user-notes.php