Referential Integrity problem when inserting into 2 tables at same time

[BeanoEFC]

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.

--
-- 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:

<?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,

 

Beano

[ToonMariner]

it will be nigh on impossible to maintain the integrity in that manner - you would be better served having a primary key on table rides and use that as the foreign key in your second table. lastinsertid will give you the value to insert into the other table.

[BeanoEFC]

Thanks for the reply.  I was afraid you were going to say that....

[BeanoEFC]

Just for future reference for any one that might be interested in doing something like this.  It is possible, I worked out my problem. 

 

In the DDL of the 'usersrides' table I miss spelt "rides" on the foreign key constraint and spelt it "ride", this was the problem.

 

It works fine now.  Thanks ToonMariner for your input.

 

Regards,

-Beano

[ToonMariner]

Still think its a risky method of maintaining referential integrity...