I've got a table and I have a text field with a URL again, I don't want any rows with duplicate URL's but I'd rather not do a check before inserting, is there no way to make a text field unique or could you suggest another field type to use?
Add a comment
|
4 Answers
The solution is basically add a extra field on database like field_hash, make it VARCHAR and Index give unique.
CREATE TABLE 'info' (
'text' text NOT NULL,
'text_hash' varchar(40) NOT NULL,
PRIMARY KEY ('id'),
UNIQUE KEY 'text_hash' ('text_hash')
);
$text = 'Full Text';
$text_hash = sha1($text);
mysql_query("INSERT INTO info (text, text_hash) VALUES ('$text', '$text_hash')");
Comments
If your text field in the database is set to unique or a primary key or however you have it set up, than an insert statement will fail upon trying to input a duplicate. That way all you should have to do is properly handle the failed insert and there you have it.
As pointed out elsewhere, you can't have an actual TEXT field be unique, but if you are using a varchar field it is doable. This may not be important at all (may just be a miscommunication), because if you are storing URL's in a TEXT field, you have other problems to worry about as well.
4 Comments
zuk1
i can't seem to add a max length to the field which is required to add the unique key, any ideas?
TheTXI
Change your field from text to varchar and give it an appropriate length
zuk1
Ok but I was just worried I'd try and insert URL's over 250 in length, but that is highly unlikely. Wht are the other problems you mentioned which come from storing URL's in text fields?
TheTXI
zuk1: I do not believe that 250 is the maximum size of a varchar field. You should be able to set that varchar field to be a larger number (say 500, 750, whatever) that way you know you'll have enough size for the URL. Another problem with TEXT fields is that it is not a very good field to store things if you are going to do string searches (such as finding all URL's that end in ".com")
You could try using another field that contains a hash of the URL field.
Comments
The correct way, is to Check whether the url exists in the table, if yes then insert, else, donot insert.
As a best practice, Exception Handling should be used where it actually requires, it should not be used as a replacement of a normal functions.
