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

Timer control causing error
I recently decided to add a timer control to an existing page that uses AJAX on my site. As soon as

The IE or FF detector
Hello Friends,

Problem of today ( we seek for php help ) as following ,

we know all th

xml
<?php

echo "<h1>XML Articles</h1>";

$home

upload control not working with update panel
Hi,

I am working with Asp.Net FileUpload control with ajax update panel,

if i use the

question about header() security
is is safe to just use the header() function to redirect someone if they are, say, not logged in? or

Passing Arguments to execlp()
I'm writing a program that mimics a unix shell. It's supposed to take commands with arguments and ex

getAlexaRank($url) function not working
I have made a function to get alexa rank
the site is here: http://mytestsite.rack111.com/1

Linux socket programming
Where can I find a good introduction to socket programming?

Renaming a file that a user uploads to site?
My site allows for registered users to upload images to the site under their own gallery. Currently

Ariba 9r - SAP MM Integration
Hi experts,

The standard Ariba adapter for 9r creates a relationship between Cost Centers