CODEWITHSUNDEEP
phpmysqldatabaseinsertconditional-statements
Zoolander
Zoolander

Reputation: 2363

How to ensure there are no existing post titles when inserting a row into a MySQL database with PHP?

I'm using the following function to insert data into a MySQL database.

Can someone update it so an error message will be returned if there's already a title in the title column with the name "My Test Post" (case insensitive)? (I don't want any duplicate posts)

public function insert() {
    // Does the Article object already have an ID?
    if ( !is_null( $this->id ) ) trigger_error ( "Article::insert(): Attempt to insert an Article object that already has its ID property set (to $this->id).", E_USER_ERROR );

    // Insert the Article
    $conn = new PDO( DB_DSN, DB_USERNAME, DB_PASSWORD );
    $sql = "INSERT INTO articles ( publicationDate, title, summary, content ) VALUES ( FROM_UNIXTIME(:publicationDate), :title, :summary, :content )";
    $st = $conn->prepare ( $sql );
    $st->bindValue( ":publicationDate", $this->publicationDate, PDO::PARAM_INT );
    $st->bindValue( ":title", $this->title, PDO::PARAM_STR );
    $st->bindValue( ":summary", $this->summary, PDO::PARAM_STR );
    $st->bindValue( ":content", $this->content, PDO::PARAM_STR );
    $st->execute();
    $this->id = $conn->lastInsertId();
    $conn = null;
}

Upvotes: 1

Views: 154

Answers (3)

Emil Condrea
Emil Condrea

Reputation: 9973

$sql = "SELECT title from articles where title=:title";
    $st = $conn->prepare ( $sql );
    $st->bindValue( ":title", $this->title, PDO::PARAM_STR );
    $st->execute();
    if($st->rowCount()>0) print "error";
else{

$conn = new PDO( DB_DSN, DB_USERNAME, DB_PASSWORD );
    $sql = "INSERT INTO articles ( publicationDate, title, summary, content ) VALUES ( FROM_UNIXTIME(:publicationDate), :title, :summary, :content )";
    $st = $conn->prepare ( $sql );
    $st->bindValue( ":publicationDate", $this->publicationDate, PDO::PARAM_INT );
    $st->bindValue( ":title", $this->title, PDO::PARAM_STR );
    $st->bindValue( ":summary", $this->summary, PDO::PARAM_STR );
    $st->bindValue( ":content", $this->content, PDO::PARAM_STR );
    $st->execute();
    $this->id = $conn->lastInsertId();
    $conn = null;

}

for case insensitive :

$sql = "SELECT title from articles where LOWER(title)=:title";
$st->bindValue( ":title", strtolower($this->title), PDO::PARAM_STR );

Upvotes: 3

Eddie
Eddie

Reputation: 10148

You have a few options. One is to set the UNique constraint on the field in question. That way MySQL will throw constraint violations if you try to save a duplicate.

A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. For all engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL. http://dev.mysql.com/doc/refman/5.1/en/create-table.html

The other alternative is to simply run a select using the field in question and check for an empty result set.

Upvotes: 1

genesis
genesis

Reputation: 50976

I'm not familiar with PDO, but here's normal mysql query to check it

if (mysql_num_rows(mysql_query("SELECT title FROM articles WHERE title = '".mysql_real_escape_string($this->title)."'")){
    die("exists already");
}

Upvotes: 0

Related Questions