doulu1914
2011-09-27 02:32 阅读 52
已采纳

如何在不使用INSERT IGNORE的情况下检查mysql中是否存在行?

I currently use this query to update listings in my database from my php app:

$query = "INSERT INTO listings (title, description) VALUES ('$title','$description')";

This listings table has a 'postid' column as it's primary key that auto increments.

I don't want to do an INSERT IGNORE and have it check postid. Instead, I'd like to keep the table structure the same and check to see if $title exists.. and not insert if it does.

Will php/mysql allow me to somehow run a:

If ($title does not exist) {
$query = "INSERT INTO listings (title, description) VALUES ('$title','$description')";
}  

If so, how would I write that?

Thanks for any suggestions.

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

2条回答 默认 最新

  • 已采纳
    drydaenth257216154 drydaenth257216154 2011-09-27 03:44

    You can do the dollowing:

    Query your database to see if there is already any list with the given title and if the count query returns 0, execute your insert statement:

    $result = mysql_query('SELECT count(*) as total from listings where title="$title"');
    
    $result = mysql_fetch_array($result);    
    
    if($result['total'] == 0){
        $query = mysql_query("INSERT INTO listings (title, description) VALUES ('$title','$description')");
    }
    

    But I strongly suggest you to do not manipulate your database this way. Better to use an ORM or a database class instead of putting your SQL statements all over the place.

    Good luck.

    点赞 评论 复制链接分享
  • douqiao1983 douqiao1983 2011-09-27 02:51

    Try this:

    INSERT INTO listings (title, description) VALUES ('$title','$description')
    FROM dual WHERE not exists (SELECT title FROM listings WHERE title = '$title');
    

    Or you can do like this:

    $result = mysql_query("UPDATE listings SET description = '$description' WHERE title ='$title';");        
    if (mysql_affected_rows() == 0) {
        $result = mysql_query("INSERT INTO listings (title, description) VALUES ('$title','$description');");
    }
    
    点赞 评论 复制链接分享

相关推荐