INSERT INTO relational database isn't working with php code

Question

The code I have for the function trying to insert the data into the table is

function registerDiet(){
    global $connect, $meat, $seafood, $salad, $name, $username, $age, $email, $password, $hash;

    $statement = mysqli_prepare($connect, "SELECT user_id FROM User WHERE username = ?");
    mysqli_stmt_bind_param($statement, "s", $username);
    mysqli_stmt_execute($statement);
    mysqli_stmt_store_result($statement);
    mysqli_stmt_bind_result($statement, $colUserID);

    $statement = mysqli_prepare($connect, "INSERT INTO Diet (user_id, meat, seafood, salad) VALUES (?, ?, ?, ?)");
    mysqli_stmt_bind_param($statement, "iiii", $colUserID, $meat, $seafood, $salad);
    mysqli_stmt_execute($statement);
    mysqli_stmt_close($statement);
}

The function is called towards the end of the code.

user_id is the primary key in the User table and a foreign key in the Diet table, the relationship has been configured and it works fine when using phpMyAdmin. Meat, seafood and salad field types are all boolean (tinyint) in the database table.

For example when I use

INSERT INTO `Diet` (`user_id`, `meat`, `seafood`, `salad`) VALUES ('46', '0', '0', '0');

in phpMyAdmin it works, anyone able to advise?

Explain what happens when you run the function. Are there any [errors](https://stackoverflow.com/questions/1053424/how-do-i-get-php-errors-to-display)? — Mikey, Mar 05 '18 at 01:41
Try [mysqli_stmt_error](http://php.net/manual/en/mysqli-stmt.error.php) to catch error — vladkras, Mar 05 '18 at 02:20
There are no errors, I've pinpointed it to the fact that '$colUserID' is empty therefore when it's inserting, nothing is being inserted. I just don't know why it's empty - I have other php files I used as reference and $colUserID was not empty.. — NathanG1999, Mar 05 '18 at 02:49

score 0 · Answer 1 · answered Mar 05 '18 at 04:28

You have to close the first statement before opening another one.

function registerDiet(){
global $connect, $meat, $seafood, $salad, $name, $username, $age, $email, $password, $hash;

$statement = mysqli_prepare($connect, "SELECT user_id FROM User WHERE username = ?");
mysqli_stmt_bind_param($statement, "s", $username);
mysqli_stmt_execute($statement);
mysqli_stmt_store_result($statement);
mysqli_stmt_bind_result($statement, $colUserID);

mysqli_stmt_close($statement);

$statement = mysqli_prepare($connect, "INSERT INTO Diet (user_id, meat, seafood, salad) VALUES (?, ?, ?, ?)");
mysqli_stmt_bind_param($statement, "iiii", $colUserID, $meat, $seafood, $salad);
mysqli_stmt_execute($statement);
mysqli_stmt_close($statement);

}

INSERT INTO relational database isn't working with php code

1 Answers1