CODEWITHSUNDEEP
mysqlif-statementrecordexists
Iain Simpson
Iain Simpson

Reputation: 469

Mysql error on update record

I am running the following just as a test to see if I have got this part of the script correct, I already have a record which is as follows in the mysql database : If in my form I put the above info into each of the form fields and hit submit, what should happen is it select the record that equals the posted leadname and emailformname and update the record that has the same leadname and emailformname with a new date from the [datesent] field in the form.

So I run the following :

<?php
$query = mysql_query("IF EXISTS(SELECT * FROM hqfjt_email_history WHERE     leadname='$_POST[leadname]' AND emailformname='$_POST[emailformname]') 
THEN UPDATE hqfjt_email_history (datesent) VALUES ('$_POST[datesent]') WHERE     emailformname='$_POST[emailformname]' AND leadname='$_POST[leadname]'
") or die(mysql_error());
?>

but instead of updating that field I get the error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF EXISTS(SELECT * FROM hqfjt_email_history WHERE leadname='Brian Cox' AND email' at line 1

By the way I cant use the no duplicate key index method for updating the record for various reasons, hence why I need to identify the record by the two fields leadname and emailformname.

I cant see a reason why the above query would fail other than my bad coding (probably the problem) as there is only one record in the DB with those two fields = true.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Thanks for everyones help, is this now correct if I want to insert on empty record ?, im not sure how to sanitise my part I added at the end though ?.

 <?php
 $sql = "IF EXISTS (
        SELECT * FROM hqfjt_email_history 
        WHERE leadname= '" . mysql_real_escape_string($_POST['leadname']) . "' 
        AND emailformname = '" . mysql_real_escape_string($_POST['emailformname']) . "'
    ) 
    THEN
        UPDATE hqfjt_email_history 
        SET datesent = '" . mysql_real_escape_string($_POST['datesent']) . "'
        WHERE emailformname = '" . mysql_real_escape_string($_POST['emailformname']) . "' 
        AND leadname = '" . mysql_real_escape_string($_POST['leadname']) . "'
    ELSE
    INSERT INTO hqfjt_email_history
    (id, leadname, emailformname, datesent) VALUES     ('$_POST[id]','$_POST[leadname]','$_POST[emailformname]','$_POST[datesent]')";
$query = mysql_query($sql) or die(mysql_error());
?>

Upvotes: 0

Views: 197

Answers (3)

Valky
Valky

Reputation: 1866

The better way to do it this by using ON DUPLICATE

If the row already exists, it updates it.

But be careful, INSERT syntax must be (for example) :

INSERT INTO table (val1,val2) 
VALUE ('123','abc')
ON DUPLICATE KEY UPDATE
val3='new'

For some statistics updates it's very useful (with val3=val3+1)

Upvotes: 0

JF Paris
JF Paris

Reputation: 134

To do this, you can use the INSERT INTO ... ON DUPLICATE KEY UPDATE ...

With this structure, MySQL will try to insert the record, if an error on duplicate key is raised, MySQL will try to update the record with that key.

Be sure that your (unique / primary) key is properly set.

INSERT INTO
  hqfjt_email_history 
SET
  leadname='$_POST[leadname]',
  emailformname='$_POST[emailformname]',
  datesent='$_POST[datesent]'
ON DUPLICATE KEY UPDATE
  leadname='$_POST[leadname]',
  emailformname='$_POST[emailformname]',
  datesent='$_POST[datesent]'

Upvotes: 1

Jules
Jules

Reputation: 7223

Your UPDATE statement is wrong. It should be written as:

UPDATE <table>
SET <columnname> = <value>
WHERE <condition>

So...

$sql= "UPDATE hqfjt_email_history 
SET datesent = '" . mysql_real_escape_string($_POST['datesent']) . "'
WHERE emailformname = '" . mysql_real_escape_string($_POST['emailformname']) . "' 
AND leadname = '" . mysql_real_escape_string($_POST['leadname'] . "'"

Note that I added a mysql_real_escape_string() function around your variables to avoid injection and apostrophe issues.

So eventually your PHP should be:

$sql = "IF EXISTS (
            SELECT * FROM hqfjt_email_history 
            WHERE leadname= '" . mysql_real_escape_string($_POST['leadname']) . "' 
            AND emailformname = '" . mysql_real_escape_string($_POST['emailformname']) . "'
        ) 
        THEN 
            UPDATE hqfjt_email_history 
            SET datesent = '" . mysql_real_escape_string($_POST['datesent']) . "'
            WHERE emailformname = '" . mysql_real_escape_string($_POST['emailformname']) . "' 
            AND leadname = '" . mysql_real_escape_string($_POST['leadname']) . "'";
$query = mysql_query($sql) or die(mysql_error());

Nevertheless I don't understand why you add the IF EXISTS ( ... ) to your query. Because basically you just check if the record exists and update it if it does. But if you do an UPDATE on a non-existing record, it will simply not update anything... So it can't do any damage writing

$sql = "UPDATE hqfjt_email_history 
            SET datesent = '" . mysql_real_escape_string($_POST['datesent']) . "'
            WHERE emailformname = '" . mysql_real_escape_string($_POST['emailformname']) . "' 
            AND leadname = '" . mysql_real_escape_string($_POST['leadname']) . "'";

Upvotes: 2

Related Questions