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

Boban Gogic
Boban Gogic used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Chris GralikeSpecialist

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial