Database Insertion error

[dhani09]

Hi,
I have the following tables
tbl_user - id, username, password
tbl_userprofile - id, userid, name, surname, gender, nationality,address, mobile, department, email, question, answer.
userid is a foreign key in tbl_userprofile referencing id in tbl_user
I'm trying insert a new user into tbl_userprofile. But i'm not sure how to go about it because of the foreign key constraint. Here's my code.

$name = "";
$surname = "";
$address = "";
$nationality = "";
$gender = "";
$email = "";
$department = "";
$mobile = "";
$question = "";
$answer = "";
$errorMessage = "";

//check if form was submitted
if($_SERVER['REQUEST_METHOD'] == 'POST'){
    $name = $_POST['firstname'];
    $surname = $_POST['lastname'];
    $email = $_POST['email'];
    $address = $_POST['address'];
    $mobile = $_POST['telno'];
    $question = $_POST['question'];
    $answer = $_POST['answer'];
    $nationality = $_POST['nationality'];
    $department = $_POST['department'];
    $gender = $_POST['gender'];

    $username = "root";
    $password = "root";
    $database = "db";
    $server = "host";

    $db_handler = mysql_connect($server, $username, $password);
    $db_found = mysql_select_db($database,$db_handler);

    if($db_found){
        $SQL = "INSERT INTO tbl_userprofile (userid, name, surname, gender, nationality, address, mobile, department, email, question, answer) VALUES 
    ('$name','$surname','$gender','$nationality','$address','$mobile','$department','$email','$question','$answer')";

        $result = mysql_query($SQL);

        mysql_close($db_handler);

        if($result){

        header("Location:index.php");
        }
        else{
            $errorMessage = "ERROR". mysql_error();
        }

    }

    else{
        $errorMessage = "Database Not Found";

    }
}

I know my $SQL query is incomplete. How do i construct it? Thanks

[broj1]

You first have to insert new user data into tbl_user table, somehow creating fields (id could be autoincrement). Then use this user ID and add it to the query for the tbl_userprofile table.

[dhani09]

Okay i understand. I know how to structrue a query to insert data into tbl_user but how i would construct a WHERE clause with the user ID to add data into tbl_userprofile.

[broj1]

You do not need a WHERE clause when you insert a new record. All you need to add is a user ID:

// get user ID form somewhere
$userId = ...

// put it into the query
$SQL = "INSERT INTO tbl_userprofile (userid, name, surname, gender, nationality, address, mobile, department, email, question, answer) VALUES 
('$userId', '$name','$surname','$gender','$nationality','$address','$mobile','$department','$email','$question','$answer')";

You need a WHERE clause when doing a SELECT, UPDATE or DELETE.

[dhani09]

yeah i know this. what i meant was using a select statement to get the id from the tbl_user table like so

$SQL = "INSERT INTO tbl_userprofile (userid, name, surname, gender, nationality, address, mobile, department, email, question, answer) VALUES 
('(SELECT id from tbl_user WHERE *something*) ', '$name','$surname','$gender','$nationality','$address','$mobile','$department','$email','$question','$answer')";

my problem with this approach is i can't figure out the condition in the WHERE clause. I believe this is what you're suggesting as well?

[broj1]

Depends on what user data you know about the new user from the tbl_user table. Most probably it is the usernname:

$username = ...;

$SQL = "INSERT INTO tbl_userprofile (userid, name, surname, gender, nationality, address, mobile, department, email, question, answer) VALUES
((SELECT id from tbl_user WHERE username='$username'), '$name','$surname','$gender','$nationality','$address','$mobile','$department','$email','$question','$answer')";

I do not think you need quotes arround the subquery, but I am not 100% sure.

[dhani09]

Okay but getting the username and password is handled on a separate form and script. So this insertion into tbl_userprofile is on a different script. I'm not sure how i would get the $username that has been collected on a different form and script and use it here.?

[broj1]

OK, but how do you know which user are you inserting data for? How is the sequence of inserting happening? Does the insert into tbl_userprofile come immediately after insert into tbl_user or is there any user interaction in between? Please describe the process.

[dhani09]

yes insertion into tbl_userprofile comes immediately after insertion into tbl_user. First the user provides a username and a password on a form and clicks register. These details are inserted into tbl_user. After clicking register the user is requested to fill a new form(on a new page) where he/she provides the information to be inserted into the tbl_userprofile table. There's no other interaction in between.

[broj1]

Then on the first page save the username into session ($_SESSION array). On the second page read it form the session and use it.

First page:

session_start();
$_SESSION['username'] = $username;
...

Second page:

session_start();
if(isset($_SESSION['username'])) {
    $username = $_SESSION['username'];
    ...
} else {
    die('Error: Username does not exist.');
}

[dhani09]

thanks a lot but there's a little problem. The insert into was giving errors so i used an insert-select instead which seems to work but when i check the database, the information isn't inserted. The page is redirected to index.php(as shown in original code) but the information isn't in the database. Is there a problem with the original code. This is the insert-into i constructed.

$SQL = "INSERT INTO tbl_userprofile (userid, name, surname, gender, nationality, address, mobile, department, email, question, answer) 
SELECT id, '$name', '$surname', '$gender', '$nationality', '$address','$mobile', '$department', '$email', '$question', '$answer'
FROM tbl_user 
WHERE username = '$username'";

[masterjiraya]

@dhani09 you made a wrong move, very confusing syntax on the last post.

try to loearn about SELECT * INTO FROM where.
this was another query technique of copying data from one table to another without fetching before insertint it to another.
make sure your parameters are defined and the indexes are the same.

[broj1]

The subquery should be in parentheses. Also you have to add a WHERE condition to get only one value as a result of the subquery. You can get the username through session as I described in my previous post.

$SQL = "INSERT INTO tbl_userprofile (userid, name, surname, gender, nationality, address, mobile, department, email, question, answer)
(SELECT id WHERE username='$username'), '$name', '$surname', '$gender', '$nationality', '$address','$mobile', '$department', '$email', '$question', '$answer'
FROM tbl_user
WHERE username = '$username'";

To test the query add this temporary debug code just after the query:

// TEMP DEBUG
die($SQL);

This will display the final query and stop the script. You can now examine the query and copy/paste it into phpmyadmin to test how it works. You can also paste it here for us to check it.

[dhani09]

Perfect. It works now. You don't know how happy i am right now. I was able to examine the query with the die($SQL). I found that i'd used the $username variable twice and it was taking the wrong value. Though the query you proposed above gave a syntax error, i used the previous insert-select query i'd posted earlier and it worked. Thanks a lot for your help.