检查插入或更新表 [英] Check to Insert or Update table

问题描述

请参见下面的代码，它检查表中是否存在数据，如果不存在，则将其插入，否则更新表.

See the code below, it check if the data exist in the table, if not exist then insert it or else update the table.

如您所见，它看起来有些杂乱-是否仍需改进代码逻辑或更小?我有几张桌子需要做同样的事情.

As you can see it look a bit messy - is there anyway to improve the code logic or something smaller? I have a few tables that need doing same thing.

foreach ($sheet as $data) {
    // Get Phone ID
    $dataPhoneID = mysql_escape_string($data['handset']['phone_id']);

    if (isset($stocks[$dataPhoneID])) {
        $stockPhone = $stocks[$dataPhoneID ];
        $phoneName = mysql_escape_string($stockPhone['description']);
        $stock = mysql_escape_string($stockPhone['stock']);

        $SQL = "SELECT * FROM phone_affiliate WHERE affiliate_id = 1 AND affiliate_phone_id = '$dataPhoneID'";
        $q = mysql_query($SQL);
        if (mysql_num_rows($q) == 0) {
            $SQLInsert = "INSERT INTO phone (name) VALUE('$phoneName')";
            if (mysql_query($SQLInsert)) {
                $phone_id = mysql_insert_id();
                $SQLInsert = "INSERT INTO phone_affiliate (phone_id, affiliate_id, affiliate_phone_id, stock) ";
                $SQLInsert .= "VALUE('$phone_id', '1', '$dataPhoneID', '$stock')";
                mysql_query($SQLInsert) or die(mysql_error());
            }
        } else {
            $row = mysql_fetch_assoc($q);
            $phone_id = $row['phone_id'];
            $SQLUpdate = "UPDATE phone_affiliate set stock = '$stock' WHERE affiliate_id = 1 AND phone_id = $phone_id";
             mysql_query($SQLUpdate) or die(mysql_error());
        }

      // Similar code block above for other tables.
    }
}

注意:我知道PDO，但是我没有时间在现有系统上替换它.

Note: I am aware about PDO but I don't have time to replace it on existing system.

推荐答案

使用mysql的插入...重复键更新.例如:

Use mysql's REPLACE INTO or INSERT... ON DUPLICATE KEY UPDATE. For example:

foreach ($sheet as $data) {
    // Get Phone ID
    $dataPhoneID = mysql_escape_string($data['handset']['phone_id']);

    if (isset($stocks[$dataPhoneID])) {
        $stockPhone = $stocks[$dataPhoneID ];
        $phoneName = mysql_escape_string($stockPhone['description']);
        $stock = mysql_escape_string($stockPhone['stock']);

        $SQLInsert = "INSERT INTO phone_affiliate (affiliate_id, affiliate_phone_id, stock) ";
        $SQLInsert .= "VALUES ('1', '$dataPhoneID', '$stock') ";
        $SQLInsert .= "ON DUPLICATE KEY UPDATE stock = '$stock'";
        mysql_query($SQLInsert);
        if (mysql_insert_id()) {
            $SQLInsert = "INSERT INTO phone (name) VALUE('$phoneName')";
            mysql_query($SQLInsert);
            $phone_id = mysql_insert_id();
            $SQLUpdate = "UPDATE phone_affiliate set phone_id = $phone_id WHERE affiliate_id = 1 AND affiliate_phone_id = $dataPhoneID_id";
        }
    }
}

这篇关于检查插入或更新表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！