How to insert multiple records to mysqli from html form based on number of events and date

Question

I am only starting with PHP so please bear with me. I have a seemingly easy assignment which I've stuck with. I have tried to search for some help but couldn't really find what I need exactly. And what I need to do, is to insert multiple records to mysqli table from an HTML form based on selected number of records and selected date.

It is a sort of recurring event but without any calendars or server side events. Just inserting new records into an existing table.

I have created the form which looks like:

<form action="" method="post" id="form_pay" name="form_pay" >           
<input type="text" name="pay_client_id[]" value="<?php echo $client_id; ?>" />
<input type="text" name="pay_amount[]"  />
<input type="text" name="pay_number[]" />
<input type="text" name="pay_due_date[]" />

<input type="submit" id="submit_pay" name="submit_pay" value="Create Table" />  
</form> 

and DB Table called pay_schedules:

=====================================================  
pay_id | client_id |  amount |  due_date 
=====================================================  

and finally beginning of php:

if(isset($_POST["submit_pay"])){    

$client_id      = $_POST['pay_client_id']; 
$amount     = $_POST['pay_amount']; 
$due_date   = date('Y-m-d',strtotime($_POST['pay_due_date'])); 
$pay_number     = $_POST['pay_number'];                                                             


//connection details
$servername = "localhost";
$username = "my_user";
$password = "my_password";
$dbname = "my_db";

$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } 

// insert into table
$sql =  "INSERT INTO pay_schedules 
            (client_id, amount, due_date) 
                VALUES ( '$client_id', '$amount', '$due_date' ) ";  

// messages
if ($conn->query($sql) === TRUE) { } else { $conn->error; }
$conn->close(); 

}

Obviously PHP is not complete and I know I have to wrap the query in a loop but I am facing following problems:

• how to insert number of records based on pay_number field (e.g. 4 records, 10 records, 24 records ect)
• how to create foreach loop that increase month +1 with each inserted record up to the number of selected pay_number field
• all inserted records have to start with due_date and have the same day but consecutive month (e.g. 2019-11-20, 2019-12-20, 2020-01-20), but how to avoid a problem with last day of month if 28th, 29th 30th or 31st is selected for months without those days?

so what I actually want to achieve is a table that looks like below based on following assumptions:

• pay_client_id: 1001
• pay_number: 5
• pay_amount: 150.25
• pay_due_date: 2019-10-31
• pay_id: auto increment PK

final table:

=====================================================  
pay_id  | client_id |  amount   |  due_date 
=====================================================  
1   | 1001      | 150.00    | 2019-10-31
2   | 1001      | 150.00    | 2019-11-30    
3   | 1001      | 150.00    | 2019-12-31
4   | 1001      | 150.00    | 2020-01-31
5   | 1001      | 150.00    | 2020-02-29

I hope I explained my problem so if anyone could help me here I would greatly appreciate it.

Answer 1

This could be a possible solution for your issues:

// change this to DateTime
$due_date   = new DateTime($_POST['pay_due_date']);
// ... other vars

$sql = '';
for($i = 0; $i < intval($pay_number); $i++) {
  $due_date_str = $due_date->format('Y-m-d');
  $sql .=  "INSERT INTO pay_schedules (client_id, amount, due_date) VALUES ( ?, ?, ?);";
  $stmt = $conn->prepare($sql);

  $stmt->bind_param("sss", $client_id, $amount, $due_date);
  $stmt->execute();
  $stmt->close();

  $due_date = add($due_date, 1);
}

This is the code for the (adapted) add() function (credits to Crend King in PHP: Adding months to a date, while not exceeding the last day of the month):

function add($date, $months) {    
  $start_day = $date->format('j');
  $date->modify("+{$months} month");
  $end_day = $date->format('j');

  if ($start_day != $end_day) {
    $date->modify('last day of last month');
  }

  return $date;
}

Please have a look at PHP mysqli::prepare in the documentation in order to avoid SQL injections by prepared statements.

EDIT: Added prepared statements, thanks @Shoyeb Sheikh for the reminder.

Answer 2

this is my final solution:

<form action="" method="post" id="form_pay" name="form_pay">
    <input type="text" name="pay_client_id" placeholder="ID" />
    <input type="text" name="pay_amount" placeholder="AMOUNT" />
    <input type="text" name="pay_number" placeholder="NUMBER" />
    <input type="text" name="pay_due_date" placeholder="DATE" />

    <input type="submit" id="submit_pay" name="submit_pay" value="Create Table" />
</form>


<?php


if (isset($_POST["submit_pay"])) {

    // post from form
    $client_id      = $_POST['pay_client_id'];
    $amount         = $_POST['pay_amount'];    
    $pay_number     = $_POST['pay_number'];


    //connection details
    $servername = "localhost";
    $username = "pm_app_user_2018";
    $password = "qEFoaD=m_g7Iny*jIg1^GLjJ";
    $dbname = "propertymode_app_db_2018";

    //connection
    $conn = new mysqli($servername, $username, $password, $dbname);
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }



    // time interval function come here
    // https://gist.github.com/pavlepredic/6220041#file-gistfile1-php 
    // by pavle predic      



    //due date from form
    $due_date       = new DateTime($_POST['pay_due_date']);

    //query
    for ($i = 0; $i < $pay_number; $i++) {

        //time interval function implementation
        $int            = new TimeInterval(0+$i, TimeInterval::MONTH);
        $future         = $int->addToDate($due_date, true); 

        // date string for mysqli
        $due_date_str   = $future->format('Y-m-d');

        // insert into table
        $sql =  "INSERT INTO pay_schedules 
            (client_id, amount, due_date)
                VALUES ( ?, ?, ? ) ";

        $stmt = $conn->prepare($sql);
        $stmt->bind_param("sss", $client_id, $amount, $due_date_str);
        //echo $sql;
        // messages
        $stmt->execute();
        $stmt->close();

        // checking results
        echo $future->format('Y-m-d'); echo ' | ';
    }

    //closing 
    $conn->close();
}
?>

Outcome in DB table example:

==================================================
pay_id      | client_id     | amount    | due_date
==================================================
1           | 10001C        | 100.25    | 2019-01-31
2           | 10001C        | 100.25    | 2019-02-28
3           | 10001C        | 100.25    | 2019-03-31
4           | 10001C        | 100.25    | 2019-04-30
5           | 10001C        | 100.25    | 2019-05-31
6           | 10001C        | 100.25    | 2019-06-30
7           | 10001C        | 100.25    | 2019-07-31
8           | 10001C        | 100.25    | 2019-08-31
9           | 10001C        | 100.25    | 2019-09-30
10          | 10001D        | 275.50    | 2019-01-12
11          | 10001D        | 275.50    | 2019-02-12
12          | 10001D        | 275.50    | 2019-03-12
13          | 10001D        | 275.50    | 2019-04-12

So the final result is exactly what I needed.

This also works for different intervals: year, month, week, day, hour, minute and second.

Thank you all!

Answer 3

what have you tried? It seems a little bit like you just want us to do the work for you as these questions wouldn't be too difficult to research?

"how to insert number of records based on pay_number field (e.g. 4 records, 10 records, 24 records ect) how to create foreach loop that increase month +1 with each inserted record up to the number of selected pay_number field"

these are the same thing, you just need to create a loop such as

while($i <= $payNumber){
//build your query
} 

as for your work with the dates you should be able to easily find that by googling something like "how to increase the date by 1 month php" and "ho to check if a month contains a day" or something, then use a comparison inside you loop

You should do some reaserch for each individual problem and you will quickly find a solution. I will give you 1 tip on doing multiple inserts though, a good way to do this is to store them in an array like this:

$inserts= [];
$values = [];
`$i` = 0;
while(condition) {
   $inserts[] = "(:value1_$i,:value2_$i,:value3_$i)";
   $values[':value1_$i'] =$value1; //..... (etc) 
}

then you can use implode(',',$inserts) to create your query string and execute the array

execute($values);

notice that I have used placemarkes here to encourage you to use a prepared statement (this is set up for PDO - but i'm sure you can do somethign similar with mysqli_prepared statements) which you should certainly be using, if you don't know how you should research this before going further

Answer 4

Try this,

if (isset($_POST["submit_pay"])) {

    $client_id      = $_POST['pay_client_id'];
    $amount     = $_POST['pay_amount'];    
    $pay_number     = $_POST['pay_number'];


    //connection details
    $servername = "localhost";
    $username = "my_user";
    $password = "my_password";
    $dbname = "my_db";

    $conn = new mysqli($servername, $username, $password, $dbname);
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }
    for ($i = 0; $i < $pay_number; $i++) {
        $due_date   = date('Y-m-t', strtotime("first day of +".($i)." months", strtotime($_POST['pay_due_date'])));
        // insert into table
        $sql =  "INSERT INTO pay_schedules 
            (client_id, amount, due_date)
                VALUES ( ?, ?, ? ) ";
        $stmt = $conn->prepare($sql);

        $stmt->bind_param("sss", $client_id, $amount, $due_date);
        //echo $sql;
        // messages
        $stmt->execute();
        $stmt->close();

    }

    $conn->close();
}

?>

<form action="" method="post" id="form_pay" name="form_pay">
    <input type="text" name="pay_client_id" />
    <input type="text" name="pay_amount" />
    <input type="text" name="pay_number" />
    <input type="text" name="pay_due_date" />

    <input type="submit" id="submit_pay" name="submit_pay" value="Create Table" />
</form>