Printable Version of Topic

Click here to view this topic in its original format

HTMLHelp Forums _ Databases _ UPDATE ROWS FOR N NUMBER OF TIMES

Posted by: shankar from vizag Apr 30 2022, 09:18 PM

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

Posted by: CharlesEF May 1 2022, 12:40 AM

Where is '$datefrm' defined? Does '$datefrm' increase by 1 for each loop? Is the same '$name' going to be used for all updates?

Posted by: shankar from vizag May 1 2022, 05:18 AM

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.

regards

update_query.pdf ( 39.74k ) Number of downloads: 167

Posted by: shankar from vizag May 1 2022, 07:08 AM

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

Posted by: CharlesEF May 1 2022, 08:16 AM

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);
          }
      }
   }
}

Posted by: shankar from vizag May 4 2022, 08:48 PM

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: 154
availability.xml ( 2.37k ) Number of downloads: 138

Posted by: CharlesEF May 5 2022, 06:20 PM

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.

Posted by: CharlesEF May 6 2022, 12:34 AM

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' ";