使用PHP在三个MySQL表中更新/插入 - UPDATE/INSERT across three MySQL tables using PHP

我有 3 个表：

SMS_IN"原始"数字（数据通过短信发送，因此所有号码都是短信形式：我正在"爆炸"它们以将它们变成一个数字）：
```
sms_textsender_numbersent_dt
```
使用会员手机号码members：
```
会员编号用户名comp_id电话
```
mobile_numbers_test他们所有数字的表格：
```
会员编号comp_id数字contact_number积极
```

我正在尝试将SMS_IN（新数字）放入mobile_numbers_test（使用旧数字），并进行 2 次检查：

SMS_IN.sender_number = members.phone ; 和
如果SMS_IN.sender_number = mobile_numbers_test.contact_number AND SMS_IN.sms_text（以爆炸形式）= mobile_numbers_test.numbers，则跳过（或覆盖） - 否则INSERT 。

这是我到目前为止所拥有的：

include ('database_connection.php');
$result = mysql_query("SELECT phone FROM members")
or die(mysql_error());  
$no=0;
echo'<table>
        <tr>
        <td>Numbers</th>
        <td>From</th>
        <td>Date</th>
        </tr>';
while($row = mysql_fetch_array($result))
    {$no++;
    $phone= $row['phone'];  
    $comp_id =  $row['comp_id'];
    $result1 = mysql_query("SELECT * FROM SMS_IN where sender_number ='$phone'")
    or die(mysql_error()); 
    while($row = mysql_fetch_array($result1))
        {$no++;
        $sms= $row['sms_text'];
        $sms_no=$row['sender_number'];
        $sms_time=$row['sent_dt'];
        $sms_exp = explode("'r'n",$sms);
        foreach($sms_exp as $no )
        { //** not working from here **//
        $result2 = mysql_query("SELECT * FROM members,mobile_numbers_test,SMS_IN 
        where members.phone = SMS_IN.sender_number and 
        mobile_numbers_test.numbers != '$no'
        ")
        or die(mysql_error());
        while($row = mysql_fetch_array($result2))
        {
        $circle=$row['state'];
        $price='Call Us';
        $contact_person=$row['Username'];
        $contact_number=$row['phone'];
        $type='Fancy';
        $per_post='Pre-Paid';
        $provider=$sms_exp[0];
        $date=strtotime($sms_time);
        $ip=$sms_no;
        $old_no=$row['numbers'];;
        if($old_no!=$no){ 
            echo '<tr><td>'.$no.'</td>';
            echo '<td>'.$contact_person.'</td></tr>';
        } else { echo 'Old No';
        }
        }
        }
        }
        }       
    echo'</table>';

在这个例子中，我echo答案，以便我知道会发生什么......如何UPDATE或INSERT？

在 (contact_number, numbers) 上mobile_numbers_test创建一个UNIQUE索引（如果尚不存在）：
```
ALTER TABLE mobile_numbers_test ADD UNIQUE INDEX (contact_number, numbers);
```

加入members，并按照您的加入条件SMS_IN：

SELECT * FROM members JOIN SMS_IN ON SMS_IN.sender_number = members.phone

循环遍历结果集，使用INSERT IGNORE分解数字并插入mobile_numbers_test。把它们放在一起（使用PDO，因为它会让你的生活更轻松，你真的应该停止使用古老的mysql_*函数编写新代码：它们不再被维护，社区已经开始弃用过程）：

$dbh = new PDO("mysql:host=$hostname;dbname=$database", $user, $pass);
$select = $dbh->query("
  SELECT * FROM members JOIN SMS_IN ON SMS_IN.sender_number = members.phone
") or die($dbh->errorInfo());
$insert = $dbh->prepare("
  INSERT IGNORE INTO mobile_numbers_test
           ( Memberid,  comp_id,  numbers,  contact_number)
    VALUES (:Memberid, :comp_id, :numbers, :contact_number)
");
while($sms = $select->fetch()) {
  foreach(explode("'r'n",$sms['sms_text']) as $no) $insert->execute(array(
    'Memberid'       => $sms['Memberid'],
    'comp_id'        => $sms['comp_id'],
    'numbers'        => $no,
    'contact_number' => $sms['sender_number']
  ));
}