PHP - mysqli_stmt_execute() always returns false

Question

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.

If it returns false, then you need to check for [mysqli errors](http://php.net/manual/en/mysqli.error.php) to find out why. The syntax looks correct, so there's something else going on. — aynber, Sep 18 '19 at 18:28
@aynber think I did it right. It gives the error I put on the edit — rcoelho14, Sep 18 '19 at 18:42
That is odd. That would normally only show up when using the query directly in a query instead of in a prepare statement. — aynber, Sep 18 '19 at 18:43
Add `else { echo myqli_error($link); }` to `if(mysqli_stmt_execute($stmt2))` to see the correct error message. — Barmar, Sep 18 '19 at 18:56
@Barmar it gives me `Fatal error: Uncaught Error: Call to undefined function myqli_error() in C:\xampp\htdocs\****************\component\calculation.php:71 Stack trace: #0 {main} thrown in C:\xampp\htdocs\*******************\component\calculation.php on line 71` - Line 71 is where I put that mysqli_error($link); — rcoelho14, Sep 18 '19 at 19:00
@Barmar So...it seems I am special kind of retard. `Duplicate entry '0000000000000000000000000000000000000000' for key 'parent_hash_UNIQUE'` I still don't have the right query for this...this field has to take the hash of the last entry inserted on the DB (or that value you see there if there aren't any entries). And I forgot I had set it as unique — rcoelho14, Sep 18 '19 at 19:07
Now it inserts correctly (I removed the unique constraint) but it gives me a `Warning: mysqli_stmt_fetch(): Couldn't fetch mysqli_stmt` on `while (mysqli_stmt_fetch($stmt)) {` — rcoelho14, Sep 18 '19 at 19:13
Don't call `mysqli_close()` inside the loop. See https://stackoverflow.com/questions/19937880/mysqliquery-couldnt-fetch-mysqli — Barmar, Sep 18 '19 at 19:17
Or `mysqli_stmt_close()`. You shouldn't close the statement until you're done using it. — Barmar, Sep 18 '19 at 19:18
BTW, there's no need for this loop. You can use `INSERT INTO calc (...) SELECT ... FROM user_ip WHERE ...` — Barmar, Sep 18 '19 at 19:18
You should also use a bound parameter for the `SELECT` query rather than concatenating a variable. — Barmar, Sep 18 '19 at 19:19
Use `mysqli_stmt_bind_param()` for `$myIP` instead of concatenating. — Barmar, Sep 18 '19 at 19:29

Barmar · Accepted Answer · 2019-09-18T21:25:49.120

2

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

edited Sep 18 '19 at 21:25

answered Sep 18 '19 at 19:25

Barmar

741,623
53
500
612

Oh! I didn't know that! Thank you! – rcoelho14 Sep 18 '19 at 19:31
Just tested it, it gives me an error `Fatal error: Uncaught Error: Call to a member function bind_param() on boolean in C:\xampp\htdocs\*************************\component\calculation.php:50 Stack trace: #0 {main} thrown in C:\xampp\htdocs\*******************\component\calculation.php on line 50` – rcoelho14 Sep 18 '19 at 21:19
That means the `prepare` call is getting an error, so you should print that error message. I've added error checking there. – Barmar Sep 18 '19 at 21:26
I had to guess the column name in your `user_ip` table, since the question uses `SELECT *`. – Barmar Sep 18 '19 at 21:27

PHP - mysqli_stmt_execute() always returns false

1 Answers1