PHP Insert - Unique

The_B_Man

Hi,
Its been a while since I've used PHP so I'm a bit rusty. I'm trying to put together a little side-app for meself as part of another little app I'm making.

I have a db table with the unique ID, plus a single varchar field for storing the data. The data will be a word of some sort, but I don't want duplicates, as I'll be using this data for something else. Basically its a list of words.

So, if I have my INSERT statement that goes:

$sql="INSERT INTO words (word) VALUES('$_POST[word]')";

This will take input from a previous form. Later on I'll "addslashes" so don't worry about that for now.

So, how can I ensure every time I add a word that its unique? Do I have to do a "SELECT *" and iterate through a loop every time? Or is there any easier way? Will it work if I set the db field for the word in the database to unique, or can you only have 1 unique field in each mysql table?

Thanks for the help.

selfdiy

You can set more then one column in the table to unique.
I would imagine that unique is case sensitive, so you'll have to allow for this, maybe insert them in lowercase only. There is a lower case function in mysql that you can use during the insert.

I'm not familiar with php so dont know if trying to violate a unique constraint will cause php to pick up an error, maybe it can be ignored...

Malice

I'm curious what you're trying to achieve here. Are you saying your table has 1 column called word? If you're not storing any other information do you really need a database at all? Couldn't you read and write to a plain text file with each word on a new line or something?

Also it may be stating the obvious but you should never do "SELECT *" in a query. Only bring back what you need, not everything.

Lastly I'm not too familiar with PHP or MySQL's syntax for query parameters either but for security you'll want to do more than just call addslashes() on the form input.

The_B_Man

Well OK, assuming I do write to a text file, which is what I was originally planning on doing actually, this still leaves the problem of duplicates.

In mySQL i think theres some way you can control it, eg:

$sql="INSERT INTO words (word) VALUES('$_POST[word]') ON DUPLICATE KEY UPDATE word=word";

which doesnt work. I'm still trying to figure it out.

But with writing to a text file, other than consume the whole text file each time, looking for the word, there's nothing that can be done to stop duplicates and only have unique words in the file.

Either way, the words collected will end up in a text file, but I thought I'd refresh my php/mysql knowledge for a bit of fun, but if you can think of a way to ensure every word is unique in a text file, then fire away. This app is only for myself(student) for a bit of practice.

Cheers.

amen

well if you are inserting into a database you could add a primary key on the column you are inserting into.

This will enforce uniqueness. When you try to save the same word a second time you will get an error which you will have to handle.

You will have to check and see if MySql treats the same word with different cases as the same word ie Word, word woRd etc

out of interest why do you only want a word insert once?

How many unique words do you envision?

The_B_Man

Ye good idea with the primary key. I'd actually made the "ID" field the primary key, but I suppose I dont really need an ID so can prob just cut the table down to a single field.

Regarding case, I know C/C++ has "tolower(string)" and I think PHP has similar, or an ignore case funciton, so that shouldnt be a problem (if it exists).

The reason I want unique words, is becuase I'm building a dictionary of swear words. I'll be then putting this list in a text file. Then I'll be using Java to count the number of swear words in a particular block of text. This is part of me teaching myself Java as well.

Actually while we're on it, I'm pretty OK with I/O streams in C++, but not in Java, so feel free to throw advice out there on how to manage them and strings etc, though I'm sure I can figure it out myself once I get this unique word thing sorted.