PHP – mysqli_stmt_execute() always returns false

Good afternoon,

I am building a calculator, using JS and PHP, and need to INSERT the results and other things to a database.

I am using prepared statements to achieve that, but for some reason, I am unable to INSERT anything, as mysqli_stmt_execute() is always returning false.

$link = new_db_connection();
$link2 = new_db_connection();
$stmt = mysqli_stmt_init($link);
$stmt2 = mysqli_stmt_init($link2);
$query = "SELECT * FROM user_ip WHERE user_ip = '" . $myIP . "'";
$query2 = "INSERT INTO calc (time,operation,result,bonus,hash,fk_id_user_ip) VALUES (?,?,?,?,?,?)";

if (mysqli_stmt_prepare($stmt, $query)) {
    mysqli_stmt_bind_result($stmt, $id_user, $user_ip);

    if (mysqli_stmt_execute($stmt)) {
        while (mysqli_stmt_fetch($stmt)) {

            if (mysqli_stmt_prepare($stmt2, $query2)) {

                mysqli_stmt_bind_param($stmt2, 'issisi', $timestamp, $operation, $result, $bonus_DB, $hash, $id_user);

                if (mysqli_stmt_execute($stmt2)) {

                    mysqli_stmt_close($stmt);
                    mysqli_stmt_close($stmt2);
                    mysqli_close($link);
                    mysqli_close($link2);
                }
            }
        }
    } else {
        mysqli_close($link);
    }
}

This is how I did it (still need a few checks to add the IP if it doesn’t exist). It does everything without a problem until if (mysqli_stmt_execute($stmt2)) returns false, so it doesn’t add anything to the DB.

Am I missing something here?

EDIT:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '?,?,?,?,?,?)' at line 1 I wrote this:

if (!mysqli_query($link2, $query2)) {
                    echo("Error description: " . mysqli_error($link2));
                }

before the execute.

Answer

The problem is that you’re closing the statements and links in the loop, so you can’t reuse them as the loop repeats.

But you don’t need any of this looping, it can be done entirely with one query that inserts the result of SELECT.

$link = new_db_connection();
$stmt = $link->prepare("
    INSERT INTO calc (time,operation,result,bonus,hash,fk_id_user_ip)
    SELECT ?,?,?,?,?, id_user
    FROM user_ip
    WHERE user_ip = ?");
if (!$stmt) {
    echo "Prepare error: " . mysqli_error($link);
    exit;
}
$stmt->bind_param('ississ', $timestamp, $operation, $result, $bonus_DB, $hash, $myIP);
if (!$stmt->execute()) {
    echo "Error description: " . mysqli_error($link);
}
$link->close();