PHP - Need Some Help With Dynamic Form

Hi guys,

I really hope this will make sense.

I am creating a dynamic field on a button click for Pickup Location. That works fine and submitting the form to the database works fine. However, instead of one entry, each time I submit the form with multiple Pickup Locations, it creates multiple separate database entries.

Here is the PHP for submitting:

if(isset($_POST['new']) && $_POST['new']==1){
$pickups = '';
foreach($_POST['pickups'] as $cnt => $pickups)
    $pickups .= ',' .$pickups;
	$locations = count($_POST["pickups"]);
	
	if ($locations > 0) {
	    for ($i=0; $i < $locations; $i++) { 
		if (trim($_POST['pickups'] != '')) {
			
    $name = mysqli_real_escape_string($con, $_POST['name']);
    $price = mysqli_real_escape_string($con, $_POST['price']);
    //$origin = $_POST['origin'];
    $pickups = $_POST["pickups"][$i];
    $destination = mysqli_real_escape_string($con, $_POST['destination']);
    $dep_date = mysqli_real_escape_string($con, $_POST['dep_date']);
    $ret_date = mysqli_real_escape_string($con, $_POST['ret_date']);
    $fleet_number = mysqli_real_escape_string($con, $_POST['fleet_number']);
    $driver = mysqli_real_escape_string($con, $_POST['driver']);
    $itinerary = mysqli_real_escape_string($con, $_POST['itinerary']);
    $submittedby = mysqli_real_escape_string($con, $_SESSION["username"]);
    $trn_date = mysqli_real_escape_string($con, date("Y-m-d H:i:s"));
    

    $query="insert into tours (`name`, `price`, `pickups`, `destination`, `dep_date`, `ret_date`, `fleet_number`, `driver`, `itinerary`, `submittedby`, `trn_date`)values ('$name', '$price', '$pickups', '$destination', '$dep_date', '$ret_date', '$fleet_number', '$driver', '$itinerary', '$submittedby',  '$trn_date')";
    mysqli_query($con,$query)
    
    or die(mysqli_error($con));
    if(mysqli_affected_rows($con)== 1 ){
    $message = '<i class="fa fa-check"></i> - Record Inserted Successfully';
    }
}
}
}
}

Here is the HTML form:

                          <form role="form" method="post" name="add_tour" id="add_tour" action"">
                                <input type="hidden" name="new" value="1" />
                            <div class="modal-body">
                                
                          <div class="row form-group">
                              <div class="col-6">
                                <div class="form-group"><label for="name" class=" form-control-label">Name</label><input type="text" id="name" name="name" placeholder="Tour Name" class="form-control">
                                </div>
                                </div>
                              <div class="col-6">
                                <div class="form-group"><label for="price" class=" form-control-label">Price</label><input type="text" id="price" name="price" placeholder="0.00" class="form-control">
                                </div>
                                </div>
                                </div>
                                
                                <div class="row form-group">
                                    <div class="col-6">
                        <div class="form-group origin" id="pickupsfield"><label for="pickups" class=" form-control-label">Pickup Location</label><input type="text" id="pickups" name="pickups[]" placeholder="Start Typing..." class="form-control"></div>
    <button type="button" class="btn btn-success add-field" id="add" name="add">Add New Location &nbsp; 
      <span style="font-size:16px; font-weight:bold;">+ </span>
    </button>
                        </div>

                        <div class="col-6">
                        <div class="form-group"><label for="destination" class=" form-control-label">Destination</label><input type="text" id="destination" name="destination" placeholder="Start Typing..." class="form-control"></div>
                        </div>
                        </div>
                          <div class="row form-group">
                              <div class="col-6">
                        <div class="form-group"><label for="dep_date" class=" form-control-label">Departure Date</label><input type="date" id="dep_date" name="dep_date" placeholder="" class="form-control"></div>
                        </div>
                              <div class="col-6">
                           <div class="form-group"><label for="ret_date" class=" form-control-label">Return Date</label><input type="date" id="ret_date" name="ret_date" placeholder="" class="form-control"></div>
                          </div>
                          </div>
                          <div class="row form-group">
                              <div class="col-6">
                        <div class="form-group"><label for="fleet_number" class=" form-control-label">Fleet Number</label>
                           <select class="form-control" id="fleet_number" name="fleet_number">
                               <option value="Select">== Select Fleet Number ==</option>
<?php
$sql = "SELECT fleet_number FROM fleet";
$result = $con->query($sql);
        while(list($fleet_number) = mysqli_fetch_row($result)){
          $option = '<option value="'.$fleet_number.'">'.$fleet_number.'</option>';
          echo ($option);
        }
          ?>
                           </select>
                        </div>
                        </div>
                              <div class="col-6">
<?php

    ?>
                           <div class="form-group"><label for="driver" class=" form-control-label">Driver</label>
                           <select class="form-control" id="driver" name="driver">
                               <option value="Select">== Select Driver ==</option>
<?php
$sql = "SELECT name FROM drivers";
$result = $con->query($sql);
        while(list($driver) = mysqli_fetch_row($result)){
          $option = '<option value="'.$driver.'">'.$driver.'</option>';
          echo ($option);
        }
          ?>
                           </select>
                           </div>
                          </div>
                          </div>
                            <div class="form-group"><label for="itinerary" class=" form-control-label">Itinerary</label>
                            <textarea class="form-control" id="itinerary" name="itinerary"></textarea>
                            </div>

                            <div class="modal-footer">
                                <button type="reset" class="btn btn-warning">Clear Form</button>
                                <button type="submit" name="submit" id="submit" class="btn btn-primary">Confirm</button>
                            </div>
                            </form>

And the Javascript for adding the new fields:

<script>
  $(document).ready(function(){

    var i = 1;

    $("#add").click(function(){
      i++;
      $('#pickupsfield').append('<div id="row'+i+'"><input type="text" name="pickups[]" placeholder="Enter pickup" class="form-control"/></div><div><button type="button" name="remove" id="'+i+'" class="btn btn-danger btn_remove">X</button></div>');  
    });

    $(document).on('click', '.btn_remove', function(){  
      var button_id = $(this).attr("id");   
      $('#row'+button_id+'').remove();  
   });

    $("#submit").on('click',function(){
      var formdata = $("#add_tour").serialize();
      $.ajax({
        url   :"",
        type  :"POST",
        data  :formdata,
        cache :false,
        success:function(result){
          alert(result);
          $("#add_tour")[0].reset();
        }
      });
    });
  });
</script>

Anyone have any idea where I am going wrong?

Before you say it, Yes, I know, Use Prepared statements 😷

In brief, I'm attempting to capture the form data from a dynamic form to a mysql database.

From he

To He

The post data looks like this from the form -
DATA SUBMITTED:

Array(

...
[petlist] => [{"Pet Name":"Hal","Pet Type":"Dog","Breed":"Lab","DOB":"02/10/2013","Gender":"Male","Special Needs":"No"},
{"Pet Name":"Bill","Pet Type":"Dog","Breed":"Golden","DOB":"03/20/2015","Gender":"Male","Special Needs":"No"}]
...

)

PHP CODE:

...
$_POST['petlist'];
...

As a non-php or mysql developer, I'm learning on the fly.  This is a section of the php file that I was using to post the form data to mySQL.  This worked great up to the point I added the dynamic form widget.

<?php
// This function will run within each post array including multi-dimensional arrays
function ExtendedAddslash(&$params)
{
        foreach ($params as &$var) {
            // check if $var is an array. If yes, it will start another ExtendedAddslash() function to loop to each key inside.
            is_array($var) ? ExtendedAddslash($var) : $var=addslashes($var);
            unset($var);
        }
}

// Initialize ExtendedAddslash() function for every $_POST variable
ExtendedAddslash($_POST);     

$submission_id = $_POST['submission_id'];
$formID =$_POST['formID'];
$ip =$_POST['ip'];
$fname =$_POST['fname'];
$lname =$_POST['lname'];
$spousename =$_POST['spousename'];
$address =$_POST['address'][0]." ".$_POST['address'][1]." ".$_POST['address'][2]." ".$_POST['address'][3]." ".$_POST['address'][4]." ".$_POST['address'][5];
... 

$db_host = 'localhost';
$db_username = 'xxxxx';
$db_password = 'xxxxx';
$db_name = 'xxxxx';

mysql_connect( $db_host, $db_username, $db_password) or die(mysql_error());
mysql_select_db($db_name);

// search submission ID

$query = "SELECT * FROM `tableName` WHERE `submission_id` = '$submission_id'";
$sqlsearch = mysql_query($query);
$resultcount = mysql_numrows($sqlsearch);

if ($resultcount > 0) {
 
    mysql_query("UPDATE `tableName` SET
                                `fname` = '$fname',
                                `lname` = '$lname',
                                `spousename` = '$spousename',
                                `address` = '$address',
                                 
                                 WHERE `submission_id` = '$submission_id'")
     or die(mysql_error());
   
} else {

    mysql_query("INSERT INTO `tableName` (submission_id, formID, IP, fname, lname, spousename, address)
                               VALUES ('$submission_id', '$formID', '$ip', '$fname', '$lname', '$spousename', '$address') ")
    or die(mysql_error()); 

}
?>

It has been suggested that I explore using the PHP Explode() function.  It's possible that may work, but can't get my head around how to apply the function to the PETLIST Array.

Looking for suggestions or direction to find a solution for this challenge.  Looking forward to any replies.

<?php

include '../../config/config.inc.php';

if(is_ajax()){
# Checks if action value exists
  if(isset($_POST["action"]) && !empty($_POST["action"])){
    $action = $_POST["action"];
# Switch case for value of action
    switch($action){
			case "insert": datatable_insert_function();
			break;
			case "update": datatable_update_function();
			break;
			case "delete": datatable_delete_function();
			break;
    }
  }
}

# Function to check if the request is an AJAX request
function is_ajax(){ return isset($_SERVER['HTTP_X_REQUESTED_WITH']) && strtolower($_SERVER['HTTP_X_REQUESTED_WITH']) == 'xmlhttprequest'; }

function datatable_insert_function(){
}

function datatable_update_function(){
}

function datatable_delete_function(){
}

?>

	# Test _POST values
	$_POST['UserID'] = '2';
	$_POST['WorkID'] = '22';
	$_POST['Status'] = '1';
	$_POST['Code'] = '1';
	$_POST['Title'] = '1';


	$columns = array();
	foreach(array_keys($_POST) as $name){
		# Exclude Action and ID
		if($name == 'Action' || $name == 'ID' || $name == 'submit' ){ continue; }
		$columns[] = $name;
	}

	print_r($columns);

	echo "<br>";

	$data = array_fill_keys($columns, 'NULL');

	print_r($data);

	foreach($data as $key => $value){
		$data[$key] = empty($_POST[$key]) ? 'NULL' : "'".mysql_real_escape_string($_POST[$key])."'";
	}

	echo "<br>";

	print_r($data);

	$insert = mysql_query('INSERT INTO datatable (ID, '.implode(', ',$columns).')VALUES (null, '.implode(',',$data).')') or die(mysql_error());