PHP -比较两个MySQL表
PHP -比较两个MySQL表
提问于 2011-09-27 14:55:42
有没有一段PHP代码可以让你比较两个MySQL表，并将缺失的条目添加到一个表中？
我有两张桌子。hs_hr_employee和权限。我想添加来自hs_hr_employee表中某些列的数据，以便它们在权限表中是相同的。
hs_hr_employee有多行，而权限表有5行。权限表从hs_hr_employee表的4列中获取信息: emp_number、employee_id、emp_firstname、emp_lastname
代码如下：
<?php
$connection = mysql_connect('localhost','admin','root');
if( isset($_POST['submit']) )
{
if( isset( $_POST['cb_change'] ) && is_array( $_POST['cb_change'] ))
{
foreach( $_POST['cb_change'] as $emp_number => $permission)
{
$sql = "UPDATE `rights` SET Permission='".mysql_real_escape_string($permission)."' WHERE emp_number='".mysql_real_escape_string($emp_number)."'";
echo __LINE__.": sql: {$sql}\n";
mysql_query( $sql );
}
}
}
?>
<p style="text-align: center;">
<span style="font-size:36px;"><strong><span style="font-family: trebuchet ms,helvetica,sans-serif;"><span style="color: rgb(0, 128, 128);">File Database - Administration Panel</span></span></strong></span></p>
<p style="text-align: center;">
</p>
<head>
<style type="text/css">
table, td, th
{
border:1px solid #666;
font-style:Calibri;
}
th
{
background-color:#666;
color:white;
font-style:Calibri;
}
</style>
</head>
<form method="post" action="admin.php">
<?php
if (!$connection)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db('users', $connection);
//mysql_query('INSERT into rights(Emp_num, ID, Name, Surname) SELECT emp_number, employee_id, emp_firstname, emp_lastname FROM hs_hr_employee');
$result = mysql_query("SELECT emp_number, employee_id, emp_firstname, emp_lastname, Permissions FROM rights");
mysql_query("INSERT INTO rights (emp_number, employee_id, emp_firstname, emp_lastname)
SELECT emp_number, employee_id, emp_firstname, emp_lastname
FROM hs_hr_employee
ON DUPLICATE KEY UPDATE employee_id = VALUES(employee_id), emp_number = VALUES(emp_number)
");
echo "<center>";
echo "<table >
<tr>
<th>Employee Number</th>
<th>ID</th>
<th>Name</th>
<th>Surname</th>
<th>Permissions</th>
<th>Change</th>
</tr>";
while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['emp_number'] . "</td>";
echo "<td>" . $row['employee_id'] . "</td>";
echo "<td>" . $row['emp_firstname'] . "</td>";
echo "<td>" . $row['emp_lastname'] . "</td>";
echo "<td>" . $row['Permissions'] . "</td>";
echo "<td> <select name='cb_change[]'><option value='all'>All</option> <option value='remote'>Remote Gaming</option> <option value='landbased'>Landbased Gaming</option> <option value='general'>General Gaming</option> </select> </td>";
echo "</tr>" ;
}
#echo "<td>" . $row['Change'] . "</td>";
echo "</table>";
echo "</center>";
#$_POST['cb_permissions'];
mysql_close($connection);
?>
<p style="text-align: center;">
</p>
<p style="text-align: center;">
</p>
<p style="text-align: right;">
<input name="Save_Btn" type="button" value="Save" />
</p>
</form>你知道怎么做吗？
谢谢，布赖恩
回答 2
Stack Overflow用户
回答已采纳
发布于 2011-09-27 14:58:54
我会在这里使用INSERT INTO SELECT。
假设您通过社会保险号(ssn)识别员工，并使用此值来更新，例如，姓名和生日：
mysql_query("
INSERT INTO hs_hr_employee (ssn, name, birthyear)
SELECT ssn, name, birthyear
FROM hs_hr_rights
ON DUPLICATE KEY UPDATE name = VALUES(name), birthyear = VALUES(birthyear)
");您还可以在FROM和ON DUPLICATE之间添加WHERE。如下所示：
...
FROM hs_hr_rights
WHERE birthyear IS NULL
ON DUPLICATE KEY UPDATE name = VALUES(name), birthyear = VALUES(birthyear)
...尽管如此，我认为没有必要复制值，因为在大多数情况下，您可以通过JOIN获取它们。
Stack Overflow用户
发布于 2011-09-27 15:01:29
select * from table a where a.common not in (select b.common from table b where a.common = b.common)并运行此命令
select * from table b where b.common not in (select a.common from table a where a.common = b.common)在php中是不可能的，但在mysql中它的ans是如上所述的。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/7565288
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号