DO not INSERT if Duplicate Row

[hp1]

Hi All,Finally got around to dabbling with MYSQL and PHP (its my 2nd day). My beginners project is to try and create a clients and appointments database for my friends hairdressing business.I have two main tables: one that holds the client info and one that holds the appointment info. I want to be able to inset data in two ways. The first is via the user on the site from a form that captures all the key infromation. The 2nd will be from an admin page only accessible via a password.I can manage to insert all the data into the client table no problem but I can't figure out how to check to see if the client already exists in the table to stop duplicate rows with different primary keys (auto incremented) being added to the table.I suspect this is probably a simple problem but i'm struggling. Appreciate advice.

[Jack McKalling]

Hello,The problem in here lies in the fact that you don't really know what the unique field is per record.The auto incremented field acts as one for MySQL, but programatically, every new record (uniqueness in disregard) will have a new unique key. So programatically making difference between duplicate records, you should think of a way to identify them.To do this, you could simply use an INT column (eg. "UniqueField") instead of an AUTO_INCREMENT column.Then with inserting a new record, specify the value of this new field as follows:

SELECT COUNT(UniqueField) AS Num_Records FROM Clients

INSERT INTO Clients (UniqueField, column1, column2) VALUES(Num_Records, 'column1data', 'column2data')

(where "Num_Records" is the fetched data from the above SELECT)This way, every new record will have a unique ID that you specify, namely the amount of existing records, starting from zero. With this method of identifying the record, you can alter the ID on beforehand while it is not automatically incremented by MySQL. It enables you to try to insert REAL duplicate rows, but of cource MySQL will prevent it.Else, if MySQL increments the ID for you, there is no way of identifying duplicate rows.Hope this helps

[justsomeguy]

It is correct to use an autoincrement column, that's what it's there for. But don't have MySQL check for duplicate rows, do that yourself. Do a select statement before you do an insert to get matching rows, and if more then 0 rows are returned then it already exists in the database. If it does, then you can get the ID that was returned and use that for the next SQL statement, or else do an insert and then use mysql_insert_id to get the new ID.You could just make something else the primary key and just try to insert, but if it already exists then your page will show a database error, and you don't want users to see those types of things. So it's better to check yourself if it exists and take the appropriate action.

[hp1]

Thanks both. I've been distracted by another problem (see php forum!) but will return to this issue as soon as possible, heeding your comments and advice.Cheers.