Welcome to ShenZhenJia Knowledge Sharing Community for programmer and developer-Open, Learning and Share
menu search
person
Welcome To Ask or Share your Answers For Others

Categories

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

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

1 Answer

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
thumb_up_alt 0 like thumb_down_alt 0 dislike
Welcome to ShenZhenJia Knowledge Sharing Community for programmer and developer-Open, Learning and Share

548k questions

547k answers

4 comments

86.3k users

...