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 »

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:
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

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

Post by s.dot »

What stupid error?
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
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 »

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?
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 »

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 ( ?, ?, ?, ?, ?, ?, ?, ?, ? )";
        }
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 »

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