check mysql insert

i have this insert


require('db.php');

$ud_ID = $_SESSION['submissionID'];
$circuitId= mysql_real_escape_string($_POST['circuitId']);
$Race1=mysql_real_escape_string($_POST['Race']);  
$first=mysql_real_escape_string($_POST['Driver1']);
$second=mysql_real_escape_string($_POST['Driver2']); 



//var_dump($_SESSION);
    $query="INSERT INTO perrace(perraceid, SubmissionId, Race, Driver1, Driver2, time_updated, updated_dt) VALUES ('', '$ud_ID','$Race1','$first','$second','CURDATE()','')";
   

mysql_query($query)or die(mysql_error());
if(mysql_affected_rows()>=1){
    echo "<p>$TeamName Your options have been updated please login again to see <p>";
   	

    echo "<p><b><a href='../log.php'>Login</a></b></p>"; 
}else{
    echo "<p>Sorry $TeamName Sorry nothing was updated<p>";
}
?>

Open in new window


what i would love is for it to check that race and submissionID is not already in database
LVL 31
James MurrellProduct SpecialistAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

skullnobrainsCommented:
- you can run a select query first if you want to check

- or if you have the proper primary key or unique index ( race,submissionID ) or ( submissionID,race )
--- you can run a replace
--- or an insert ... on duplicate key update depending on your needs
--- or the same regular index but check the error number : duplicates produce a specific error code that you'll never get otherwise

---

i'm unsure what you want to do exactly but given the above code, i'd assume removing the "or die ..." and adding an on duplicate key update clause to your insert query would work as expected ( insert if the row is new, update otherwise )

if you only want to update existing rows, use an actual update query rather than an insert and keep the rest of the code unchanged.
1
James MurrellProduct SpecialistAuthor Commented:
Thanks for comment,

i what people to enter race once, so SubmissionId, Race should only be allowed once

ie

3, 34
3,34 should not be allowed and message explaining that

but 3, 34
3,17
3,67  

all ok and insert should then happen
0
Ray PaseurCommented:
You need to stop using MySQL ASAP.  It's been removed from PHP.  Here's the right way to get rid of the die() problem.  Now, on to the question...

You can mark any column or index UNIQUE and MySQL will throw error #1062 when an attempt is made to insert a duplicate.  Here's an excerpt from another question where we had a class that guaranteed a unique and random key.
    // FUNCTION TO ENSURE THE RANDOM STRING IS UNIQUE
    public function make_random_key()
    {
        $key = NULL;
        $this->cnt++;

        // GENERATE A UNIQUE AND RANDOM TOKEN
        while ($key == NULL)
        {
            $key = $this->random_string();
            $sql = "INSERT INTO $this->mytable ( $this->mycolumn ) VALUES ( '$key' )";
            $res = $this->mysqli->query($sql);

            // IF THERE IS A QUERY ERROR
            if (!$res)
            {
                // THE EXPECTED QUERY ERROR WOULD BE A DUPLICATE VALUE, NULLIFY THE KEY AND START OVER
                if ($this->mysqli->errno == 1062)
                {
                    trigger_error("1062 Duplicate Key Event: $key at " . number_format($this->cnt), E_USER_NOTICE);
                    $this->errs[$this->cnt] = $key;
                    $key = NULL;
                }
                // OTHER UNEXPECTED QUERY ERROR
                else
                {
                    $err
                    = 'QUERY FAILURE:'
                    . ' ERRNO: '
                    . $this->mysqli->errno
                    . ' ERROR: '
                    . $this->mysqli->error
                    . ' QUERY: '
                    . $sql
                    ;
                    trigger_error($err, E_USER_ERROR);
                }
            }
        }
        $this->keys[$this->cnt] = $key;
        return $key;
    }

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

skullnobrainsCommented:
if i understand properly, you don't want to update the row if the same id is used a second time but rather reject the insertion

1) you need to create a unique index on (SubmissionId, Race)

and change a little your code

2)

# run the query with no error handling : we'll handle errors below
$ret=@mysql_query($query);

# if the insertion was successful ( which means no duplicate either since you have a unique index )
$ret and die('all happy : insertion successful');

# if the insertion failed because of a duplicate key
mysql_errno() === 1062 and die('already submitted');

# if it failed for another reason
die(mysql_error());

Open in new window

---

alternatively if for some reason you cannot create the index, you can run a select query beforehand. ideally you'd need to lock the table first in order to prevent race conditions if your code is called multiple times at once with the same id.
1
Ray PaseurCommented:
If perraceid is an AUTO_INCREMENT key you can take it out of the query entirely.

If you're looking to track the date/time when a row was inserted or updated, you can add a column that uses a TIMESTAMP data type.  Omit this from the query string, too.  MySQL will automatically handle setting and updating the value.  You don't have to put CURDATE() into the query.  One less thing to test!

You can use ALTER TABLE .. ADD to add the unique key constraint.
0
skullnobrainsCommented:
MySQL will automatically handle setting and updating the value.  You don't have to put CURDATE() into the query

yes but don't forget you need the column to be declared with "on update current timestamp" for this to work. the timestamp will be updated for both inserts and updates. replace obviously as well. on duplicate key updates will update the field either if explicitely specified in the clause of if the clause produces an actual replacement ( the latter is undocumented so i'm unsure you can rely on that behavior )
0
James MurrellProduct SpecialistAuthor Commented:
Big thanks Ray Paseur, i am slowing working at learning new way. but is a nightmare for me.... i think i get what you are saying....
also thanks skullnobrains  unsure where i would put your code....


If i mark the two fields unique , in my mind it would not allow
3, 34
3,17
3,67  

this is ok but

3, 34
3,17
3,17

is not allowed
0
skullnobrainsCommented:
unsure where i would put your code


replace whatever is below line 15 with this code.

if you have created the required index, it will work as stated.
0
Ray PaseurCommented:
I think you're right about the example here if the UNIQUE index covers both columns.  If either column is UNIQUE all by itself, like an AUTO_INCREMENT PRIMARY KEY, that might not work the way you want, so it's worth careful testing.

I'm pretty sure that both TIMESTAMP updates and affected_rows counts are not changed if the data in the row is not changed.  Eg: a row that contains "James" in the name column will not be changed by an UPDATE or REPLACE query that sets name='James' in that row.  I think that REPLACE INTO is a MySQL-only extension of the SQL standard.  It's very handy!  The name REPLACE is a little misleading.  The query actually deletes, then inserts.  And it can affect multiple rows.  Contrast ON DUPLICATE KEY UPDATE which will only hit one row, no matter how many rows match the key.
0
James MurrellProduct SpecialistAuthor Commented:
Thanks guys i have learnt some, but need to learn so much more
0
Ray PaseurCommented:
... need to learn so much more
Ha!  Don't we all!

Best of luck with your project.
1
skullnobrainsCommented:
good luck from here as well



@ray

I'm pretty sure that both TIMESTAMP updates and affected_rows counts are not changed if the data in the row is not changed.

of course. i'm uncertain what the behavior of on update current timestamp is when using on duplicate key update and the constraint is fired but the clause does not produce an actual change to the stored data

I think that REPLACE INTO is a MySQL-only extension of the SQL standard

it is ! but other sql server implementations have equivalents ( such as insert or replace into ... ), and using on duplicate key update ( or equivalents ) can produce the same behavior.

Contrast ON DUPLICATE KEY UPDATE which will only hit one row, no matter how many rows match the key.

not sure what you mean : on duplicate key update can work with multiple rows : example  :

INSERT INTO mytable ( key, counter )
VALUES
('a' , 2)
,('b' ,6)
/* ,(...) */
on duplicate key update
set counter = ifnull ( counter , VALUES ( counter ) , counter + VALUES ( counter ) )

Open in new window


i'm pretty sure i don't need to explain what the above does. but yes each key ( or whatever combination of values triggers a unique constraint ) will be updated only once
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.