INSERT INTO关系数据库不能使用php代码

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?

Answer 1

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

}