"String doesn't match number of bind variables"

ahux · March 10, 2010

Hey currently working on a form using Ajax, it is getting the results fine but when adding to the database I get the following error

"mysqli_stmt::bind_param() [function.mysqli-stmt-bind-param]: Number of elements in type definition string doesn't match number of bind variables" in leaveComment.php

This is index.php

<?php

if(isset($_POST['submit'])) {
$post = array();

foreach($_POST as $key=>$value) {
	if(isset($_POST[$key]) && strlen(trim($value)) >= 2) {
		$post[$key] = $value;
	}
	else $error = true;


} // end foreach

if(!$error) {
	require 'leaveComment.php';
}
}

?>
<!DOCTYPE html>

<html lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"> 
<title>ajaxComments</title>
<style type="text/css" media="screen">
body {
	font-family: helvetica, arial;
	line-height: 1.4em;
}
#container {
	width: 700px;
	margin: 10px auto 150px;
	color: #454545;
}

ul {
	margin: 0;
	padding: 0;
	list-style: none;
}

h3 {
	margin: 0;
	padding: 0;
	color: black;
}

h2 {
	color: #292929;
	font-size: 40px;
	margin-top: 40px;
}

p {
	font-size: 14px;
}

#addComment {
	margin-top: 40px;
	position: relative;
}

input, textarea {
	padding: .4em;
}

#comments li {
	border-top: 1px solid white;
	border-bottom: 1px solid #bcbbbb;
	padding: 20px 0 14px;
}

#comments li:last-child {
	border-bottom: none;
}
#comments li:first-child {
	border-top: none;
	padding-top: 0;
}

#comments, #addComment {
	background: #e3e3e3;
	border: 1px solid #bcbbbb;
	padding: 2em;
	-moz-border-radius: 2px;
	-webkit-border-radius: 2px;
}

input[type=text] {
	width: 70%;
}
textarea {
	width: 100%;
}

.error {font-style: italic; color: red;}

.overlay {
	width: 100%;
	height: 100%;
	background: black url(loader.gif) no-repeat 50% 50%;
	position: absolute;
	left: 0;
	top: 0;
	display: none;
	opacity: .9;
}	


</style>
</head>
<body>

<div id="container">

	<h2>ajaxComments</h2>

	<ul id="comments"> <?php require 'getItemsFromDatabase.php'; ?> </ul>

	<h2 id="leaveAComment">Leave a Comment</h2>		
	<div id="addComment">
		<form action="index.php" method="post">
			<p><input type="text" id="name" name="name" value="<?php echo $_POST['name'];?>" />

			<p><textarea name="comment" id="comment" rows="8" cols="40"><?php echo $_POST['comment'];?></textarea>

			<p><input type="submit" name="submit" id="submit" value="Post Comment"></p>
			<?php if(isset($error)) echo "<p class='error'>Please fill out each field correctly.</p>"; ?>
		</form>
	</div>

</div> <!-- end container-->

<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/jquery.min.js" type="text/javascript" charset="utf-8"></script>	

<script type="text/javascript">

$('#submit').click(function() {

var name = $('#name').val().replace(/[^\w\d ]+/gi, ''),

	comment = $('#comment').val();

if(name.length < 2 || comment.length < 4 ) {	
	if($('.error').length === 0) {
		$('form').append('<p class="error">Please fill out each field.</p>');
	}
	return false;
}

$.post(
	'leaveComment.php', {

	'name' : name, 

	'comment' : comment 
	}, 

	function(r) {			
		$('.error').fadeOut(200);

		$('<div class="overlay"></div>')
			.appendTo('#addComment')
   	        .fadeIn(1000, function() {
		       $('#comments')
		  	       .append('<li>' + r + '</li>')
		  		       .children(':last')
		  			   .height($('#comments li:last').height())
		  			   .hide()
		  			   .slideDown(800, function() {	
					       var bodyHeight = $('html').height();
		  				   $('#addComment').fadeOut(1000, function() {
						       $('html').height(bodyHeight);
							   $('h2#leaveAComment').fadeOut(200, function(){$(this).text('Thank you for your comment!').fadeIn(200)});
						   });
					   });			
   		  });
	}

); // end post

return false; // disable submit click

});

</script>
     
</body>
</html>

leavecomment.php

<?php

require 'config.php';

if(IS_AJAX) {
$post['name'] = $_POST['name']; 
$post['comment'] = $_POST['comment'];
} 


$mysqli = new mysqli($server, $username, $password, $db) or die('There was a problem connecting');

if($stmt = $mysqli->prepare("INSERT INTO project_review VALUES(NULL,?,?)")) {

$stmt->bind_param('sss', $post['name'],  $post['comment']);

if(!$stmt->execute()) die($mysqli->error);
$stmt->close();

if(IS_AJAX) {
	echo "<h3>" .$post['name'] . "</h3>";
	echo "<p>" . $post['comment'] . "</p>";
}


}

else echo 'ERROR!';

In the MySQL database, I have 3 column's id, name and comment

I have gone over it several times to see if I can spot the error, but still no luck.

ahux

Adam · March 10, 2010

Edit:

You're trying to bind 3 variables when there's only 2 question mark params.

aeroswat · March 10, 2010

Try removing this NULL, in this statement

$mysqli->prepare("INSERT INTO project_review VALUES(NULL,?,?)")

would be

$mysqli->prepare("INSERT INTO project_review VALUES(?,?)")

From what I see in the doc files this is the proper way to do this.

ahux · March 10, 2010

Thanks for the respons aeroswat, I've made the change but now when I click the submit button it jus comes up with an saying "ERROR" (which is my error message for when it doesnt add to the database.

ahux

Adam · March 10, 2010

You need to leave the NULL in there, or you'll be inserting (or trying) into the first two fields.

I modified my original post so you may not have seen it. You're trying to bind 3 variables to the prepared statement, when you only have 2 parameters to bind to.

aeroswat · March 10, 2010

sorry I see now. I think you need that NULL but you need to add an extra field to account for that NULL in your bind params. Looking for a solution for you real fast

aeroswat · March 10, 2010

From what I'm seeing you need to leave out the NULL but you have to specify where the VALUES are going in the SQL statement. Like such

"INSERT INTO tbl (col2, col3) VALUES (?, ?)"

Adam · March 10, 2010

Edit: Bugger! Not doing great today.

Try:

$stmt->bind_param('ss', $post['name'],  $post['comment']);

aeroswat · March 10, 2010

From what I'm seeing you need to leave out the NULL but you have to specify where the VALUES are going in the SQL statement. Like such

"INSERT INTO tbl (col2, col3) VALUES (?, ?)"

That won't change the fact he's binding 3 variables/strings:
$stmt->bind_param('sss', $post['name'],  $post['comment']);
To only 2 parameters:

INSERT INTO project_review VALUES(NULL,?,?)

Ya sorry. So it's a mix of both of our solutions

Remove the extra s. You only need 2 s' in your type string.

ahux · March 10, 2010

Thank you so much for your help guys, works a treat now! :-D

ahux

Sign In

"String doesn't match number of bind variables"

Recommended Posts

ahux

Link to comment

Share on other sites

Adam

Link to comment

Share on other sites

aeroswat

Link to comment

Share on other sites

ahux

Link to comment

Share on other sites

Adam

Link to comment

Share on other sites

aeroswat

Link to comment

Share on other sites

aeroswat

Link to comment

Share on other sites

Adam

Link to comment

Share on other sites

aeroswat

Link to comment

Share on other sites

ahux

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information