We help IT Professionals succeed at work.

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

Boban Gogic
Boban Gogic asked
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.
Watch Question

Chris GralikeSpecialist

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.
Most Valuable Expert 2017
Distinguished Expert 2018
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