MySql newbie.

I'm in the process of creating a product catalog. Each product has a unique code which has to be entered by the user at the time of entering new products into the table.

How can I prevent duplicate codes from being inserted into the table??

Thanks,

Ranesh.

do a select query with the new number, if the query returns any rows (mysql_num_rows($qh) > 0) display error, if not, insert the record into the table

What is $qh ?

(Please excuse my ignorance....I'm a newbie.)

thnx.

Ranesh.

that's your query handler variable

You definitely don't want to be doing it the way Ryan's just suggested unless you can possibly help it (sorry Ryan!). There are some major issues with the method that's been outlined which I'll detail in a moment. First, here's how you'd be better off doing it, if possible.

All you need to do to ensure that the DB doesn't get multiple rows with the same ID (which should be unique) is to specify that the column containing that unique ID can only contain unique values. This is done by specifying a key on the column -- it can either be the primary key (basically, just a unique indexed key), or simply a unique key. To add a unique key to your table use the following:
<BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">code:</font><HR><pre>
ALTER TABLE mytable ADD UNIQUE (my_unique_column)
[/code]

If you're going to be doing a lot of lookups via this column, you can also make it all a lot faster by adding an index:
<BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">code:</font><HR><pre>
ALTER TABLE mytable ADD INDEX (my_unique_column)
[/code]

So, now the reasons for not using the other method suggested:

1) You're relying on application logic to maintain the integrity of your data -- what if at some point you or someone else writes some other application logic that works with the same table, but forgets to add that integrity checking (or simply implements it incorrectly)

2) For every insert, you're needing at least two roundtrips to the server (one to check if the row exists and then one to insert it). This will double the time it takes for an insert to occur, and as a round trip can be quite slow (especially if at some point the DB is moved elsewhere on a network or something) it can have a serious impact on application performance

3) Probably most importantly, it doesn't work. You introduce an unpleasant race condition because you're working in a multi-user environment. Imagine the following sequence of events where User A and User B are both trying to insert '123' into the table:
User A checks if '123' is free. Query says yes.
User B checks if '123' is free. Query says yes.
User A inserts '123'
User B inserts '123'!
Because you can't control the interleaving of query/insert statements across multiple users (without using transactions, which are sloooow), it's possible for multiple users to check for the availability of a value before any of them insert, leading to the DB erroneously reporting to multiple users that they can use a single given value.

As you can imagine, situations like this are absolute buggers to debug, so you want to try to avoid them before they happen. Using a unique key approach to this problem, only one round trip is needed on average -- if the unique key constraint is violated (i.e. two people try to insert the same value), the DB will chuck an error back at you, which you can then trap and alert the user about to get a new key (or do what you will).

Chris,
No problem, I always welcome a better way to do things, and after your explanation I have seen the error of my ways. This will also prompt me to learn about PHP's error handling features.