UPDATE ROWS FOR N NUMBER OF TIMES |
UPDATE ROWS FOR N NUMBER OF TIMES |
shankar from vizag |
Apr 30 2022, 09:18 PM
Post
#1
|
Advanced Member Group: Members Posts: 202 Joined: 18-June 13 Member No.: 19,316 |
Greetings
Earlier I posted a topic for inserting a record for n number of times and now I need help and guidance for an update query in similar way. Please suggest me how to update multiple rows for n number of times (like user specified times to repeat) I tried with this, but not working Here $days is the result of date range difference stored in a variable if(isset($days) && is_numeric($days) && $days !='' && $days>0) { for($i=1;$i<=$days;$i++) { $sql = "UPDATE availability SET cabin1='$name' where `gen_date`='$datefrm'"; if(mysqli_query($conn, $sql)){ echo "<h3>data stored in a database successfully."; } else{ echo "ERROR: Hush! Sorry $sql. " . mysqli_error($conn); } } } I have a table `availability` with columns gen_date, cabin1, cabin2, cabin3, cabin4 and this is for room reservation application. the above table should fill with name of guest as per stay days corresponding to the `gen_date` column. regards |
CharlesEF |
May 1 2022, 12:40 AM
Post
#2
|
Programming Fanatic Group: Members Posts: 1,996 Joined: 27-April 13 From: Edinburg, Texas Member No.: 19,088 |
Where is '$datefrm' defined? Does '$datefrm' increase by 1 for each loop? Is the same '$name' going to be used for all updates?
|
shankar from vizag |
May 1 2022, 05:18 AM
Post
#3
|
Advanced Member Group: Members Posts: 202 Joined: 18-June 13 Member No.: 19,316 |
Where is '$datefrm' defined? Does '$datefrm' increase by 1 for each loop? Is the same '$name' going to be used for all updates? Yes the guest name should repeat in the respective room column with respect to the date range. I have been attached sample table with my code which is not working in the attachment. regards update_query.pdf ( 39.74k ) Number of downloads: 2707 |
shankar from vizag |
May 1 2022, 07:08 AM
Post
#4
|
Advanced Member Group: Members Posts: 202 Joined: 18-June 13 Member No.: 19,316 |
CODE if(isset($days) && is_numeric($days) && $days !='' && $days>0) { for($i=1;$i<=$days;$i++) { $sql = "UPDATE availability SET cabin1='$name' WHERE `gen_date` BETWEEN '$datefrm' AND '$dateto' "; if(mysqli_query($conn, $sql)){ echo "<h3>data stored in a database successfully."; } else{ echo "ERROR: Hush! Sorry $sql. " . mysqli_error($conn); } } } The above update query works to update records of a particular guest for given date range but, how can I extend the code to other rooms as well. It is not working for other rooms when I use CODE if(isset($days) && is_numeric($days) && $days !='' && $days>0) { for($i=1;$i<=$days;$i++) { $sql = "UPDATE availability SET cabin2='$name' WHERE `gen_date` BETWEEN '$datefrm' AND '$dateto' "; if(mysqli_query($conn, $sql)){ echo "<h3>data stored in a database successfully."; } else{ echo "ERROR: Hush! Sorry $sql. " . mysqli_error($conn); } } } Kindly guide me in extending the query for other rooms as well regards |
CharlesEF |
May 1 2022, 08:16 AM
Post
#5
|
Programming Fanatic Group: Members Posts: 1,996 Joined: 27-April 13 From: Edinburg, Texas Member No.: 19,088 |
1st you need an array of room numbers. 2nd you need to add to the WHERE clause. Something like:
CODE $rooms = array(2, 4, 5, 6); if(isset($days) && is_numeric($days) && $days !='' && $days>0) { foreach($rooms as $key => $value) { for($i=1;$i<=$days;$i++) { $sql = "UPDATE availability SET cabin1='$name' WHERE `gen_date` BETWEEN '$datefrm' AND '$dateto' AND `room_no` = $value"; if(mysqli_query($conn, $sql)) { echo "<h3>data stored in a database successfully."; } else { echo "ERROR: Hush! Sorry $sql. " . mysqli_error($conn); } } } } |
shankar from vizag |
May 4 2022, 08:48 PM
Post
#6
|
Advanced Member Group: Members Posts: 202 Joined: 18-June 13 Member No.: 19,316 |
Greetings Charles
1. Here I am attaching two files. One is update.php and other one is mysql table in xml format. 2. My PHP file is working fine but only concern is, there is an input select box with name "ref", where admin will assign the room for the guest after receiving couple of requests from guests. 3. When submits the update.php form, the guest name is repeating successfully as per the days of stay but the same is repeating in other columns as well. 4. I am not understanding how to restrict the update query to the particular room assigned by the admin. 5. Kindly set my update query as per the requirement. Regards Attached File(s) update.php ( 5.62k ) Number of downloads: 1520 availability.xml ( 2.37k ) Number of downloads: 1490 |
CharlesEF |
May 5 2022, 06:20 PM
Post
#7
|
Programming Fanatic Group: Members Posts: 1,996 Joined: 27-April 13 From: Edinburg, Texas Member No.: 19,088 |
Greetings Charles 2. My PHP file is working fine but only concern is, there is an input select box with name "ref", where admin will assign the room for the guest after receiving couple of requests from guests. What you need to do is update your 'ref' select to include option values. Like this: CODE <option value="1">Cabin 1</option> <option value="2">Cabin 2</option> <option value="3">Cabin 3</option> <option value="4">Cabin 4</option> Now you need to change the cabin1 code to this: CODE $sql = "UPDATE `availability` SET `cabin . $ref`= '$name' WHERE `gen_date` BETWEEN '$datefrm' AND '$dateto' "; Of course you need to assign $ref first. Be sure to echo $sql, to be sure the cabin number is set correctly. Also, you use mysqli so you should be using prepared statements. Your current code is open to SQL injection attacks. |
CharlesEF |
May 6 2022, 12:34 AM
Post
#8
|
Programming Fanatic Group: Members Posts: 1,996 Joined: 27-April 13 From: Edinburg, Texas Member No.: 19,088 |
I don't think the $sql statement I posted will do what you want. You should use:
CODE $sql = "UPDATE `availability` SET `cabin{$ref}` = '$name' WHERE `gen_date` BETWEEN '$datefrm' AND '$dateto' "; This post has been edited by CharlesEF: May 6 2022, 12:35 AM |
Lo-Fi Version | Time is now: 4th December 2024 - 09:46 AM |