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:
<?php
    session_start();

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

    if(isset($_SESSION['loggedin']))

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

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

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

    //INSERT DATA INTO RESOURCES
    $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");
        die();
    }
    else
    {
        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?
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.
https://dev.mysql.com/doc/refman/5.7/en/commit.html
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.
0
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 {
  $conn->autocommit(false);
  $statement = $conn->prepare($sql1);
  $statement->bind_param("dsssdsss", $userId, $username, $name, $lastname, $age, $gender, $country, $company_name);
  $statement->execute();

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

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

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

Open in new window

1

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
HTML

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.