1

Hi i'm new to php and mysql.

I'm wondering how can i use PHP to check if a table in my mysql database contains a ROW which have the specific ID and if not add the data to the table .

For example,

Table structure:(Table name : record) ID , DATA

i have ID=123 and DATA=hello stored in a variable in the php code , how can i use php sql query to find out whether the data exist by checking using its ID in the table , if not , INSERT the ID and DATA into the table.

I hope you understand.

p/s i have connected the php script to the database

ETAN
  • 2,852
  • 5
  • 22
  • 34

5 Answers5

3

Make the ID UNIQUE:

CREATE TABLE my_table( ID INT UNSIGNED UNIQUE...

then use INSERT IGNORE:

INSERT IGNORE INTO my_table( ID, DATA ) VALUES( some_id, some_data )
nobody
  • 10,126
  • 4
  • 22
  • 41
  • It's a good suggestion, although you can only add a `UNIQUE` constraint if all the selected fields are already unique, otherwise it gives you an error. In the table I was working on, there were already some duplicate entries (for some unknown reason) and I was attempting to prevent further duplicates. – Simon East Aug 24 '11 at 03:41
  • For anyone wondering, `INSERT IGNORE` changes any MySQL errors into warnings so that you can continue running a long script without it being interrupted. You can then view the warnings with the command `SHOW WARNINGS`. – Simon East Aug 24 '11 at 03:43
2
IF NOT EXISTS(SELECT 1 FROM structure WHERE ID = <yourid>)
   INSERT INTO structure (ID, DATA) VALUES(<yourid>, <yourdata>)
Erik A. Brandstadmoen
  • 9,864
  • 2
  • 34
  • 52
1

Just replace INSERT with REPLACE.

http://dev.mysql.com/doc/refman/5.5/en/replace.html

Sean Thayne
  • 813
  • 1
  • 7
  • 9
  • Good suggestion Sean - this will avoid duplicate values, however it will also *overwrite* existing data, which may not be the intent of the original poster. – Simon East Aug 24 '11 at 03:45
1

Another option:

INSERT INTO record(id, `data`) SELECT 123, 'hello'
FROM new_table WHERE NOT EXISTS (SELECT id from record where id = 123 );
Bruno Flávio
  • 750
  • 10
  • 27
0

If your ID is unique key, then you can directly try to insert the row. If that ID is already in the table, the database would not let you insert it and will return error. Else you have to first run a SELECT statement to see if this ID exists in your table and if not insert it.

Also this thread will help you a lot I think How to 'insert if not exists' in MySQL?

Community
  • 1
  • 1
Yasen Zhelev
  • 3,872
  • 3
  • 26
  • 54