MySql Update problem

[blackwolf23]

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);


  

}

[trq]

Is session_start() called within both these functions scope?

[blackwolf23]

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);

[trq]

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);

}

[blackwolf23]

Prints out the following:

UPDATE UserProfile set Age = '33', Dob = '33', Height = '33' Weight = '33' UserID = 0 WHERE Users.UserID = UserProfile.UserID

[trq]

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?

[blackwolf23]

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.

[blackwolf23]

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."')";