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,

Your Answer:

Login to answer

Other forums

Not showing whole name with mail () script
I sent up a simple mail form with the PHP mail() script. One problem is when it sends an email with

php form help
Hey,

I use a control file to set my meta tags and titles

Here's an example
Code: &a

Help! refer to a friend script with captcha code
Hi guys, I am posting on here in desperate need for some help with an ongoing search I have been doi

help with Get_
ok some how this is not working i am not sure what i am doing wrong here but what this does is when

HTML Form Server Side Validation
Hi, Im new here, im currently doing a website for a friend, and I have designed using snippets from

captcha error
I have been trying to implement a captcha in php...

here is the code..
Code: (php) [Select

Undefined index on my form
ok im getting Undefined index on this line.. print_r($_REQUEST['form']);

below is the full sc

Problem with Sending Mail by PHP
hello all,
I m new to php, can u plz provide me the code to send mail by php having CAPTC

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

whats wrong with my code please help!!!
this is the error


Warning: mysql_close(): supplied argument is not a valid MySQL-Link res