save data from an array to database

[mires_0]

Hi,

I'm trying to save data from an array to database but something is wrong. This is from i send data, as you see im using arrays [] because the user have the posibility to clone the row

<div class="box-body">
                 <div class="form-group">
                  <label class="col-sm-2">Student</label>
                  <div class="col-sm-10">                    
			<table id="table" border="1" bordercolor="#00acc1">
            
                          <thead>
                            <tr>
                              <th><p>Name</p></th>
                              <th><p>Mode</p></th>
                              <th><p>Sport</p></th>
                              <th>&nbsp;</th>
                            </tr>
                          </thead>

                          <tbody>
                         
                            <tr>
                              <td><select style="width:325px" name="Name[]" class="form-control">
                                      <option value="">Select</option>
                                        <?php
                                        $search = "SELECT * FROM prof";
                                        $data = $connect->prepare($search); 
                                        $data->execute();
                                        while($re=$data -> fetch(PDO::FETCH_ASSOC)){
                                          echo "<option value = '".$re['id']."'>";
                                          echo $re['n_prof'].' '.$re['ap_prof'];}
                                        ?>
                                  </select>
                             </td>
                                 <td>
                                <select class="form-control" name="mode[]" style="width:150px"  />
                                  <option value="">Select</option>
                                  <option value="Administrator">Administrator</option>
                                  <option value="Scholar">Scholar</option>
                                  <option value="External student">External student</option>
                                  <option value="Thesis">Thesis</option>
                                  <option value="Voluntary">Voluntary</option>
                                </select>
                              </td>
                           <td>
                                <select class="form-control" name="Sport[]" style="width:150px"  />
                                  <option value="">Select</option>
                                  <option value="Football">Football</option>
                                  <option value="Baseball">Baseball</option>
                                  <option value="Swimming">Swimming</option>
                                  <option value="Horse riding">Horse riding</option>
                                  <option value="basketball">basketball</option>
                                </select>
                              </td>
                              <td class="Delete">Delete</td>
                            </tr>

                         
                          </tbody>
                        </table>
                        <input type="button" id="add" value="+ add student"  class="btn btn-primary"/>


                  </div>
                </div>
              </div>

my db.table is like this

id  | name  |  mode  | sport  | idstudent_fk

And Im using an algorithm to read the array and every time can save every row, but im usind PDO and im having problems

try{

here i insert data in a table
here i got the last id from that table then ...

if($_POST['name']!="" and $_POST['mode']!="" and $_POST['sport']!=""){
							 	if(is_array($_POST['name'])){
									while(list($key, $name) = each($_POST['name']) 
							 		  and list($val,$mode) = each($_POST['mode'])
							 		  and list($id, $sport) = each($_POST['sport'])){
                             $sql = "INSERT INTO sports(id_studentfk, mode_stu, sport, id_projfk) 
											values(:value, :mode, :sport, :lastid)";
											$statement = $connect ->prepare($sql);
											$statement -> bindParam(':name', $name, PDO::PARAM_INT);
											$statement -> bindParam(':mode', $mode, PDO::PARAM_STR);
											$statement -> bindParam(':sport', $sport, PDO::PARAM_STR);
											$statement -> bindParam(':lastid', $lastid, PDO::PARAM_INT);
									    	$pdoExec = $statement -> execute();
							 		}
}//end if array
}//end if post
} catch (PDOException $e) {
						     print 'ERROR: '. $e->getMessage();
						     print '<br/>Data Not Inserted';
						}

the idea is creating a loop to read the array and every time can insert data. Im using PDO this time is not working, im geeting this error

ERROR: SQLSTATE[HY093]: Invalid parameter number: parameter was not defined
Data Not Inserted

I hope someone can help me to solve this

 

 

 

[Barand]

48 minutes ago, mires_0 said:

if($_POST['name']!="" and $_POST['mode']!="" and $_POST['sport']!=""){

I suspect a program failure at this line.

$_POST['name'] etc are arrays - you are trying to treat them as string values here.

I would

$lastId = 99999;            // or whatever

$sql = "INSERT INTO sports(id_studentfk, mode_stu, sport, id_projfk) 
               values(:value, :mode, :sport, :lastid)";
$stmt = $connect->prepare($sql);

foreach ($_POST['name'] as $k => $name) {
    $mode = $_POST['mode'][$k];
    $sport = $_POST['sport'][$k];
    
    if ( $name!='' && $mode!='' && $sport!='' )
      $stmt->execute( [ 'name' => $name,
                      'mode' => $mode,
                      'sport' => $sport,
                      'lastid' => $lastId      ]);
}

 

[mires_0]

Thank you so much Barand, your answer solve it, i just add the double dot

if($name!='' && $mode!='' && $sport!='')
      $stmt->execute([':name' => $name,
                      ':mode' => $mode,
                      ':sport' => $sport,
                      ':lastid' => $lastId]);

, 

[Barand]

The "double dot" in the execute array keys is optional.

[Psycho]

Something to consider, you can make the code much simpler if you can structure the input data in the same manner as you plan to use it. Instead of having fields called "name[]", "mode[]" and "sport[]" that are only associated by being in the same position - you can give them a more concrete structure that matches how you will use them for your query.

The first input fields could be named: "student[0][name]", "student[0][mode]" and "student[0][sport]"

The second input fields names: "student[1][name]", "student[1][mode]" and "student[1][sport]"

etc. . . . 

This, of course, requires a little more logic in the process of 'cloning' rows to create the input fields. But, with this structure, your processing code would go something like this

foreach($_POST['student'] as $studentRec)
{
    $stmt->execute($studentRec);
}