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

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
tonyoyo
Forum Newbie
Posts: 5
Joined: Thu Oct 28, 2010 11:59 am

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

Post 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.
tonyoyo
Forum Newbie
Posts: 5
Joined: Thu Oct 28, 2010 11:59 am

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

Post by tonyoyo »

Literally no-one knows? That's depressing...
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

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

Post 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.
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

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

Post 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
Post Reply