Jump to content
mires_0

save data from an array to database

Recommended Posts

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

 

 

 

Share this post


Link to post
Share on other sites
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      ]);
}

 

  • Great Answer 1

Share this post


Link to post
Share on other sites

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]);

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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);
}

  • Like 1

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

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.