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

Form File Upload
I've read through tons of resources and tutorials and as far as I can tell, my code is accurate, but

mySQL and PHP search
Hello,
I am trying to code a project and ran into a brick wall with one of my pages. I am pretty

Inserting into MySQL Newbie
Hi ive got a slight problem where ive made a simple web form where the customer inserts the ammount

PHP error. Need help urgently
Hi,

I am programming a php site and have a problem that i just cant find out how to fix. When

Multiple upload and Resize
I would like some help on my script I have the for my index.php

////

<html&

Getting the full city list from maxminds geoip database
I am suprised I can't find any references to this.

I have maxminds geoip lite installed and i

Streaming local file from PHP while it’s been written to by a CURL process
I am creating a simple Proxy server for my website. Why I am not using mod_proxy and mod_cache is a

NOOB needs help with upload file
The intent of this form is to create a folder for a client and upload an image into another folder '

how to get IP address?
hey guys,

i have this code which is supposed to get me the IP address of my site visitors:

Getting rid of quotes when printing data
Hi Guys, I use the filter_var FILTER_SANITIZE_STRING to filter the textarea input. The function esca