Referential Integrity problem when inserting into 2 tables at same time
Posted on
16th Feb 2014 07:03 pm by
admin
Hello Everyone,
Im relativity new to PHP and MySQL and i have come up against a problem. i have 2 tables that are linked via foreign key constraints.
Code: --
-- Table structure for table `rides`
--
CREATE TABLE rides (
ride_date TIMESTAMP,
ride_name VARCHAR(50) NOT NULL,
planned_distance_travelled INT(5),
actual_distance_travelled INT(5),
planned_time_taken INT(5),
actual_time_taken INT(5),
average_heart_rate INT(5),
calories_burned INT(5),
weather_conditions VARCHAR(100),
feelings_about_ride VARCHAR(100),
PRIMARY KEY (ride_date)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Table structure for table `usersrides`
--
CREATE TABLE usersrides (
userid SMALLINT UNSIGNED NOT NULL,
ride_date TIMESTAMP,
weight int(5),
PRIMARY KEY (userid,ride_date),
CONSTRAINT `fk_usersrides_ride_date` FOREIGN KEY (ride_date) REFERENCES ride (ride_date) ON DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
As you can see they are linked by the ride_date, which is a timestamp. This means that inorder to successfully insert data into the tables, the data must be inserted at the same time to the second.
This is the PHP script i am using to insert the data into the 2 tables:
Code: <?php
echo connect();
$userid = $_SESSION['userid'];
$weight = 0;
$ride_name = $_POST['ride_name'];
$planned_dist_trav = $_POST['planned_dist_trav'];
$actual_dist_trav = $_POST['actual_dist_trav'];
$planned_time_tak = $_POST['planned_time_tak'];
$actual_time_tak = $_POST['actual_time_tak'];
$avg_heart_rate = $_POST['avg_heart_rate'];
$calories= $_POST['calories'];
$weather_cond = $_POST['weather_cond'];
$feelings = $_POST['feelings'];
$ride_name = stripslashes($ride_name);
$planned_dist_trav = stripslashes($planned_dist_trav);
$actual_dist_trav = stripslashes($actual_dist_trav);
$planned_time_tak = stripslashes($planned_time_tak);
$actual_time_tak = stripslashes($actual_time_tak);
$avg_heart_rate = stripslashes($avg_heart_rate);
$calories = stripslashes($calories);
$weather_cond = stripslashes($weather_cond);
$feelings = stripslashes($feelings);
$ride_name = mysql_real_escape_string($ride_name);
$planned_dist_trav = mysql_real_escape_string($planned_dist_trav);
$actual_dist_trav = mysql_real_escape_string($actual_dist_trav);
$planned_time_tak = mysql_real_escape_string($planned_time_tak);
$actual_time_tak = mysql_real_escape_string($actual_time_tak);
$avg_heart_rate = mysql_real_escape_string($avg_heart_rate);
$calories = mysql_real_escape_string($calories);
$weather_cond = mysql_real_escape_string($weather_cond);
$feelings = mysql_real_escape_string($feelings);
//add the date into the rides table
$sql="INSERT INTO rides (ride_name, planned_distance_travelled, actual_distance_travelled, planned_time_taken, actual_time_taken, average_heart_rate, calories_burned, weather_conditions, feelings_about_ride) VALUES ('$ride_name','$planned_dist_trav','$actual_dist_trav','$planned_time_tak','$actual_time_tak','$avg_heart_rate','$calories','$weather_cond','$feelings')";
$result=mysql_query($sql);
if (!$result) {
die('Error: ' . mysql_error());
}elseif ($result) {
echo "1 Record Added to the rides table";
echo "<BR>";
}
//add the date into the usersrides table
$sql1="INSERT INTO usersrides (userid, weight) VALUES ('$userid','$weight')";
$result1=mysql_query($sql1);
if (!$result1) {
die('Error: ' . mysql_error());
}elseif ($result1) {
echo "1 Record Added to the usersrides table";
//header("Refresh: 2; url=home.php");
}
mysql_close()
?>
I realise this is alot of reading, but i didnt know how to explain this situation any other way. At the moment, data is being inserted into the 'rides' table, but when it gets to the usersrides table a foriegn key error comes up.
What i would like to know is....Is what im trying to do possible?
Thanks in advance,
-Regards,
No comments posted yet
Your Answer:
Login to answer
256
24
Other forums
Delete HTML file after loading
I have limited experience with php and its been a year or two since I've last used it. I have a sma
Help =( !! Upload Pics [PHP script]
Hello
I need help with a php script [MULTIPLE UPLOAD IMAGES] , where I want to add a feature (wat
While Problem
i am having a problem with a while statement here is the code
Code: [Select]<?php
sess
Why does this file not return file names that start with numbers?
This file returns a list of filenames to help populate a drop down in my form. For some reason it ig
Line break?
Hi, I'm new to the forum and new to php. I'm not sure if I'm using the correct terminology so here i
Writing Windows Device Drivers with C
Yes: one of the nice features of C is void*. You can put anything there and get anything out. Very
Checking if field is empty AND using !is_numeric
Hi lads,
Need to be able to display an error message (using isset?) that says "Cant lea
DataTable Help Required
Hi all I am creating an app that utilises and MS Access back end and queries it quite a bit at vario
PHP hyperlinks generator - HELP plz
Hi
I need some help to get this done using php:
1 - I have few hyperlinks say 500
wav to mp3 + goDaddy
I need to convert wav files to mp3s. From what I have found so far one way to do it is to have my PH