CODEWITHSUNDEEP
phpmysql
Tom Granot
Tom Granot

Reputation: 1850

PHP/MySQL Check If Value Exists, If So-Do Not Insert It

I asked that before, but couldn't figure it out.

I have this form:

<?php
if ( isset ($_REQUEST['fname']{0}, $_REQUEST['lname']{0}, $_REQUEST['mail']{0}, $_REQUEST['url']{0}) ){
   $query = "INSERT INTO table1 (url, fname, lname, mail) VALUES ('".$_REQUEST[url]."', '".$_REQUEST[fname]."', '".$_REQUEST[lname]."', '".$_REQUEST[mail]."')"; 
$result = mysql_query($query)
or die ("Query Failed: " . mysql_error());
}
else{
   echo "One Of The Values Not Entered Correctly. Please Press Back In Your Browser And Enter The Missing Values.";
}
?>

And I would like to know if it is possible for it to check if a url exists in the system before entering it again.

Upvotes: 1

Views: 4032

Answers (5)

Supan Shah
Supan Shah

Reputation: 21

Replace does exactly what you need.

http://dev.mysql.com/doc/refman/5.0/en/replace.html

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted

Upvotes: 2

Phil
Phil

Reputation: 164767

Given the description in the OP and subsequent comments (try insert, throw error if exists), I'd simply make sure the required "unique" columns had unique constraints or were part of the primary key.

Then, simply attempt the insert and catch / handle any unique constraint violation errors. This is quicker than checking for existing records.

Using PDO with the error mode set to throw exceptions means you can wrap this code nicely in a try catch block. From memory, unique constraint violations set the exception code to something you can test against.

Upvotes: 0

Joshua Martell
Joshua Martell

Reputation: 7212

If you do have a UNIQUE or PRIMARY KEY index on the url column, INSERT IGNORE will do what you want. New rows will go in and duplicates will be ignored. INSERT ... ON DUPLICATE KEY UPDATE will allow you to update if there's a duplicate, if that's what you want to do. Also, pay good attention to the SQL injection comments from the others here.

Upvotes: 0

Webist
Webist

Reputation: 37

Make sure the url column in db is PRIMARY or UNIQUE.

ALTER TABLE  `table1` ADD PRIMARY KEY(`url`);

Before you can use this insert function, you must add mysql_connect(), mysql_select_db()

function insert($post = array(), $tb, $announce=true, $ignore="",$updateonduplicate=false){

        foreach($post as $k => $v){
            $fields[$k] = $v;
            $values[] = mysql_real_escape_string($v);
        }

        $query = "INSERT {$ignore} INTO `{$tb}` (`".implode("`,`",array_keys($fields))."`)"." VALUES ('".implode("','",$values)."')";

        if($updateonduplicate !== false){

        $query .= " ON DUPLICATE KEY UPDATE ";
        $countfields = 0;
         foreach($fields as $field => $value){

            $query .= " `".$field."`='".$value."'";

            $countfields++;
            if(count($fields) !== $countfields)
            $query .= ",";
         }
        }


//mysql_connect(), mysql_select_db()
// assuming db is connected, database selected
            $result = mysql_query($query)

        if($announce !== true)
        return;


        if(!$result){       
            $announce = "Query Failed: " . mysql_error();

        }else{
            $announce = "insert_success";

        }

        if($updateonduplicate === true && $result === 0 && mysql_affected_rows() >=1){
            $announce = "update_success";
        }   

        return $announce;
    }

$post = array();
$post['url'] = $_REQUEST[url];
$post['fname'] = $_REQUEST[fname];
$post['lname'] = $_REQUEST[lname];
$post['mail'] = $_REQUEST[mail];

insert($post,"table1",true,"",true);

Upvotes: 0

mdarwi
mdarwi

Reputation: 933

Check out MySQL INSERT ... ON DUPLICATE KEY UPDATE, which you can use if you set the URL as unique in your database.

Also, you should make sure to sanitize your inputs before inserting them: http://php.net/manual/en/function.mysql-real-escape-string.php

Upvotes: 5

Related Questions