PHP - Compare two MySQL tables_问答_开发者_运维开发者技术经验分享

开发者 https://www.devze.com 2023-04-08 10:40 出处：网络

Is there a PHP code that let\'s you compare 2 MySQL tables with each other and lets you add missing entries into one?

相关专题：php

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;">
    &nbsp;</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;">
    &nbsp;</p>
<p style="text-align: center;">
    &nbsp;</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

PHP - Compare two MySQL tables

精彩评论

关注公众号

热门标签

图文推荐

PHP - Compare two MySQL tables

更多 问答 相关资讯：

精彩评论

关注公众号

热门标签

图文推荐

更多问答相关资讯：