scampbell70 Posted February 16, 2015 Share Posted February 16, 2015 I am brand new to php and MySQL. I am working on a small project and I am getting this error and I cannot find the reason why. Can someone please help. The error is Warning: mysqli_stmt::bind_result(): Number of bind variables doesn't match number of fields in prepared statement in C:\xampp\htdocs\test\records.php on line 134 <?php /* Allows the user to both create new records and edit existing records */ // connect to the database include("connect-db.php"); // creates the new/edit record form // since this form is used multiple times in this file, I have made it a function that is easily reusable function renderForm($first = '', $middle = '', $last = '', $ClientID = '', $Diagnosis = '', $Gender = '', $LevelCare = '', $Counselor = '', $error = '', $id = '') { ?> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd"> <html> <head> <title> <?php if ($id != '') { echo "Edit Record"; } else { echo "New Record"; } ?> </title> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/> </head> <body> <h1><?php if ($id != '') { echo "Edit Record"; } else { echo "New Record"; } ?></h1> <?php if ($error != '') { echo "<div style='padding:4px; border:1px solid red; color:red'>" . $error . "</div>"; } ?> <form action="" method="post"> <div> <?php if ($id != '') { ?> <input type="hidden" name="id" value="<?php echo $id; ?>" /> <p>id: <?php echo $id; ?></p> <?php } ?> <strong>First Name: *</strong> <input type="text" name="FirstName" value="<?php echo $first; ?>"/><br/> <strong>Middle Name: </strong> <input type="text" name="MiddleName" value="<?php echo $middle; ?>"/> <strong>Last Name: *</strong> <input type="text" name="LastName" value="<?php echo $last; ?>"/> <strong>Client ID: *</strong> <input type="text" name="ClientID" value="<?php echo $ClientID; ?>"/> <strong>Diagnosis: *</strong> <input type="text" name="Diagnosis" value="<?php echo $Diagnosis; ?>"/> <strong>Gender: *</strong> <input type="text" name="Gender" value="<?php echo $Gender; ?>"/> <strong>Level <span class="posthilit">of</span> Care: *</strong> <input type="text" name="LevelCare" value="<?php echo $LevelCare; ?>"/> <strong>Counselor: *</strong> <input type="text" name="Counselor" value="<?php echo $Counselor; ?>"/> <p>* required</p> <input type="submit" name="submit" value="Submit" /> </div> </form> </body> </html> <?php } /* EDIT RECORD */ // if the 'id' variable is set in the URL, we know that we need to edit a record if (isset($_GET['id'])) { // if the form's submit button is clicked, we need to process the form if (isset($_POST['submit'])) { // make sure the 'id' in the URL is valid if (is_numeric($_POST['id'])) { // get <span class="posthilit">variables</span> from the URL/form $id = $_POST['id']; $FirstName = htmlentities($_POST['FirstName'], ENT_QUOTES); $MiddleName = htmlentities($_POST['MiddleName'], ENT_QUOTES); $LastName = htmlentities($_POST['LastName'], ENT_QUOTES); $ClientID = htmlentities($_POST['ClientID'], ENT_QUOTES); $Diagnosis = htmlentities($_POST['Diagnosis'], ENT_QUOTES); $Gender = htmlentities($_POST['Gender'], ENT_QUOTES); $LevelCare = htmlentities($_POST['LevelCare'], ENT_QUOTES); $Counselor = htmlentities($_POST['Counselor'], ENT_QUOTES); // check that FirstName, LastName and ClientID are not empty if ($FirstName == '' || $MiddleName == '' || $LastName == '' || $ClientID == '' || $Diagnosis == '' || $Gender == '' || $LevelCare == '' || $Counselor == '') { // if they are empty, show an error message and display the form $error = 'ERROR: Please fill in all required fields!'; renderForm($FirstName, $MiddleName, $LastName, $ClientID, $Diagnosis, $Gender, $LevelCare, $Counselor, $error, $id); } else { // if everything is fine, update the record in the database if ($stmt = $mysqli->prepare("UPDATE clients SET FirstName = ?, MiddleName = ?, LastName = ?, ClientID = ?, Diagnosis = ?, Gender = ?, LevelCare = ?, Counselor = ? WHERE id=?")) { $stmt->bind_param("ssssssssi", $FirstName, $MiddleName, $LastName, $ClientID, $Diagnosis, $Gender, $LevelCare, $Counselor, $id); $stmt->execute(); $stmt->close(); } // show an error message if the query has an error else { echo "ERROR: could not prepare SQL statement."; } // redirect the user once the form is updated header("Location: view-paginated.php"); } } // if the 'id' variable is not valid, show an error message else { echo "Error!"; } } // if the form hasn't been submitted yet, get the info from the database and show the form else { // make sure the 'id' value is valid if (is_numeric($_GET['id']) && $_GET['id'] > 0) { // get 'id' from URL $id = $_GET['id']; // get the record from the database if($stmt = $mysqli->prepare("SELECT * FROM clients WHERE id=?")) { $stmt->bind_param("i", $id); $stmt->execute(); $stmt->bind_result($id, $FirstName, $MiddleName, $LastName, $ClientID, $Diagnosis, $Gender, $LevelCare, $Counselor); $stmt->fetch(); // show the form renderForm($FirstName, $MiddleName, $LastName, $ClientID, $Diagnosis, $Gender, $LevelCare, $Counselor, NULL, $id); $stmt->close(); } // show an error if the query has an error else { echo "Error: could not prepare SQL statement"; } } // if the 'id' value is not valid, redirect the user back to the view.php page else { header("Location: view.php"); } } } /* NEW RECORD */ // if the 'id' variable is not set in the URL, we must be creating a new record else { // if the form's submit button is clicked, we need to process the form if (isset($_POST['submit'])) { // get the form data $FirstName = htmlentities($_POST['FirstName'], ENT_QUOTES); $MiddleName = htmlentities($_POST['MiddleName'], ENT_QUOTES); $LastName = htmlentities($_POST['LastName'], ENT_QUOTES); $ClientID = htmlentities($_POST['ClientID'], ENT_QUOTES); $Diagnosis = htmlentities($_POST['Diagnosis'], ENT_QUOTES); $Gender = htmlentities($_POST['Gender'], ENT_QUOTES); $LevelCare = htmlentities($_POST['LevelCare'], ENT_QUOTES); $Counselor = htmlentities($_POST['Counselor'], ENT_QUOTES); // check that FirstName and LastName are both not empty if ($FirstName == '' || $MiddleName == '' || $LastName == '' || $ClientID == '') { // if they are empty, show an error message and display the form $error = 'ERROR: Please fill in all required fields!'; renderForm($FirstName, $MiddleName, $LastName, $ClientID, $Diagnosis, $Gender, $LevelCare, $LevelCare, $Counselor, $error); } else { // insert the new record into the database if ($stmt = $mysqli->prepare("INSERT clients (FirstName, MiddleName, LastName, ClientID, Diagnosis, Gender, LevelCare, Counselor) VALUES (?, ?, ?, ?, ?, ?, ?, ?)")) { $stmt->bind_param("ssssssss", $FirstName, $MiddleName, $LastName, $ClientID, $Diagnosis, $Gender, $LevelCare, $Counselor); $stmt->execute(); $stmt->close(); } // show an error if the query has an error else { echo "ERROR: Could not prepare SQL statement."; } // redirec the user header("Location: view.php"); } } // if the form hasn't been submitted yet, show the form else { renderForm(); } } // close the mysqli connection $mysqli->close(); ?> Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted February 16, 2015 Share Posted February 16, 2015 the error is telling you what the problem is. the number of fields/columns you SELECT'ed in the query with the * doesn't match what you bound with the bind_result() statement. when you have reached the point of using prepared queries, it's best to list out each field/column you are selecting. this will also prevent problems should the order of the fields/columns in your database table get changed since you can see what exact fields/columns you are selecting and what order they will be in the fetched rows in the result set. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.