We help IT Professionals succeed at work.

check mysql insert

James Murrell
on
272 Views
Last Modified: 2017-03-12
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
Comment
Watch Question

CERTIFIED EXPERT

Commented:
- 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 MurrellQA Product Specialist
CERTIFIED EXPERT

Author

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
Most Valuable Expert 2011
Author of the Year 2014
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Most Valuable Expert 2011
Author of the Year 2014

Commented:
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.
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
James MurrellQA Product Specialist
CERTIFIED EXPERT

Author

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
CERTIFIED EXPERT

Commented:
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.
Most Valuable Expert 2011
Author of the Year 2014
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
James MurrellQA Product Specialist
CERTIFIED EXPERT

Author

Commented:
Thanks guys i have learnt some, but need to learn so much more
Most Valuable Expert 2011
Author of the Year 2014

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

Best of luck with your project.
CERTIFIED EXPERT

Commented:
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