Insert if not exist php

Question

I have the code

mysql_query("INSERT INTO authors (author) VALUES ('$rec_fic_author') WHERE NOT EXISTS (SELECT * FROM authors WHERE author='$rec_fic_author')") or die("cannot insert author");

Where authors is the table containing authorID (pk), and author $rec_fic_author is a _POST variable

I'm trying to make it so that when a person is adding an author, the code checks the database for anyone with the same name (under the author column). If not, then add the entry I have author as TEXT, and for some reason, I can't make it unique in phpMyAdmin. I looked into REPLACE and INSERT .. ON DUPLICATE KEY UPDATE, but as I understand they both need the value in question to be a unique/PK value. This is not the case here.

Help?

Could you share your table structure with information such as PK — TeaCupApp, Aug 11 '11 at 05:02
Why dont you just do a SELECT for the author and check the mysql_num_rows()? If its equal 0 do the insert. — mpratt, Aug 11 '11 at 05:11
author as TEXT seems a bit over-dimensioned, a CHAR/VARCHAR should do; and these can have a UNIQUE INDEX. — wonk0, Aug 11 '11 at 05:45

score 1 · Accepted Answer · answered Aug 11 '11 at 06:10

1

I'd suggest you to operate with PK or with UK (as wonk0 suggested), then you could use INSERT statement with INGNORE clause.

INSERT Syntax

answered Aug 11 '11 at 06:10

Devart

119,203
23
166
186

score 0 · Answer 2 · edited May 23 '17 at 11:47

0

This is a good query and NOT EXISTS is typically quite fast if you have good indexes. But i am wondering why this is not working!

I think this can be useful for your concern... LINK, Check it.

edited May 23 '17 at 11:47

Community

1
1

answered Aug 11 '11 at 05:13

Imran Khan

2,373
2
22
35

Unfortunately this is no valid syntax for an INSERT; cf http://dev.mysql.com/doc/refman/5.1/en/insert.html – wonk0 Aug 11 '11 at 05:48
@wonk0, i didn't mean that this is a valid, but i suggest a already posted link, and if it is possible that is good.... (please up-vote my answer). – Imran Khan Aug 11 '11 at 05:52

Insert if not exist php

2 Answers2