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

check if value exists
I have googled this for a while and I am getting lots of different results. Is there a standard meth

$variable = $variable
Hello

Sorry if this is a really simple question but I have run out of ideas. Is there a reall

What are causes of a connection-timeout with fopen()?
Hello! Here is the situation: The server I host my website on just upgraded it's PHP build from 4.4.

Echo multiple lines of html code
Hi guys,

I would like to know if there is a way to echo multiple lines of html codes.

problem with query error
First Thanks to those who helped me on my previous posts, and the following code i'm using is not mi

shift numbers with paging
Hi

How would i be able to display 10 numbers 1 2 3 4 5 6 7 8 9 10
and when I click next

Help Optimizing code
Good Morning,

I wrote a small import function for a website of mine and I know there has to b

Dynamically allocating the number of rows in a table based on a variable value
Hi,
I have situation here which i cannot resolve, I have a variable temp which stores the num

Oracle11g Patch issue
Hi all,

I'm getting the following error while installing Oracle11g Patch 11.1.0.7.0 on IBM-AI

Schedule editor
Hi,

I have a question related to Schedule editor.
If we schedule a BLS for every 30