how to insert multiple rows in mysql using php?

Question

With my code, client must be able to enter 5 members details which has to be stored in the DB at a time.

It shows me this error:

Warning: mysqli_stmt::bind_param(): Number of variables doesn't match number of parameters in prepared statement in C:\xampp\htdocs\tutorial\index.php on line 278

          <?php 
           include "config.php";
           ?>
         <!DOCTYPE html>
            <html>
           <head>
            <title>Registration</title>

            <!-- Bootstrap CSS -->
             <link rel="stylesheet" 
          href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/css/bootstrap.min.css">

             <!-- jQuery library -->
              <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"> 
          </script>

          <!-- Bootstrap JS -->
         <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/js/bootstrap.min.js"></script>

          </head>
          <body>
          <style>
          .button {
         background-color: #4CAF50;
         border: none;
         color: white;
         padding: 15px 32px;
          text-align: center;
           text-decoration: none;
         display: inline-block;
             font-size: 16px;
         margin: 4px 2px;
          cursor: pointer;
         }

          #customers {
          font-family: Arial, Helvetica, sans-serif;
          border-collapse: collapse;
          width: 100%;
     }

     #customers td, #customers th {
     border: 1px solid #ddd;
     padding: 8px;
       }

       #customers tr:nth-child(even){background-color: #f2f2f2;}

        #customers tr:hover {background-color: #ddd;}

   #customers th {
   padding-top: 12px;
     padding-bottom: 12px;
    text-align: left;
  background-color: #4CAF50;
  color: white;
  }
    </style>

    <div class='container'>
      <div class='row'>

        <div class='col-md-6' >

       <form method='post' action=''>

        <h1>Registration</h1>
        <?php 
        // Display Error message
        if(!empty($error_message)){
        ?>
        <div class="alert alert-danger">
          <strong>Error!</strong> <?= $error_message ?>
        </div>

        <?php
        }
        ?>

        <?php 
        // Display Success message
        if(!empty($success_message)){
        ?>
        <div class="alert alert-success">
          <strong>Success!</strong> <?= $success_message ?>
        </div>

        <?php
        }
        ?>
       <center><table id="customers" class="display" style="width:100%">
       <thead>
        <tr>
            <th>S.No</th>
            <th>Name</th>
            <th>Mobile</th>
            <th>email</th>
            <th>Member/Non-Member</th>
        </tr>
       </thead>
        <tbody>
          <tr>
            <td>1</td>
            <td><div class="form-group">
          <label for="fname">Name</label>
          <input type="text" class="form-control" name="fname" id="fname" required="required" 
          maxlength="80">
        </div></td>
            <td><div class="form-group">
          <label for="mob">Mobile</label>
          <input type="text" class="form-control" name="mob" id="mob" required="required" 
         maxlength="80">
        </div></td>
        <td><div class="form-group">
          <label for="email">Email address:</label>
          <input type="email" class="form-control" name="email" id="email" required="required" 
           maxlength="80">
        </div></td>
            <td><select size="1" id="row-1-office" name="mem">
                <option value="Member" selected="selected">
                    Member
                </option>
                <option value="Non-Member">
                    Non-Member
                </option>
                
            </select></td>
        </tr>

        <tr>
            <td>2</td>
            <td><div class="form-group">
          <label for="fname">Name</label>
          <input type="text" class="form-control" name="fname" id="fname" required="required" 
        maxlength="80">
        </div></td>
            <td><div class="form-group">
          <label for="mob">Mobile</label>
          <input type="text" class="form-control" name="mob" id="mob" required="required" 
        maxlength="80">
        </div></td>
        <td><div class="form-group">
          <label for="email">Email address:</label>
          <input type="email" class="form-control" name="email" id="email" required="required" 
         maxlength="80">
        </div></td>
            <td><select size="1" id="row-1-office" name="mem">
                <option value="Member" selected="selected">
                    Member
                </option>
                <option value="Non-Member">
                    Non-Member
                </option>
                
            </select></td>
        </tr>

        <tr>
            <td>3</td>
            <td><div class="form-group">
          <label for="fname">Name</label>
          <input type="text" class="form-control" name="fname" id="fname" required="required" 
         maxlength="80">
        </div></td>
            <td><div class="form-group">
          <label for="mob">Mobile</label>
          <input type="text" class="form-control" name="mob" id="mob" required="required" 
        maxlength="80">
        </div></td>
        <td><div class="form-group">
          <label for="email">Email address:</label>
          <input type="email" class="form-control" name="email" id="email" required="required" 
        maxlength="80">
        </div></td>
            <td><select size="1" id="row-1-office" name="mem">
                <option value="Member" selected="selected">
                    Member
                </option>
                <option value="Non-Member">
                    Non-Member
                </option>
                
            </select></td>
        </tr>
        
        <tr>
            <td>4</td>
            <td><div class="form-group">
          <label for="fname">Name</label>
          <input type="text" class="form-control" name="fname" id="fname" required="required" 
        maxlength="80">
        </div></td>
            <td><div class="form-group">
          <label for="mob">Mobile</label>
          <input type="text" class="form-control" name="mob" id="mob" required="required" 
        maxlength="80">
        </div></td>
        <td><div class="form-group">
          <label for="email">Email address:</label>
          <input type="email" class="form-control" name="email" id="email" required="required" 
        maxlength="80">
        </div></td>
            <td><select size="1" id="row-1-office" name="mem">
                <option value="Member" selected="selected">
                    Member
                </option>
                <option value="Non-Member">
                    Non-Member
                </option>
                
            </select></td>
        </tr>

        <tr>
            <td>5</td>
            <td><div class="form-group">
          <label for="fname">Name</label>
          <input type="text" class="form-control" name="fname" id="fname" required="required" 
       maxlength="80">
        </div></td>
            <td><div class="form-group">
          <label for="mob">Mobile</label>
          <input type="text" class="form-control" name="mob" id="mob" required="required" 
       maxlength="80">
        </div></td>
        <td><div class="form-group">
          <label for="email">Email address:</label>
          <input type="email" class="form-control" name="email" id="email" required="required" 
        maxlength="80">
        </div></td>
            <td><select size="1" id="row-1-office" name="mem">
                <option value="Member" selected="selected">
                    Member
                </option>
                <option value="Non-Member">
                    Non-Member
                </option>
                
            </select></td>
        </tr>

 </table>
        <button type="submit" name="btnsignup" class="btn btn-default">Submit</button>
      </form>
    </div>

 </div>
 </div>
 </body>
 <?php 
 $error_message = "";$success_message = "";

 if(isset($_POST['btnsignup'])){
 $fname = trim($_POST['fname']);
 $mob = trim($_POST['mob']);
 $email = trim($_POST['email']);
 $mem = trim($_POST['mem']);

 $isValid = true;

 if($fname == '' || $mob == '' || $email == '' || $mem == '' ){
 $isValid = false;
 $error_message = "Please fill all fields.";
 }



 if ($isValid && !filter_var($email, FILTER_VALIDATE_EMAIL)) {
 $isValid = false;
 $error_message = "Invalid Email-ID.";
 }

 if($isValid){

 // Check if Email-ID already exists
 $stmt = $con->prepare("SELECT * FROM users WHERE email = ?");
 $stmt->bind_param("s", $email);
 $stmt->execute();
 $result = $stmt->get_result();
 $stmt->close();
 if($result->num_rows > 0){
   $isValid = false;
   $error_message = "Email-ID is already existed.";
 }

 }

 if($isValid){
 $insertSQL = "INSERT INTO users(fname,mob,email,mem ) values(?,?,?,?),(?,?,?,?),(?,?,?,?),(?,?,?,?), 
 (?,?,?,?)";
 $stmt = $con->prepare($insertSQL);
 $stmt->bind_param("ssss",$fname,$mob,$email,$mem);
 $stmt->execute();
 $stmt->close();

 $success_message = "Account created successfully.";
}
}
?>
</html>

Does this answer your question? [PDO Prepared Inserts multiple rows in single query](https://stackoverflow.com/questions/1176352/pdo-prepared-inserts-multiple-rows-in-single-query) — Al-Amin, Nov 03 '20 at 06:49
Does this answer your question? [mysqli\_fetch\_assoc() expects parameter / Call to a member function bind\_param() errors. How to get the actual mysql error and fix it?](https://stackoverflow.com/questions/22662488/mysqli-fetch-assoc-expects-parameter-call-to-a-member-function-bind-param) — Dharman, Nov 03 '20 at 11:10

score -1 · Answer 1 · answered Nov 03 '20 at 06:58

-1

On the line you bind values

$stmt->bind_param("ssss",$fname,$mob,$email,$mem);

Therea are 5 parameters. But your query already has more, those are not matching. If you want PHP to prepare statement with 20 parameters, you have to bind 20 values.

There isn't any shortcut to bind values N times.

answered Nov 03 '20 at 06:58

YigitOzdemir

95
1
13

Thanks, after binding 20 parameters the error has been cleared but the entries aren't stored on the DB – vennila Nov 03 '20 at 07:36
No, there is no error but when I click submit it doesn't store the entries in database – vennila Nov 03 '20 at 10:06
can you please add a try-catch blog to staement execution? try { someCode(); } } catch (PDOException $e) { echo $e->message; } – YigitOzdemir Nov 04 '20 at 09:03

how to insert multiple rows in mysql using php?

1 Answers1