PHP/Postgresql and EXISTS function

[rhyspaterson]

Hey guys,

 

I'm trying to put together an INSERT command wherein a person can submit a record into the database through a HTML form. However i want to make sure the data is not already in there (stopping duplicates). The table has four columns;

 

id, extension, description and filetype_group

 

I want to match my variable $fileExtension (which is the $_POST variable from the form) against everything in the 'extension' column.

 

So far i have come up with this:

 

$result = pg_exec ($dbconn, "INSERT into filetype (id, extension, description, filetype_group) VALUES ($fileID,'$fileExtension','$fileDescription','$fileGroup') WHERE not exists (SELECT * FROM filetype WHERE filetype.extension = $fileExtension);");

 

 

The code works fine up until the WHERE not exists section, where i get syntax errors, but unfortunately i have no idea what's wrong. I was hoping you guys could shed some light. Primary key for 'filetype' table is the 'id' column.

 

Cheers,

/Rhys

[btherl]

Hi,

 

You can't do it in one statement.  Instead, do a select to see if it exists, followed by an insert.  If you want to be safe against concurrent inserts, then do it like this:

 

BEGIN

SELECT ...

if doesn't exist, INSERT

COMMIT

 

PS: This is the mysql forum.  Postgresql forum is over here: http://www.phpfreaks.com/forums/index.php/board,37.0.html

[radalin]

Well juast add a unique key to the coloumn that you want it to be a unique value. Then you don't need to check it. PgSQL will not insert anything because of violation of keys and it will return false.

 

Also see the postgresql select statement syntax for further information.