Page 1 of 1

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

Posted: Thu Oct 28, 2010 12:02 pm
by tonyoyo
I'm sure there are other posts about this, but I can't figure why the first INSERT works and the second INSERT gives me the stupid error... :?

Code: Select all

	if ( !$isSecondHandItem )
	{
		$sql = "INSERT INTO
				Catalogue
				(
				Title,
				Author,
				Price,
				Publisher,
				Description,
				Format,
				Available,
				DisplayOnIndex,
				Category,
				Image,
				SubCategory,
				offer,
				Weight 
				)
			VALUES
				( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )";
	}
	else
	{
		$sql = "INSERT INTO
				secondhandBooks
				(
				bookTitle,
				bookAuthor,
				condition,
				price,
				Available,
				Weight,
				Publisher,
				Description,
				Format 
				)
			VALUES
				( ?, ?, ?, ?, ?, ?, ?, ?, ? )";
	}

    // fetch the data from the DB
    if ( ! $stmt = $db->prepare($sql))
    {
        $feedback = $stmt->error;
    }
    else
    {
        if ( !$isSecondHandItem )
	{
		$stmt->bind_param('ssissiisiiiii', $fTitle, $fAuthor, $fPrice, $fPublisher, $fDescription, $fFormat, $fAvailable, $fNewAddition, $fCategory, $fImage, $fSubCategory, $fFreeDelivery, $fWeight);
	}
	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();
		}
    }

    $db->close();
Please can someone help me with this? It makes no sense. :banghead:

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

Posted: Thu Oct 28, 2010 12:07 pm
by s.dot
What stupid error?

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

Posted: Thu Oct 28, 2010 12:13 pm
by tonyoyo
Thank you thank you thank you!

OK, I'll try and explain better:

"Number of variables doesn't match number of parameters in prepared statement"

When the page runs (form is submitted) it determines whether the item is second-hand or not. If it is a new item it runs the first SQL statement, binds the parameters and executes perfectly. Data goes in the DB and everything. When the item is second-hand it runs the other SQL statement and throws up the above error message about variables and parameters.

I've checked the following things: :idea:

- Number of variables (all exist and all have data in them)
- Number of columns (all exist and there are no others; except for the auto-inc id column)
- Tried it on 3 different DBs on 3 different servers; same result every time, first SQL runs fine and second SQL fails. :crazy:

Any ideas?

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

Posted: Thu Oct 28, 2010 3:12 pm
by mikosiko
try this...

In your second Insert one of the field names is "condition" which is a mysql reserved word
http://dev.mysql.com/doc/refman/5.1/en/ ... words.html // look here

to solve the issue surround that field name (better all of them) with backticks .. in this way

Code: Select all

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

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

Posted: Fri Oct 29, 2010 3:20 am
by tonyoyo
Thanks mikosiko! You were spot-on!

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);
And even with the ticks it still gives me the same error as before, first part runs fine second part (above) falls over.