PHP - Compare two MySQL tables
Is there a PHP code that let's you compare 2 MySQL tables with each other and lets you add missing entries into one?
I have two tables. hs_hr_employee and rights. I want to add data from 开发者_运维问答certain columns from the hs_hr_employee table so that they would be the same in the rights tables.
hs_hr_employee has multiple rows, whereas the rights table has 5 rows. The rights table gets the info from 4 columns from the hs_hr_employee table, emp_number, employee_id, emp_firstname, emp_lastname
Below is the code:
<?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>
Any idea how to do it?
Thanks, Brian
I'd go with an INSERT INTO SELECT here.
Assume you recognize an employee by the Social Security Number (ssn), and you use this value to update, for instance, name and birthyear:
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)
");
You can also add a WHERE in between FROM and ON DUPLICATE. Like so:
...
FROM hs_hr_rights
WHERE birthyear IS NULL
ON DUPLICATE KEY UPDATE name = VALUES(name), birthyear = VALUES(birthyear)
...
Although, I don't see the need of copying values since in most cases you can fetch them through JOINs.
select * from table a where a.common not in (select b.common from table b where a.common = b.common)
And run this as well
select * from table b where b.common not in (select a.common from table a where a.common = b.common)
Not possible in php but in mysql its ans is as above
加载中,请稍侯......
精彩评论