0

I have a database table with two columns 'id' and 'name'.

Then I have some testing php code with mysqli_stmt_fetch which tries to execute mysql queries at various places in the code with mysqli_query. Apparently, inside the fetch loop, the $link variable is not usable, but if I assign the same object to it as at the start, it works.

Also, when I removed the reassignment inside fetch statement, the query with 'baz2' didn't execute even after the fetch statement if it iterated at least once, but worked if it didn't.

What is going on here? Does the fetch statement do something to the $link object? How should I execute queries inside fetch statements?

<?php
$link = mysqli_connect("localhost", "root", "vertrigo", "phpmyadmin");
if (mysqli_connect_errno()) {
    printf("Connect failed: ", mysqli_connect_error());
    exit();
}

$s = "SELECT * FROM test WHERE name='".$_POST["name"]."'";
if ($stmt = mysqli_prepare($link, $s)) {
    $s = "INSERT INTO test VALUES(0, 'baz');";
    if (mysqli_query($link, $s)) 
        echo "Insert success before the fetch. ";
    else 
        echo "Didn't insert before the fetch. ";

    mysqli_stmt_execute($stmt);
    mysqli_stmt_bind_result($stmt, $id, $name);

    while (mysqli_stmt_fetch($stmt)) {
        $s = "INSERT INTO test VALUES(0, 'foo');";
        if (mysqli_query($link, $s)) 
            echo "Insert success inside fetch without reassignment. ";
        else 
            echo "Didn't insert inside fetch without reassignment. ";
        $link = mysqli_connect("localhost", "root", "vertrigo", "phpmyadmin");
        if (mysqli_query($link, $s)) 
            echo "Insert success with reassigned link. ";
        else 
            echo "Didn't insert inside fetch. ";
    }
    $s = "INSERT INTO test VALUES(0, 'baz2');";
    if (mysqli_query($link, $s)) 
        echo "Insert success after the fetch. ";
    else 
        echo "Didn't insert after the fetch. ";
}
else
    echo "coulnd't prepare";
?>

With existing name in $_POST['name'], this php returns "Insert success before the fetch. Didn't insert inside fetch without reassignment. Insert success with reassigned link. Insert success after the fetch. ".

Without: "Insert success before the fetch. Insert success after the fetch. "

BoltKey
  • 1,994
  • 1
  • 14
  • 26
  • ^^ No, that' won't help you. This code isn't inside context of a class, and the procedural `mysqli_*()` are fine. – Michael Berkowski Feb 07 '16 at 14:24
  • 1
    You need to check errors after the failures. Instead of custom strings, echo `mysqli_error($link);`. My suspicion is that you have a "commands out of sync" situation between an incomplete fetch and attempt at a new query. – Michael Berkowski Feb 07 '16 at 14:26
  • If that's the case, see also http://stackoverflow.com/questions/3632075/mysqli-giving-commands-out-of-sync-error-why – Michael Berkowski Feb 07 '16 at 14:26
  • Michale Berkowski: Yup, it is "commands out of sync". Thanks. – BoltKey Feb 07 '16 at 14:31
  • Thought so (the question I looked at just before yours was the same problem). You'll need some logic with `mysqli_stmt_store_result()`. http://php.net/manual/en/mysqli-stmt.store-result.php – Michael Berkowski Feb 07 '16 at 14:35

0 Answers0