我有2个服务器,一个是本地服务器,上面有个表myself_matches的数据是实时更新的;
另外一个是目标服务器,上面有个表myself_matches2,也要保持跟本地服务器数据实时更新,所以写了一个程序准备让它让windows任务计划不断定时运行。现在我想把myself_matches上的数据都插入到myself_matches2,所以在程序里面分别做了连接,并且判断假如在myself_matches2已经存在某条记录,那么就不要再重复插入此条记录。
问题是:无论我myself_matches2表中存在不存在从myself_matches读来的记录,它都会插入,运行多少次程序它就插入多少次,造成大量的重复,请教各位我的程序错在哪里?我已经调试了很久了,都没有解决。谢谢各位!///////////////////////////////////////////////////////////////////////////////////////////////////////<?php
$link=mysql_connect("localhost","admin","admin") or die('连接数据库失败'.mysql_error());//连接本地服务器
mysql_query("set names gbk");
if(mysql_select_db("phpcms",$link))
echo "数据库连接成功"."<br>";
else
echo ('数据库选择失败:'.mysql_error()); $link2=mysql_connect("192.16.1.100","admin","admin");//连接目标服务器
mysql_query("set names gbk");
if(mysql_select_db("phpcms",$link2))
echo "数据库2连接成功"."<br>";
else
echo ('数据库2选择失败:'.mysql_error());$sql1 = "SELECT * FROM myself_matches";
$query1 = mysql_query($sql1,$link) or die(mysql_error());
while($row1 = mysql_fetch_assoc($query1))
{
$day = $row1['day'];
$time = $row1['time'];
$typename = $row1['typename'];
$hometeam = $row1['hometeam'];
$points = $row1['points'];
$guestteam = $row1['guestteam'];
$halfpoints = $row1['halfpoints'];
$res = $row1['res'];
$sql2 = "select * from myself_matches2 where `day`='$day' and `time` like '%{$time}%' and hometeam like '%{$hometeam}%' and halfpoints like '%{halfpoints}%'" ;
//$sql2 = "select * from myself_matches2 where (`day`='$day') and (`time` = '$time') and (hometeam = '$hometeam') and (halfpoints = 'halfpoints')" ;
$query2 = mysql_query($sql2,$link2) or die(mysql_error());
if (($num = mysql_num_rows($query2)) == 0)
{
$sql2 = "INSERT INTO myself_matches2 (
`day`, `time`, typename, hometeam, points, guestteam, halfpoints, res
) VALUES (
'$day' ,'$time', '$typename' ,'$hometeam' ,'$points' ,'$guestteam' ,'$halfpoints' ,'$res'
)";
$query2=mysql_query( $sql2, $link2 ) or die(mysql_error());
if($query2==true)
{
echo "成功!!<br>";
}
else
{
echo "失败!!<br>";
}
}
else
{
echo "错误数据已存在!<br>";
}
}
?>
另外一个是目标服务器,上面有个表myself_matches2,也要保持跟本地服务器数据实时更新,所以写了一个程序准备让它让windows任务计划不断定时运行。现在我想把myself_matches上的数据都插入到myself_matches2,所以在程序里面分别做了连接,并且判断假如在myself_matches2已经存在某条记录,那么就不要再重复插入此条记录。
问题是:无论我myself_matches2表中存在不存在从myself_matches读来的记录,它都会插入,运行多少次程序它就插入多少次,造成大量的重复,请教各位我的程序错在哪里?我已经调试了很久了,都没有解决。谢谢各位!///////////////////////////////////////////////////////////////////////////////////////////////////////<?php
$link=mysql_connect("localhost","admin","admin") or die('连接数据库失败'.mysql_error());//连接本地服务器
mysql_query("set names gbk");
if(mysql_select_db("phpcms",$link))
echo "数据库连接成功"."<br>";
else
echo ('数据库选择失败:'.mysql_error()); $link2=mysql_connect("192.16.1.100","admin","admin");//连接目标服务器
mysql_query("set names gbk");
if(mysql_select_db("phpcms",$link2))
echo "数据库2连接成功"."<br>";
else
echo ('数据库2选择失败:'.mysql_error());$sql1 = "SELECT * FROM myself_matches";
$query1 = mysql_query($sql1,$link) or die(mysql_error());
while($row1 = mysql_fetch_assoc($query1))
{
$day = $row1['day'];
$time = $row1['time'];
$typename = $row1['typename'];
$hometeam = $row1['hometeam'];
$points = $row1['points'];
$guestteam = $row1['guestteam'];
$halfpoints = $row1['halfpoints'];
$res = $row1['res'];
$sql2 = "select * from myself_matches2 where `day`='$day' and `time` like '%{$time}%' and hometeam like '%{$hometeam}%' and halfpoints like '%{halfpoints}%'" ;
//$sql2 = "select * from myself_matches2 where (`day`='$day') and (`time` = '$time') and (hometeam = '$hometeam') and (halfpoints = 'halfpoints')" ;
$query2 = mysql_query($sql2,$link2) or die(mysql_error());
if (($num = mysql_num_rows($query2)) == 0)
{
$sql2 = "INSERT INTO myself_matches2 (
`day`, `time`, typename, hometeam, points, guestteam, halfpoints, res
) VALUES (
'$day' ,'$time', '$typename' ,'$hometeam' ,'$points' ,'$guestteam' ,'$halfpoints' ,'$res'
)";
$query2=mysql_query( $sql2, $link2 ) or die(mysql_error());
if($query2==true)
{
echo "成功!!<br>";
}
else
{
echo "失败!!<br>";
}
}
else
{
echo "错误数据已存在!<br>";
}
}
?>
咦?你怎么在这?
我已经判断了呀,看看代码。
我已经用了好几种判断方式了 什么mysql_fetch_array,mysql_query !== false,还有现在的if (($num = mysql_num_rows($query2)) == 0)
都不起作用。
$query1 = mysql_query($sql1,$link) or die(mysql_error());
$query2 = mysql_query($sql2,$link2) or die(mysql_error());这两句是否能连接到不同的服务器?如果不能,也不应该能从本机读数据到目标服务器了呀!真是奇怪。
换成
$sql2 = "select count(1) from myself_matches2 where `day`='$day' and `time` like '%{$time}%' and hometeam like '%{$hometeam}%' and halfpoints like '%{halfpoints}%'" ; 试试
换成
$sql2 = "select count(1) from myself_matches2 where `day`='$day' and `time` like '%{$time}%' and hometeam like '%{$hometeam}%' and halfpoints like '%{halfpoints}%'" ; 试试
$query2 = mysql_query($sql2,$link2) or die(mysql_error());
$row = mysql_fetch_assoc($query2) ;
if ($row['datanumber'] == 0){}
else {}
where `day`='".$day."'
都改成这样试试
你可以google下'mysql数据库同步'。
如果楼主非要写程序的话可以这个样子。
在两个数据库表中增加一个primary key 自动增加的。
然后你插入到另外一个表中在insert中需要加上这个field, 如果在目标表中已经有了个记录的话,根据primary key 的唯一性 这条记录是插入不进去的