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

Question

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.

Yes, with SQL query. Do you happen to know how to run a query? — Your Common Sense
– Your Common Sense, Commented Dec 12, 2010 at 21:03
I do indeed: $query = "QUERY"; $result = mysql_query($query) or die ("Query Failed: " . mysql_error()); The question is: how do i check if it exists? — Tom Granot
– Tom Granot, Commented Dec 12, 2010 at 21:05
It's not a knowledge but rather a copy/paste, but anyway. Well, run a SELECT query and see if it will return anything. — Your Common Sense
– Your Common Sense, Commented Dec 12, 2010 at 21:56
Col. Shrapnel-you are the kind of people that make people to not want to get into this world. What is wrong with copying somebody Else's code, understanding what it does, and using it for your own purposes? — Tom Granot
– Tom Granot, Commented Dec 25, 2010 at 11:33

mdarwi · Accepted Answer · 2010-12-12 21:04:57Z

5

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

answered Dec 12, 2010 at 21:04

mdarwi

9335 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Supan Shah · Accepted Answer · 2012-09-21 13:58:22Z

2

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

answered Sep 21, 2012 at 13:58

Supan Shah

211 bronze badge

Comments

Webist · Accepted Answer · 2010-12-12 22:00:54Z

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);

Joshua Martell · Accepted Answer · 2010-12-12 22:53:51Z

0

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.

answered Dec 12, 2010 at 22:53

Joshua Martell

7,2122 gold badges33 silver badges37 bronze badges

Comments

Phil · Accepted Answer · 2010-12-12 23:23:26Z

0

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.

answered Dec 12, 2010 at 23:23

Phil

166k25 gold badges265 silver badges269 bronze badges

Collectives™ on Stack Overflow

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

5 Answers 5

Comments

Comments

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

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

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

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related