Adding a date to mysql value being inserted if value exists in column

Question

I have a MySQL table like that is structured like this:

|       uri       |   title     |
_________________  _____________
Latest-Update      Latest Update
                   Latest Update

where the uri column is an INDEX. I have occassional injections I perform into a mysql table, and the ERROR Duplicate Entry appears if i have 1 or more URI's with the same name. So i use PHP for the inejction, and want to append a date("m-d-y") to the end of the variable $uri when being inserted. My INSERT statement INSERT INTO table(uri,title) values('$uri', '$title'). I want to see if in the table that is being inserted to, before the INSERT happens, if i can tell if the $uri value already exists in the uri table, and if so, that variable will become $uri = $uri."-"date("m-d-y"); How can i tell? I have done some research and lead me to believe i should be using INSERT...ON DUPLICATE KEY UPDATE?

Using on duplicate key update will force it to update the existing entry rather than inserting a new one — Anigel, May 02 '12 at 15:29

score 0 · Answer 1 · answered May 02 '12 at 15:34

0

Most probably this could be due to your uri being a primary key. For this, you should probably create a surrogate key and that would allow duplication. If that is indeed what you need.

answered May 02 '12 at 15:34

Nutty Nur

101
3
14

score 0 · Answer 2 · answered May 02 '12 at 20:57

Not sure if you are interested in Triggers but you can depend on BEFORE INSERT trigger. Trigger requires to check if new uri being inserted has a dup entry and if yes, append dateStringOfYourFormat and insert.

Example:

mysql> drop table mytable;
Query OK, 0 rows affected (0.03 sec)

mysql> create table mytable( uri varchar(128) );
Query OK, 0 rows affected (0.05 sec)

mysql> drop trigger uri_modifier;
ERROR 1360 (HY000): Trigger does not exist
mysql> delimiter //
mysql> create trigger uri_modifier before insert on mytable
    -> for each row
    -> begin
    ->  if ( select count(uri) from mytable where uri=NEW.uri ) > 0 then
    ->   set @modified_uri_string=concat(NEW.uri, '-', date_add(sysdate(), interval floor(rand()*1e6) microsecond));
    ->   set NEW.uri=@modified_uri_string;
    ->  end if;
    -> end//
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> delimiter ;
mysql>
mysql> set @my_new_uri_string = 'my_uri_string_to_search';
Query OK, 0 rows affected (0.00 sec)

mysql> set @modified_uri_string = @my_new_uri_string;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from mytable;
Empty set (0.00 sec)

mysql> insert into mytable values(@my_new_uri_string);
Query OK, 1 row affected (0.03 sec)

mysql> select @my_new_uri_string, @modified_uri_string;
+-------------------------+-------------------------+
| @my_new_uri_string      | @modified_uri_string    |
+-------------------------+-------------------------+
| my_uri_string_to_search | my_uri_string_to_search |
+-------------------------+-------------------------+
1 row in set (0.00 sec)

mysql> select * from mytable;
+-------------------------+
| uri                     |
+-------------------------+
| my_uri_string_to_search |
+-------------------------+
1 row in set (0.00 sec)

mysql> insert into mytable values(@my_new_uri_string);
Query OK, 1 row affected (0.03 sec)

mysql> select @my_new_uri_string, @modified_uri_string;
+-------------------------+----------------------------------------------------+
| @my_new_uri_string      | @modified_uri_string                               |
+-------------------------+----------------------------------------------------+
| my_uri_string_to_search | my_uri_string_to_search-2012-05-03 02:21:39.983405 |
+-------------------------+----------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from mytable;
+----------------------------------------------------+
| uri                                                |
+----------------------------------------------------+
| my_uri_string_to_search                            |
| my_uri_string_to_search-2012-05-03 02:21:39.983405 |
+----------------------------------------------------+
2 rows in set (0.02 sec)

mysql> insert into mytable values(@my_new_uri_string);
Query OK, 1 row affected (0.01 sec)

mysql> select @my_new_uri_string, @modified_uri_string;
+-------------------------+----------------------------------------------------+
| @my_new_uri_string      | @modified_uri_string                               |
+-------------------------+----------------------------------------------------+
| my_uri_string_to_search | my_uri_string_to_search-2012-05-03 02:21:39.412580 |
+-------------------------+----------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from mytable;
+----------------------------------------------------+
| uri                                                |
+----------------------------------------------------+
| my_uri_string_to_search                            |
| my_uri_string_to_search-2012-05-03 02:21:39.983405 |
| my_uri_string_to_search-2012-05-03 02:21:39.412580 |
+----------------------------------------------------+
3 rows in set (0.00 sec)

mysql>
mysql>

Make sure that if uri inserted is different than what passed in, you have a handle to know what the modified value is.

Adding a date to mysql value being inserted if value exists in column

2 Answers2