HughbertD Posted December 21, 2007 Share Posted December 21, 2007 Hi there, I have a form which adds records to a DB. The information is stored over 2 tables, so as a result I need to add the records primary_key and a foreign key to another table. My problem is that since I am using an auto_increment value for primary key, I don't ever reference the sql variable, e.g. $_POST[$studentID] and so don't know how I go about grabbing this value to put into my second table. My code is posted below. If any one can help at all I would really really appreciate it. <?php $con = mysql_connect("localhost","root","mickey"); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("jostark", $con); $sql="INSERT INTO student(courseID, firstName, lastName, otherName, title, houseNum, streetName, postalTown, city, postCode, county, country, termHouseNum, termStreetName, termPostalTown, termCity, termPostCode, termCounty, termCountry, phoneNum, mobilePhoneNum, termPhoneNum, email, termEmail,personalEmail, dateOfBirth, gender, facebook, myspace, homeWebsite) VALUES ('$_POST[course]','$_POST[firstname]','$_POST[lastname]','$_POST[othername]','$_POST[title]','$_POST[housenum]', '$_POST[streetname]','$_POST[postaltown]', '$_POST[city]','$_POST[postcode]', '$_POST[county]', '$_POST[country]', '$_POST[termhousenum]', '$_POST[termstreetname]', '$_POST[termpostaltown]','$_POST[termcity]','$_POST[termpostcode]','$_POST[termcounty]', '$_POST[termcountry]', '$_POST[phonenum]', '$_POST[mobilenum]', '$_POST[termphonenum]','$_POST[email]', '$_POST[termemail]', '$_POST[personalemail]', '$_POST[dobYear]$_POST[dobMonth]$_POST[dobDay]', '$_POST[gender]', '$_POST[facebook]', '$_POST[myspace]', '$_POST[homewebsite]')"; if (!mysql_query($sql,$con)) { echo ($sql); die('Error: ' . mysql_error()); } else $con = mysql_connect("localhost","root","mickey"); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("jostark", $con); $sql="INSERT INTO studentplacement(studentID, companyID) values ('$_POST[]', '$_POST[placement]')"; if (!mysql_query($sql,$con)) { echo ($sql); die('Error: ' . mysql_error()); } else $url = "http://localhost/joStark/css/addStudent.php"; $content = file_get_contents($url); echo $content; //<!--<html> //<body> //Record added!<br /> //<a href="http://localhost/joStark/addStudentPlacement.php">Give the student a placement?</a> //<br /> //<a href="http://localhost/joStark/addStudentFinalTest.php">Go Back to previous page?</a> //</body> //</html>--> mysql_close($con) ?> Quote Link to comment Share on other sites More sharing options...
ChadNomad Posted December 21, 2007 Share Posted December 21, 2007 "Auto Increment" automatically increments . The number is automatically assigned and placed into the table, you don't need to add it to your SQL query. I think that's what your thinking about anyway. Quote Link to comment Share on other sites More sharing options...
HughbertD Posted December 21, 2007 Author Share Posted December 21, 2007 This is not the problem. The first table holds the student's details. (studentID, name, course) The second table must hold the studentID and the companyID as a company can have many students and vice versa. So I can add to the first table fine, I don't reference studentID because like you said it auto increments. But I also want to, at the same time add the studentID and companyID to the second table. What do I use to reference the newly created studentID? Quote Link to comment Share on other sites More sharing options...
chigley Posted December 21, 2007 Share Posted December 21, 2007 mysql_insert_id() should do the trick Quote Link to comment Share on other sites More sharing options...
HughbertD Posted December 21, 2007 Author Share Posted December 21, 2007 Fantastic, You lot are absolute gold! Thanks! 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.