Link to home
Start Free TrialLog in
Avatar of James Murrell
James MurrellFlag 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
Avatar of skullnobrains
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.
Avatar of 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
Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks guys i have learnt some, but need to learn so much more
... need to learn so much more
Ha!  Don't we all!

Best of luck with your project.
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