blackwolf23 Posted June 7, 2007 Share Posted June 7, 2007 Hello, I'm having difficulty updating two tables that are related by a foreign key. I've written two functions that update the User's profile which is contained in the User and UserProfile tables. The first function works fine and updates the User's details in the User table but the second functiion gives me the error 'Invalid query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Weight = '33' UserID = 0 WHERE Users.UserID = UserProfile.UserID' at line 1 Whole query: UPDATE UserProfile set Age = '33', Dob = '33', Height = '33' Weight = '33' UserID = 0 WHERE Users.UserID = UserProfile.UserID I know it is to do with how I am passing the ".$_SESSION['userID']." to the Update statement but I am not certain on exactly how to do this. Here is my code for both functions: function register2($Username,$Firstname,$Surname,$Email) { $conn = connect(); $query = "UPDATE Users set Firstname = '$Firstname', Surname = '$Surname', Email = '$Email' WHERE Username ='$Username'"; $verify = mysql_query($query); $userID = @mysql_insert_id(); $_SESSION['userID'] = $userID; } function Update_profile2($Age,$Dob,$Height,$Weight,$userID) { $conn = connect(); mysql_insert_id(); $query = "UPDATE UserProfile set Age = '$Age', Dob = '$Dob', Height = '$Height' Weight = '$Weight' UserID = ".$_SESSION['userID']." WHERE Users.UserID = UserProfile.UserID"; $verify = mysql_query($query); } Quote Link to comment Share on other sites More sharing options...
trq Posted June 7, 2007 Share Posted June 7, 2007 Is session_start() called within both these functions scope? Quote Link to comment Share on other sites More sharing options...
blackwolf23 Posted June 7, 2007 Author Share Posted June 7, 2007 Its called within the main script. <?php require_once('Functions.php'); //Get the post variables $Firstname = $_POST['fname']; $Surname = $_POST['sname']; $Email= $_POST['email']; $Age = $_POST['age']; $Dob = $_POST['dob']; $Height = $_POST['height']; $Weight = $_POST['weight']; //Start the session session_start(); if(!isset($_SESSION['Valid_User'])) { header('Location:Login.php'); } if(isset($_SESSION['Valid_User'])) { echo 'Welcome, '.$_SESSION['Valid_User']. ' <br/>'; } //Update the user's details in the User table register2($_SESSION['Valid_User'],$Firstname,$Surname,$Email); //Update the User's details in the Profile table Update_profile2($Age,$Dob,$Height,$Weight,$userID); Quote Link to comment Share on other sites More sharing options...
trq Posted June 7, 2007 Share Posted June 7, 2007 What does this produce? <?php function Update_profile2($Age,$Dob,$Height,$Weight,$userID) { $conn = connect(); mysql_insert_id(); $query = "UPDATE UserProfile set Age = '$Age', Dob = '$Dob', Height = '$Height' Weight = '$Weight' UserID = ".$_SESSION['userID']." WHERE Users.UserID = UserProfile.UserID"; die($query); $verify = mysql_query($query); } Quote Link to comment Share on other sites More sharing options...
blackwolf23 Posted June 7, 2007 Author Share Posted June 7, 2007 Prints out the following: UPDATE UserProfile set Age = '33', Dob = '33', Height = '33' Weight = '33' UserID = 0 WHERE Users.UserID = UserProfile.UserID Quote Link to comment Share on other sites More sharing options...
trq Posted June 7, 2007 Share Posted June 7, 2007 Your problem is that mysql_insert_id returns the value of the last autoincrement added after an INSERT query. Your running UPDATE queries. Are you trying to ADD new records or UPDATE existing ones? Quote Link to comment Share on other sites More sharing options...
blackwolf23 Posted June 7, 2007 Author Share Posted June 7, 2007 Update an existing one but then how do I ensure the correct record in the Profile table is updated? Both tables are linked by the UserID column. Quote Link to comment Share on other sites More sharing options...
blackwolf23 Posted June 7, 2007 Author Share Posted June 7, 2007 I tried the following query but I still can't get the UserProfile table to update. $query = "UPDATE UserProfile set Age = '$Age', Dob = '$Dob', Height = '$Height' Weight = '$Weight' WHERE UserID IN (SELECT UserID from Users WHERE Username = '".$Username."')"; 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.