Jump to content

Warning: mysqli_stmt::bind_result(): Number of bind variables doesn't match number of fields in prepared statement

scampbell70

By scampbell70
in PHP Coding Help

 Share

Recommended Posts

scampbell70 Newbie

scampbell70

Posted
Posted

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();
     ?>
mac_gyver Prolific Member

mac_gyver

Posted
Posted

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.

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.