php - 如何比较两个表的数据

Question

我有两个结构相同的数据库(mysql)。我想要:

• 比较两个表中的数据。表一 - 家庭和第二个工作，
• 发送包含结果的电子邮件，
• 更新表工作中的数据。

我的查询:

select id, code, quantity from wpx_products

我在表 home 和 work (两个数据库)中运行此查询。这是输出:

"3";"home 005-07";"2"
"63";"home 033-12";"2"
"15";"home 005-19";"2"

以及工作:

"1";"work 005-07";"2"
"2";"work 033-12";"5"
"3";"work 005-19";"2"

我想做什么？我所说的“比较”是什么意思？我想在“代码”列中查找不包含标签工作或家庭的记录。例如我想找到033-12并检查数量。如果从家到工作的差异复制值(value)。

在第一秒我想在mysql中使用触发器。但这对我来说不是解决方案，因为我无法自己运行它，也无法发送包含结果的电子邮件。实现此功能的最佳方法是什么？感谢您的帮助。

亲切的问候

--------------------编辑------------------------ ---

我检查了下面的代码(感谢#AntG)。我有一个问题。当我打印时

foreach ($result_target AS $target) {
    $code_target = substr($target['code'], 4);
    if ($code_source === $code_target) {
        if ($source['quantity'] !== $target['quantity']) {
            print $source['quantity'] .' -> ' . $target['quantity']."<br /><br />";
            $match[] = array('code' => $source['code'], 'quantity' => $source['quantity'], 'targetid' => $target['id'], 'sourceid' => $source['id']);      
        }
        $found = true;
        break;
    }
}

我有这个结果:http://suszek.info/projekt1/就像你看到的那样，有 50 个值。当我打印 $match 时，有更多重复的值，我不知道为什么？

$msg = '';
foreach ($match AS $entry) {
    $msg .= 'Change identified: Home_ID=' . $entry['sourceid'] . ' code: ' . $entry['code'] . ' quantity:' . $entry['quantity'] . PHP_EOL . '<br />';
    print $msg;
    /*  Perform DB updates using $entry['targetid'] and $entry['quantity'] */
}

我有这个结果:http://suszek.info/projekt1/index_1.php以及所有代码:

 $match = array(); $new = array();

 foreach ($result_source AS $source) {

     $found = false;
     $code_source = substr($source['code'], 4);
     foreach ($result_target AS $target) {
         $code_target = substr($target['code'], 4);
         if ($code_source === $code_target) {
             if ($source['quantity'] !== $target['quantity']) {
                 print $source['quantity'] .' -> ' . $target['quantity']."<br /><br />";
                 $match[] = array('code' => $source['code'], 'quantity' => $source['quantity'], 'targetid' => $target['id'], 'sourceid' => $source['id']);      
             }
             $found = true;
             break;
         }
     }

     if (!$found) {
         $new[] = array('code' => $source['code'], 'quantity' => $source['quantity'], 'sourceid' => $source['id']);
     } } $msg = ''; foreach ($match AS $entry) {
     $msg .= 'Change identified: Home_ID=' . $entry['sourceid'] . ' code: ' . $entry['code'] . ' quantity:' . $entry['quantity'] . PHP_EOL
 . '<br />';
     print $msg;
     /*  Perform DB updates using $entry['targetid'] and $entry['quantity'] */ }

 foreach ($new AS $entry) {
     $msg .= 'New Entry: Home_ID=' . $entry['sourceid'] . ' code: ' . $entry['code'] . ' quantity:' . $entry['quantity'] . PHP_EOL . '<br
 />';
     #print $msg;
     /*  Perform DB inserts using $entry['code'] and $entry['quantity'] if this is desired behaviour */ }

 /*  Send email with $msg  */

Answer 1

如果您在 $results 数组中捕获两个查询结果，那么我认为 substr() 就是您在这里需要的 PHP 函数:

$match=array();
$new=array();
foreach($results_home AS $home)
{
    $found=false;
    $code=substr($home['code'],5);
    foreach($results_work AS $work)
    {
        if($code===substr($work,5))
        {
             if($home['quantity']!==$work['quantity'])
             {
                 $match[]=array('code'=>$home['code'],'quantity'=>$home['quantity'],'workid'=>$work['id'],'homeid'=>$home['id']);
             }
             $found=true;
             break;
        }
    }
    if(!$found)
    {
        $new[]=array('code'=>$home['code'],'quantity'=>$home['quantity'],'homeid'=>$home['id']);

    }
}
$msg='';
foreach($match AS $entry)
{
    $msg.='Change identified: Home_ID='.$entry['homeid'].' code: '.$entry['code'].' quantity:'.$entry['quantity'].PHP_EOL;
    /*  Perform DB updates using $entry['workid'] and $entry['quantity'] */
}

foreach($new AS $entry)
{
    $msg.='New Entry: Home_ID='.$entry['homeid'].' code: '.$entry['code'].' quantity:'.$entry['quantity'].PHP_EOL;
    /*  Perform DB inserts using $entry['code'] and $entry['quantity'] if this is desired behaviour */
}

/*  Send email with $msg  */