[RESOLVED] sql insert where value value doesnt already exist

[bicky]

Hello All

I have created a statement that works fine, taking the value from a form and inserting into a MS SQL table.

I now want to expand the code, to only insert when the value doesnt already exist...

My Current Code is as follows:

$tsql = "INSERT INTO email_supp
        (email,date,source)
        VALUES
        (?,?,?)";

$today = date("d/m/y");
$source= 'Email Form';
$email = array($_POST['emailopt'], $today,$source);

/* Prepare and execute the query. */
$stmt = sqlsrv_query( $conn, $tsql, $email);
if( $stmt )
{
     echo "Row successfully inserted.\n";
}
else
{
     echo "Row insertion failed.\n";
     die( print_r( sqlsrv_errors(), true));
}

/* Free statement and connection resources. */
/*sqlsrv_free_stmt( $stmt);*/

sqlsrv_close( $conn);
?>

My main problem here is I dont really know how the value is taken from the form, I know its within this part (array($_POST['emailopt']) but If i wanted to just specify the value to a variable my PHP seems to then fall over.

ANY help would be appreciated..

Thanks in advance

Bicky

[bradgrafelman]

bicky wrote:

I now want to expand the code, to only insert when the value doesnt already exist...

So why don't you just add a UNIQUE index on that column?

[bicky]

Thanks for the reply...

That is an option but dont want to change the structure and index on the SQL table...

I did see somewhere that I could use sqlsrv_num_rows and then only do an insert when the result is 0

When I tried this though I couldn't get it to work...

any help with how to add that to the code above would be great!

[laserlight]

bicky wrote:

That is an option but dont want to change the structure and index on the SQL table...

Why not?

bicky wrote:

I did see somewhere that I could use sqlsrv_num_rows and then only do an insert when the result is 0

However, that is likely to be vulnerable to a race condition.

[bradgrafelman]

I don't know anything about MS SQL, but generally speaking you could do something like... 1) lock the table for writing, 2) SELECT rows that match, 3) INSERT if no rows found, 4) unlock table for writing.

That's a lot more complicated than just: 1) add UNIQUE index on column.

[bicky]

OK fair point then, this does seem to be easiest option...Thanks for your suggestions and recommendations

But out of interest, if I did want to check the table prior to inserting, is this possible? If so how would I do this? and what would be the best way?

BTW, what 's a 'Race' condition?

[laserlight]

bicky wrote:

But out of interest, if I did want to check the table prior to inserting, is this possible? If so how would I do this? and what would be the best way?

bradgrafelman mentioned locking the table, but I do not think that this is possible in most high lever programming interfaces. You cannot really wrap everything in a single transaction because you will be checking the row count in PHP. Maybe it is possible if you used a stored procedure, but I am afraid that I lack sufficient knowledge of stored procedures to describe how this might be done.

bicky wrote:

BTW, what 's a 'Race' condition?

In this case, it would be a case where the script checks for one user that there are no matching rows, but before it can do the insert, it checks for another concurrent user that there are no matching rows. Since both checks pass, the script proceeds to do the inserts, thus resulting in duplicate rows.

[bradgrafelman]

laserlight wrote:

bradgrafelman mentioned locking the table, but I do not think that this is possible in most high lever programming interfaces.

Eh? MySQL can; 12.3.5. LOCK TABLES and UNLOCK TABLES Syntax

[laserlight]

bradgrafelman wrote:

Eh? MySQL can

Interestingly, a quick check shows that it can be done with PostgreSQL too, though in both cases it is done at the level of the SQL dialect.

[bicky]

OK, In my instance having a user insert duplicate values at the same time won't be a problem.

In my case, I would only need to insert if the value is not already present. The point of this is someone with no SQL skills can update a SQL table with the value using a simple HTML form.

I think I could do this in the SQL statement by using something like the following:

INSERT INTO email_supp
(email,date,source)
VALUES
([emailvariable],[datevariable],[sourcevariable])" where [emailvariable] not in (select email from email_supp);

Now the emailvariable is taken from the form the other variables are created in the PHP code itself. The problem I seem to be having is constructing the SQL statement using the emailvariable from the form.

I hope you understand what I am trying to explain. I just need an exaple of the SQL statement above but using the variables declared in the PHP code.

Hope you can help...

[bradgrafelman]

INSERT statements don't have WHERE clauses, so no, you can't do that. You'd have to do as I said above; first do a SELECT query to see if any rows already match the given data and, if not, INSERT the data.

[Crowly]

Did a little google search and found a simular tread on another forum, heres two solutions to that problem:

I want to insert a record only if the record I'm about to insert does not match the Username and Application field.

This is what I got so far...

INSERT INTO dbo.Applications (Date, Username, Application, Version) VALUES('3/10/2009', 'Tom', 'Calculator', '2.0') WHERE NOT EXISTS (SELECT FROM dbo.Applications WHERE Username = 'Tom' AND Application = 'Calculator')

IF NOT EXISTS (SELECT * FROM dbo.Applications WHERE Username = 'Tom' AND Application = 'Calculator') BEGIN
 INSERT INTO dbo.Applications 
 (Date, Username, Application, Version) 
 VALUES
 ('3/10/2009', 'Tom', 'Calculator', '2.0') 
END

INSERT INTO dbo.Applications (Date, Username, Application, Version) 
select '3/10/2009', 'Tom', 'Calculator', '2.0' 
WHERE NOT EXISTS (SELECT * 
FROM dbo.Applications 
WHERE Username = 'Tom' 
AND Application = 'Calculator')

[bradgrafelman]

And now compare all of that jumbled mess to this one single query:

ALTER TABLE Applications ADD UNIQUE User_Application (Username, Application)

and tell me which one seems easier and more efficient. :p