Avatar of James Murrell
James Murrell
Flag for United Kingdom of Great Britain and Northern Ireland asked on

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
PHPMySQL ServerSQL

Avatar of undefined
Last Comment
skullnobrains

8/22/2022 - Mon
skullnobrains

- 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.
James Murrell

ASKER
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
ASKER CERTIFIED SOLUTION
Ray Paseur

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Ray Paseur

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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
James Murrell

ASKER
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
skullnobrains

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.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
James Murrell

ASKER
Thanks guys i have learnt some, but need to learn so much more
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Ray Paseur

... need to learn so much more
Ha!  Don't we all!

Best of luck with your project.
skullnobrains

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