INSERT INTO MYSQL TABLE AS PER INPUT DATE RANGE WITH A LOOP Options

[shankar from vizag]

Greetings

________________________________________________________________
ID | Date | Name | Room Number
_________________________________________________________________
1 | 2022-05-01 | Thomas | 118

2 2022-05-15 Thomas 118

I have a mysql table to save the above data. It is for a room reservation and the person Thomas has a reservation from 2022-05-01 to 2022-05-15.

I want to save the thomas name for 15 times in the table using loop inside the insert into query for mysql. At present I am using the following plain insert query not understanding how to use the loop to store the name as per the date range.

$sql1 = "INSERT INTO allotment (date, name, room)VALUES ('$date', '$name', '$room')";

Kindly guide me to fix the issue for mysql. I am using xampp server 3.2.2 with php 5.6.40.

Regards

[CharlesEF]

It looks like your loop should be based on 'date'. Something like this:

CODE

$start = new DateTime('2022-05-01');
$end = new DateTime('2022-05-15');

while($start <= $end)
{
// $sql1 = "INSERT INTO allotment (date, name, room) VALUES ('" . $start->format("Y-m-d") . "', '$name', '$room')";
echo($start->format("Y-m-d") . PHP_EOL);
$start = $start->modify("+1 day");
}

Wouldn't it be better to have the starting and ending date on 1 record. That way you might not need 15 records inserted. Insert it once then use the WHERE clause when doing queries.

This post has been edited by CharlesEF: Apr 27 2022, 02:46 AM

[shankar from vizag]

It looks like your loop should be based on 'date'. Something like this:

CODE

$start = new DateTime('2022-05-01');
$end = new DateTime('2022-05-15');

while($start <= $end)
{
// $sql1 = "INSERT INTO allotment (date, name, room) VALUES ('" . $start->format("Y-m-d") . "', '$name', '$room')";
echo($start->format("Y-m-d") . PHP_EOL);
$start = $start->modify("+1 day");
}

Wouldn't it be better to have the starting and ending date on 1 record. That way you might not need 15 records inserted. Insert it once then use the WHERE clause when doing queries.

Thank you Charles, I tried the following with for loop

I stored the datediff in $days variable

if(isset($days) && is_numeric($days) && $days !='' && $days>0)
{
for($i=1;$i<=$days;$i++)
{
$sql = "INSERT INTO approved_request (name, frmdate, intime, todate, outtime, rooms, days, remarks)VALUES ('$name',
'$datefrm','$timein','$dateto','$timeout','$days','$rooms', '$remarks')";

if(mysqli_query($conn, $sql)){
echo "<h3>data stored in a database successfully.";


} else{
echo "ERROR: Hush! Sorry $sql. "
. mysqli_error($conn);
}

Thank you so much for your support Charles