The Web Design Group

... Making the Web accessible to all.

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> UPDATE ROWS FOR N NUMBER OF TIMES
shankar from vizag
post Apr 30 2022, 09:18 PM
Post #1


Advanced Member
****

Group: Members
Posts: 188
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
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
CharlesEF
post May 1 2022, 12:40 AM
Post #2


Programming Fanatic
********

Group: Members
Posts: 1,900
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?
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
shankar from vizag
post May 1 2022, 05:18 AM
Post #3


Advanced Member
****

Group: Members
Posts: 188
Joined: 18-June 13
Member No.: 19,316



QUOTE(CharlesEF @ May 1 2022, 11:10 AM) *

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.

regardsAttached File  update_query.pdf ( 39.74k ) Number of downloads: 3
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
shankar from vizag
post May 1 2022, 07:08 AM
Post #4


Advanced Member
****

Group: Members
Posts: 188
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
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
CharlesEF
post May 1 2022, 08:16 AM
Post #5


Programming Fanatic
********

Group: Members
Posts: 1,900
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);
          }
      }
   }
}
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
shankar from vizag
post May 4 2022, 08:48 PM
Post #6


Advanced Member
****

Group: Members
Posts: 188
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)
Attached File  update.php ( 5.62k ) Number of downloads: 6
Attached File  availability.xml ( 2.37k ) Number of downloads: 9
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
CharlesEF
post May 5 2022, 06:20 PM
Post #7


Programming Fanatic
********

Group: Members
Posts: 1,900
Joined: 27-April 13
From: Edinburg, Texas
Member No.: 19,088



QUOTE(shankar from vizag @ May 4 2022, 08:48 PM) *

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.
Your current code updates cabin1 thru cabin3 regardless of which cabin was picked. You need to remove the code for cabin2 and cabin3.

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.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
CharlesEF
post May 6 2022, 12:34 AM
Post #8


Programming Fanatic
********

Group: Members
Posts: 1,900
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
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post

Reply to this topicStart new topic
2 User(s) are reading this topic (2 Guests and 0 Anonymous Users)
0 Members:

 



- Lo-Fi Version Time is now: 25th May 2022 - 06:24 PM