insert insert到mysql else update insert？

Question

有人能帮帮忙吗?我正试着让这个脚本从我的mysql表中选择合适的值，如果它们存在，就更新它们，如果它们不存在，就插入它们。

有人能告诉我哪里出错了吗?谢谢。

    <?php
        require_once("session.php"); 
        require_once("functions.php");
        require('_config/connection.php');

        session_start();

        include '_config/connection.php'; 

        $height_ft = $_POST['height_ft'];
        $height_in = $_POST['height_in'];
        $weight_st = $_POST['weight_st'];
        $weight_lb = $_POST['weight_lb'];    

        $result = mysql_query("SELECT height_ft FROM ptb_stats WHERE id=".$_SESSION['user_id']."");

        $result2 = mysql_query("SELECT height_in FROM ptb_stats WHERE id=".$_SESSION['user_id']."");

        $result3 = mysql_query("SELECT weight_st FROM ptb_stats WHERE id=".$_SESSION['user_id']."");

    $result4 = mysql_query("SELECT weight_lb FROM ptb_stats WHERE id=".$_SESSION['user_id']."");

    if( !$result ) { 
       echo "The username you entered does not exist"; 
    } else if( $height_ft != mysql_result( $result, 0 ) ) { 
       echo "";    

         $sql = mysql_query("UPDATE ptb_stats SET height_ft='$height_ft' WHERE id=".$_SESSION['user_id'].""); 

         $sql = mysql_query("UPDATE ptb_stats SET height_in='$height_in' WHERE id=".$_SESSION['user_id'].""); 

         $sql = mysql_query("UPDATE ptb_stats SET weight_st='$weight_st' WHERE id=".$_SESSION['user_id'].""); 

         $sql = mysql_query("UPDATE ptb_stats SET weight_lb='$weight_lb' WHERE id=".$_SESSION['user_id']."");    

         mysql_query("INSERT INTO ptb_stats (user_id, height_ft) VALUES (".$_SESSION['user_id'].", ".$height_ft.")"); 

         mysql_query("INSERT INTO ptb_stats (user_id, height_in) VALUES (".$_SESSION['user_id'].", ".$height_in.")"); 

         mysql_query("INSERT INTO ptb_stats (user_id, weight_st) VALUES (".$_SESSION['user_id'].", ".$weight_st.")");

         mysql_query("INSERT INTO ptb_stats (user_id, weight_lb) VALUES (".$_SESSION['user_id'].", ".$weight_lb.")");
    }

    if( $sql ) { 
        $_SESSION['edit_done']="<div class=\"infobox-edit-done\"><strong>Thank You -</strong> Your Details were changed.</div>"; 
        header("Location: {$_SERVER['HTTP_REFERER']}");
    } else { 
        $_SESSION['edit_done2']="<div class=\"infobox-edit-done\"><strong>Oooops! -</strong> That didn't work. Try again.</div>"; 
        header("Location: {$_SERVER['HTTP_REFERER']}");
    }  
?>

Answer 1

在MySQL中有一个使用ON DUPLICATE KEY UPDATE进行更新或插入的选项，它可以更简洁地解决您的问题。

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

对于您来说，查询将如下所示：

$sql=mysql_query("INSERT INTO ptb_stats (user_id, height_ft) 
VALUES (".mysql_real_escape_string($_SESSION['user_id']).", "
         .mysql_real_escape_string($height_ft).") 
ON DUPLICATE KEY UPDATE user_id = ".mysql_real_escape_string($_SESSION['user_id']).");