PHP, MySQL Insert data from first table into second, third and fourth

I have one table called users (id, username, password), second table called profiles (id, user_id, username, name, lastname, age, gender, country, company_name), third table called companies (id, user_id, name, resources_id) and fourth table called resources (id, user_id, company_id, money). When users register their account, they need to create their profile. Now when they fill profile data, profiles and companies tables are filled just fine.

My problem is in resources table:
Error: Incorrect integer value: '' for column 'company_id' at row 1
When I refresh that page with error, resources table is filled fine, but now I got double rows for profiles and companies tables.

My code:

    $dbserver                    = "localhost";
    $dbusername                  = "root";
    $dbpassword                  = "1234512345";
    $db                          = "game";

    $conn = new mysqli($dbserver, $dbusername, $dbpassword, $db);

    if ($conn->connect_error)
        die("Connection failed: ".$conn->connect_error);


    $username = $_SESSION['loggedin'];

    $query = "SELECT id FROM users WHERE username = '$username'";
    $result = mysqli_query($conn, $query);
    $row = mysqli_fetch_assoc($result);

    $userId = $row['id'];
    $_POST['id'] = $userId;

    $name = $_POST['name'];
    $lastname = $_POST['lastname'];
    $age = $_POST['age'];
    $gender = $_POST['gender'];
    $country = $_POST['country'];
    $company_name = $_POST['company_name'];
    $_POST['loggedin'] = $username;

    $query = "SELECT id FROM companies WHERE name = '$company_name'";
    $result = mysqli_query($conn, $query);
    $row = mysqli_fetch_assoc($result);

    $companyId = $row['id'];
    $_POST['id'] = $companyId;

    $sql = "INSERT INTO profiles (user_id, username, name, lastname, age, gender, country, company_name)
    VALUES ('$userId', '$username', '$name', '$lastname', '$age', '$gender', '$country', '$company_name')";

    $sql2 = "INSERT INTO companies (user_id, name)
    VALUES ('$userId', '$company_name')";

    $sql3 = "INSERT INTO resources (user_id, company_id)
    VALUES ('$userId', '$companyId')";

    if($conn->query($sql) && $conn->query($sql2) && $conn->query($sql3) === TRUE)
        header("Location: ../../index.php?page=profile");
        echo "Error: ".$conn->error;

Open in new window

What should I do differently? I'm still beginner into this, sorry for long post.
Boban GogicAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chris GralikeSpecialistCommented:
I didnt review all the code yet, but

firstoff it might be wise to use transactions when data is related.
This way you can guarantee that all related data is inserted and correct before actually commiting it to the database.

Second, try vardump($companyID) to verify the datatype is correct.
Julian HansenCommented:
Some comments
You are saving username in the $_SESSION and then referencing the database to get the userid - wouldn't it be more prudent to store the ID in the session as well - it will save you a query.

This code assumes $company_name exists - what if it doesnt?
 $query = "SELECT id FROM companies WHERE name = '$company_name'";

Open in new window

The error is saying that $companyId contains an empty string which implies that this query failed.

You need to put some error checking on your $results to check that the query completed correctly.

Another important topic you should research and apply to your code is transactions. You are doing 3 inserts into your DB as part of one submit - what happens if one fails and your script bombs out - now you have orphaned records in your DB - you don't want that.

A transaction enables you to treat multiple (separate) queries (inserts) as a single (atomic) operation. In other words you start a transaction - do all your queries - if they are all successful you commit them, if any of them fail you rollback the transaction and the database goes back to the state before you started inserting.

Also, I would use prepared statements for your queries.

In MySQLi transactions are relatively straight forward - here is an example using your code.

Please look up prepare(), bind_param(), autocommit(), commit() and rollback() as well as looking at transactions and prepared statements in general.

$sql1 = "INSERT INTO profiles (user_id, username, name, lastname, age, gender, country, company_name) VALUES (?,?,?,?,?,?,?,?)";
$sql2 = "INSERT INTO companies (user_id, name)  VALUES (?,?)";
$sql3 = "INSERT INTO resources (user_id, company_id) VALUES (?,?)";
try {
  $statement = $conn->prepare($sql1);
  $statement->bind_param("dsssdsss", $userId, $username, $name, $lastname, $age, $gender, $country, $company_name);

  $statement = $conn->prepare($sql2);
  $statement->bind_param("ds", $userId, $company_name);

  $statement = $conn->prepare($sql3);
  $statement->bind_param("dd", $userId, $companyId);

  header("Location: ../../index.php?page=profile");
catch(Exception $e){
    // undo everything that was done in the try block in the case of a failure.
    echo "Error: ".$conn->error;

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.