mysqli problem - prepared statements

Question

Hello,

I have the following snippet of code from my application, which should work. However, i get the error that: Warning: mysqli_stmt::bind_result() [mysqli-stmt.bind-result]: Number of bind variables doesn't match number of fields in prepared statement in file.

I am using the exact number of columns to bind as to select, and using less or more makes no difference.

if ($getRecords = $con->prepare("SELECT Product_NO, Product_NAME, SUBTITLE, CURRENT_BID, START_PRICE, BID_COUNT, QUANT_TOTAL, QUANT_SOLD, ACCESSSTARTS, ACCESSENDS, ACCESSORIGIN_END, USERNAME, BEST_BIDDER_ID, FINISHED, WATCH, BUYITNOW_PRICE, PIC_URL, PRIVATE_AUCTION, AUCTION_TYPE, ACCESSINSERT_DATE, ACCESSUPDATE_DATE, CAT_DESC, CAT_PATH, COUNTRYCODE, LOCATION, CONDITIONS, REVISED, PAYPAL_ACCEPT, PRE_TERMINATED, SHIPPING_TO, FEE_INSERTION, FEE_FINAL, FEE_LISTING, PIC_XXL, PIC_DIASHOW, PIC_COUNT, ITEM_SITE_ID FROM Sales WHERE PRODUCT_NO = ?")) {
        $getHtml = $con->prepare("SELECT PRODUCT_DESC FROM SaleS WHERE PRODUCT_NO = ?");
        $getHtml->bind_param("i", $id);
        $getHtml->execute();
        $getHtml->bind_result($PRODUCT_DESC);
        $getRecords->bind_param("i", $id);
        $getRecords->execute();
        $getRecords->bind_result($PRODUCT_NO, $PRODUCT_NAME, $SUBTITLE, $CURRENT_BID, $START_PRICE, $BID_COUNT, $QUANT_TOTAL, $QUANT_SOLD, $ACCESSSTARTS, $ACCESSENDS, $ACCESSORIGIN_END, $USERNAME, $BEST_BIDDER_ID, $FINISHED, $WATCH, $BUYITNOW_PRICE, $PIC_URL, $PRIVATE_Sale, $Sale_TYPE, $ACCESSINSERT_DATE, $ACCESSUPDATE_DATE, $CAT_DESC, $CAT_PATH, $COUNTRYCODE, $LOCATION, $CONDITIONS, $REVISED, $PAYPAL_ACCEPT, $PRE_TERMINATED, $SHIPPING_TO, $FEE_INSERTION, 
        $FEE_FINAL, $FEE_LISTING, $PIC_XXL, $PIC_DIASHOW, $PIC_COUNT, $ITEM_SITE_ID);
        while ($getRecords->fetch()) {

Answer 1

Your first SQL query has the word "SELECT" repeated.

Also, make sure MySQL actually supports having two different, prepared statements on the same connection. This has been a problem for me before, but I never figured out if this was a library problem (I did not use mysqli) or a limitation in MySQL.

Answer 2

What PHP version are you using? Does the error occur at line 4 or 7?

Answer 3

I have a feeling that you have to fetch from a prepared statement before you can instantiate another one. Try checking that.

Edit:

I ran this test, and the results are below:

Stmt1
MySQL: ID: 1
PHP: int(1)

Stmt2
MySQL: ID: 1
PHP: int(0)

So it looks to me like $stmt2 is being ignored. $stmt2 works properly if I close $stmt1 before executing $stmt2:

$stmt1->fetch();
$stmt1->close();

$stmt2->execute();
$stmt2->bind_result($commentId);
$stmt2->fetch();

Edit2:

Try this:

$query1 = 'SELECT Product_NO, Product_NAME, SUBTITLE, CURRENT_BID, START_PRICE, BID_COUNT, QUANT_TOTAL, QUANT_SOLD, ACCESSSTARTS, ACCESSENDS, ACCESSORIGIN_END, USERNAME, BEST_BIDDER_ID, FINISHED, WATCH, BUYITNOW_PRICE, PIC_URL, PRIVATE_AUCTION, AUCTION_TYPE, ACCESSINSERT_DATE, ACCESSUPDATE_DATE, CAT_DESC, CAT_PATH, COUNTRYCODE, LOCATION, CONDITIONS, REVISED, PAYPAL_ACCEPT, PRE_TERMINATED, SHIPPING_TO, FEE_INSERTION, FEE_FINAL, FEE_LISTING, PIC_XXL, PIC_DIASHOW, PIC_COUNT, ITEM_SITE_ID FROM Sales WHERE PRODUCT_NO = ?';
$query2 = 'SELECT PRODUCT_DESC FROM SaleS WHERE PRODUCT_NO = ?';

if ($getRecords = $con->prepare($query1)) {
    $getRecords->bind_param("i", $id);
    $getRecords->execute();        
    $getRecords->bind_result($PRODUCT_NO, $PRODUCT_NAME, $SUBTITLE, $CURRENT_BID, $START_PRICE, $BID_COUNT, $QUANT_TOTAL, $QUANT_SOLD, $ACCESSSTARTS, $ACCESSENDS, $ACCESSORIGIN_END, $USERNAME, $BEST_BIDDER_ID, $FINISHED, $WATCH, $BUYITNOW_PRICE, $PIC_URL, $PRIVATE_Sale, $Sale_TYPE, $ACCESSINSERT_DATE, $ACCESSUPDATE_DATE, $CAT_DESC, $CAT_PATH, $COUNTRYCODE, $LOCATION, $CONDITIONS, $REVISED, $PAYPAL_ACCEPT, $PRE_TERMINATED, $SHIPPING_TO, $FEE_INSERTION, 
    $FEE_FINAL, $FEE_LISTING, $PIC_XXL, $PIC_DIASHOW, $PIC_COUNT, $ITEM_SITE_ID);
    while ($getRecords->fetch()) {
        // stuff
    }
    $getRecords->close();

    $getHtml = $con->prepare($query2);
    $getHtml->bind_param("i", $id);
    $getHtml->execute();
    $getHtml->bind_result($PRODUCT_DESC);
    // Stuff with $getHtml (e.g. fetch)
}