Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
446 views
in Technique[技术] by (71.8m points)

php - MySql: if value exists UPDATE else INSERT

I have some code that looks like this. There is also an autoincrement field in the table that I must retain (it is used in other tables). I would like to simplify and optimize this code.

$query ="SELECT * FROM models WHERE col1 = 'foo'";
$testResult = mysql_query($query) or die('Error, query failed');    

if(mysql_fetch_array($testResult) == NULL){
    //insert...
    $query ="INSERT INTO models (col1, col2, col3)
    VALUES ('foo', 'bar', 'alph')";
    $result = mysql_query($query) or die('Error, query failed');
}else{
    //update...
    $query = "UPDATE models
        SET col1='foo', col2='bar', col3='alph'
        WHERE col1='foo' AND col2='bar'";
        $result = mysql_query($query) or die('Error, query failed');        
}

Edit: The primary key id is the field that is auto incremented. I never want to alter this. However , when another field(s) is/are duplicated, this is when I want to update that record.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

How about REPLACE INTO:

REPLACE INTO models
( col1, col2, col3 )
VALUES
( 'foo', 'bar', 'alpha' )

Assuming col1 is your primary key, if a row with the value 'foo' already exists, it will update the other two columns. Otherwise it will insert a new row.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...