INSERT INTO MYSQL TABLE AS PER INPUT DATE RANGE WITH A LOOP |
INSERT INTO MYSQL TABLE AS PER INPUT DATE RANGE WITH A LOOP |
shankar from vizag |
Apr 26 2022, 08:32 PM
Post
#1
|
Advanced Member Group: Members Posts: 202 Joined: 18-June 13 Member No.: 19,316 |
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 |
Apr 27 2022, 02:38 AM
Post
#2
|
Programming Fanatic Group: Members Posts: 1,984 Joined: 27-April 13 From: Edinburg, Texas Member No.: 19,088 |
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 |
Apr 30 2022, 09:04 PM
Post
#3
|
Advanced Member Group: Members Posts: 202 Joined: 18-June 13 Member No.: 19,316 |
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 |
Lo-Fi Version | Time is now: 26th September 2024 - 01:59 PM |