[SOLVED] Php and MySql problem

[blackwolf23]

Hello,

 

I'm having trouble formulating the Sql query I require for inserting additional information about a user into my website. The the user first registers by filling out a form and the form information is inserted into the User table. The user is then redirected to a page called UserProfile where they enter some more information and this should be inserted into the UserProfile table. I'm having trouble figuring out how to write the Insert query in a way that will link the UserProfile information with the User stored in the User table.

 

Here is my current query:

  mysql_insert_id();      
$query = "INSERT into UserProfile (Age,Dob,Height,Weight) values ('".$Age."','".$DOB."', '".$Height."', '".$Weight."')";     
WHERE User.UserID = UserProfile.UserID";      
$verify = mysql_query($query); 

This query results in no information being stored in the UserProfile table.The structure of the two tables look like this:

 

User

-----------

UserID (PK)(Auto_increment)

Firstname

Surname

Address

Email

 

UserProfile

-------------

ProfileID(PK) (Auto_Increment)

UserID

Age

Dob

Height

Weight

 

[kael.shipman]

Hi,

 

You're structuring your query incorrectly. MySQL has to open all the tables that it uses in the where clause (i.e., they have to be named in a "from" statment), so you can't run joins on inserts.

 

First you have to make a userID column in your userProfile table. This will be the connection between the two tables. Then you have to store the mysql_insert_id() in a variable (and if you're going across pages, you might as well use a $_SESSION['userId'] variable) and enter that in with the rest of the information in your userProfile table:

 

<?php
start_session();

$insertUser = @mysql_query("insert into User set uname = '$userName', upass = '$userPass'");
$userID = @mysql_insert_id();
$_SESSION['userID'] = $userID;

.....

$query = "INSERT into UserProfile (Age,Dob,Height,Weight,userID) values ('$Age','$DOB', '$Height', '$Weight',".$_SESSION['userID'].")";    
$verify = mysql_query($query);

if ($verify) { echo 'Success!'; }
else { echo 'Failed!'; }
?>

 

You join them later by querying as follows:

 

<?php
$userInfo = @mysql_query("select up.age, up.dob, up.height, up.weight, u.uname, u.upass from userProfile up, user u where u.userID = up.userID");
?>

[blackwolf23]

Thankyou for your quick reply which solved my problem.