Page 1 of 1

Number of variables doesn't match number of parameters in pr

Posted: Tue Nov 02, 2010 10:59 am
by tonyoyo
Hi people!

I'm getting this error message from MySQL DB on PHP/Apache 2:

[text]Warning: mysqli_stmt::bind_param() [mysqli-stmt.bind-param]: "Number of variables doesn't match number of parameters in prepared statement"[/text]

And here's my code:

Code: Select all

$sql = "UPDATE
		secondhandBooks
	SET
		`bookTitle` = ?,
		`bookAuthor` = ?,
		`condition` = ?,
		`price` = ?,
		`Available` = ?,
		`Weight` = ?,
		`Publisher` = ?,
		`Description` = ?,
		`Format` = ?
	WHERE
		`ItemID` = ? ";


    if ( ! $stmt = $db->prepare($sql))
    {
        $feedback = $stmt->error;
    }
    else
    {
	$stmt->bind_param('ssiiiissii', $fTitle, $fAuthor, $fCondition, $fPrice, $fAvailable, $fWeight, $fPublisher, $fDescription, $fFormat, $itemid);

        if ( ! $stmt->execute())
        {
            $feedback = $stmt->error;
        }
	else
	{
            $_SESSION['updatefeedback'] = '<p>Item was updated</p>';
            $stmt->close();
	}
    }
What I really don't understand is why this code does work:

Code: Select all

$sql = "INSERT INTO
		secondhandBooks
		(
		`bookTitle`,
		`bookAuthor`,
		`condition`,
		`price`,
		`Available`,
		`Weight`,
		`Publisher`,
		`Description`,
		`Format` 
		)
	VALUES
		( ?, ?, ?, ?, ?, ?, ?, ?, ? )";

    if ( ! $stmt = $db->prepare($sql))
    {
        $feedback = $stmt->error;
    }
    else
    {
	$stmt->bind_param('ssiiiissi', $fTitle, $fAuthor, $fCondition, $fPrice, $fAvailable, $fWeight, $fPublisher, $fDescription, $fFormat);

        if ( ! $stmt->execute())
        {
            $feedback = $stmt->error;
        }
	else
	{
            $_SESSION['updatefeedback'] = '<p>Item was added</p>';
            $stmt->close();
	}
    }
So... what's with that then? How come the UPDATE fails with a lie but the INSERT runs perfectly?

A very nice gentleman pointed out that 'condition' was a reserved word and so I needed back-ticks around the column names. Adding those fixed my INSERT problem but not my UPDATE problem. Since then the post has gone dead... so here it is fresh and new!

The help from a PHP/SQL guru would be most appreciated at this point.

Re: Number of variables doesn't match number of parameters i

Posted: Fri Nov 19, 2010 3:39 am
by tonyoyo
Literally no-one knows? That's depressing...

Re: Number of variables doesn't match number of parameters i

Posted: Fri Nov 19, 2010 5:01 am
by Benjamin
Try adding the following line before you prepare the statement.

Code: Select all

$stmt->store_result();
I never use bind_param so I've never ran into this. I would also make sure all the variables are set and not null. (Not sure if that would cause this problem.)

There is a bug report on this: http://bugs.php.net/bug.php?id=31037 which you may want to add to.

Re: Number of variables doesn't match number of parameters i

Posted: Fri Nov 19, 2010 9:21 am
by mikosiko
@tonyoyo:

Something doesn't match between your previous post and this one...
in your previous post your said:
That's my insert fixed, but I have very similar code for the update part:

Code: Select all

if (!$isSecondHandItem )
        $sql = "UPDATE
                                Catalogue
                        SET
                                Title = ?,
                                Author = ?,
                                Price = ?,
                                Publisher = ?,
                                Description = ?,
                                Format = ?,
                                Available = ?,
                                DisplayOnIndex = ?,
                                Category = ?,
                                Image = ?,
                                SubCategory = ?,
                                offer = ?,
                                Weight = ?
                        WHERE
                                ItemID = ? ";
else
        $sql = "UPDATE
                                secondhandBooks
                        SET
                                `bookTitle` = ?,
                                `bookAuthor` = ?,
                                `condition` = ?,
                                `price` = ?,
                                `Available` = ?,
                                `Weight` = ?,
                                `Publisher` = ?,
                                `Description` = ?
                        WHERE
                                `ItemID` = ? ";
And the bind:

Code: Select all

if (!$isSecondHandItem )
        $stmt->bind_param('ssissiisiiiiii', $fTitle, $fAuthor, $fPrice, $fPublisher, $fDescription, $fFormat, $fAvailable, $fNewAddition, $fCategory, $fImage, $fSubCategory, $fFreeDelivery, $fWeight, $itemid);
else
        $stmt->bind_param('ssiiiissi', $fTitle, $fAuthor, $fCondition, $fPrice, $fAvailable, $fWeight, $fPublisher, $fDescription, $itemid);
both post doesn't match right?.

Now... if your error message is :
"Number of variables doesn't match number of parameters i"

did you do the basic test of validate which bind sentences is executing?... because clearly they have different parameters requirements and that is more likely the reason of the error.

hope this help