BeanoEFC Posted December 17, 2009 Share Posted December 17, 2009 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 Quote Link to comment Share on other sites More sharing options...
ToonMariner Posted December 17, 2009 Share Posted December 17, 2009 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. Quote Link to comment Share on other sites More sharing options...
BeanoEFC Posted December 17, 2009 Author Share Posted December 17, 2009 Thanks for the reply. I was afraid you were going to say that.... Quote Link to comment Share on other sites More sharing options...
BeanoEFC Posted December 17, 2009 Author Share Posted December 17, 2009 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 Quote Link to comment Share on other sites More sharing options...
ToonMariner Posted December 17, 2009 Share Posted December 17, 2009 Still think its a risky method of maintaining referential integrity... Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.