update multiple rows based on the same appno

[ralph03]

Hi guys, based on image 1, I plan to update staffno in multiple rows based on appno. I aim to update staffno=3 with all appno=4. However, only 1 staffno updated. Here is the code:

 

<?php

if (isset($_POST['update_btn'])){

    $staffno = $_POST['staffno']; // ID of the record to be updated

   

    $update = "UPDATE applicantabove18p2

                SET staffno = '$staffno'

                WHERE appid = '$appid'";

 

    $update1 = "UPDATE applicantabove18p1

                SET staffno = '$staffno'

                WHERE appid = '$appid'";

 

    $update2 = "UPDATE applicantabove18p1a

                SET staffno = '$staffno'

                WHERE appid = '$appid'";

 

    $update3 = "UPDATE applicantabove18att

                SET staffno = '$staffno'

                WHERE appid = '$appid'";

 

    if (mysqli_query($con, $update) && mysqli_query($con, $update1) && mysqli_query($con, $update2) && mysqli_query($con, $update3)) {

    if ($data){

        ?>

        <script type="text/javascript">

            alert("Data updated successfully");

            window.open("coordinatorview3.php","_self")

        </script>

        <?php

    }

    else{

        ?>

        <script type="text/javascript">

            alert("Please try again");

        </script>

        <?php

    }

}

}

?>

The code related is $update3. Please advise. Tq in advance

[Barand]

One query will suffice - you don't need four. And why are they updating 4 different tables?
 

UPDATE tablename
SET staffno = 3
WHERE appno = 4;

 

[ralph03]

In this case, only $update3 is affected.

 $update3 = "UPDATE applicantabove18att

                SET staffno = '$staffno'

                WHERE appid = '$appid'";

Thank you for your suggestion, however, when I changed WHERE appid to appno, it didn't update even one row.

[Barand]

No problem with the query at this end

mysql> select * from test_91;
+-------+-------+---------+
| appid | appno | staffno |
+-------+-------+---------+
|     1 |     2 |       2 |
|     2 |     3 |       2 |
|     3 |     4 |       0 |
|     4 |     4 |       0 |
|     5 |     4 |       0 |
|     6 |     4 |       0 |
+-------+-------+---------+
6 rows in set (0.00 sec)

mysql> UPDATE test_91
    -> SET staffno = 3
    -> WHERE appno = 4;
Query OK, 4 rows affected (0.09 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> select * from test_91;
+-------+-------+---------+
| appid | appno | staffno |
+-------+-------+---------+
|     1 |     2 |       2 |
|     2 |     3 |       2 |
|     3 |     4 |       3 |
|     4 |     4 |       3 |
|     5 |     4 |       3 |
|     6 |     4 |       3 |
+-------+-------+---------+
6 rows in set (0.00 sec)

[Barand]

Same thing using PHP

<?php
const HOST = '????';
const USERNAME = '????';
const PASSWORD = '????';
const DBNAME = '????';

$staffno = 3;
$appno = 4;

    mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);         // Tell mysql to report errors
    $conn = mysqli_connect(HOST,USERNAME,PASSWORD,DBNAME);           // connect to DB
    $conn->set_charset('utf8');
    
    ShowData($conn);

    $stmt = $conn->prepare("UPDATE test_91
                            SET staffno = ?
                            WHERE appno = ?
                            ");
    $stmt->bind_param('ii', $staffno, $appno);
    $stmt->execute();
    
    echo "<br><b>Table updated here</b><br>";
    
    ShowData($conn);
    
function ShowData($conn)
{
    $res = $conn->query("SELECT * FROM test_91");
    echo '<pre>';
    printf("%-10s%-10s%-10s<br><br>", 'AppID', 'AppNo', 'StaffNo');
    foreach ($res as $row)   {
        vprintf("%-10s%-10s%-10s<br>", $row);
    }
    echo "</pre>\n";
}
?>

Output...

AppID     AppNo     StaffNo   

1         2         2         
2         3         2         
3         4         0         
4         4         0         
5         4         0         
6         4         0         

Table updated here
AppID     AppNo     StaffNo   

1         2         2         
2         3         2         
3         4         3         
4         4         3         
5         4         3         
6         4         3  

 

[ralph03]

3 hours ago, Barand said:

Same thing using PHP

<?php
const HOST = '????';
const USERNAME = '????';
const PASSWORD = '????';
const DBNAME = '????';

$staffno = 3;
$appno = 4;

    mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);         // Tell mysql to report errors
    $conn = mysqli_connect(HOST,USERNAME,PASSWORD,DBNAME);           // connect to DB
    $conn->set_charset('utf8');
    
    ShowData($conn);

    $stmt = $conn->prepare("UPDATE test_91
                            SET staffno = ?
                            WHERE appno = ?
                            ");
    $stmt->bind_param('ii', $staffno, $appno);
    $stmt->execute();
    
    echo "<br><b>Table updated here</b><br>";
    
    ShowData($conn);
    
function ShowData($conn)
{
    $res = $conn->query("SELECT * FROM test_91");
    echo '<pre>';
    printf("%-10s%-10s%-10s<br><br>", 'AppID', 'AppNo', 'StaffNo');
    foreach ($res as $row)   {
        vprintf("%-10s%-10s%-10s<br>", $row);
    }
    echo "</pre>\n";
}
?>

Output...

AppID     AppNo     StaffNo   

1         2         2         
2         3         2         
3         4         0         
4         4         0         
5         4         0         
6         4         0         

Table updated here
AppID     AppNo     StaffNo   

1         2         2         
2         3         2         
3         4         3         
4         4         3         
5         4         3         
6         4         3  

 

Thank you sir for your solution given. Can you please modify it using my share code above? This is due to I have an issue understanding the flow of your suggested code.Tq

[Barand]

23 minutes ago, ralph03 said:

I have an issue understanding the flow of your suggested code

It flows from top to bottom, as normal.

[ralph03]

Ok, finally I got the solution. The code is as below for sharing. Thanks guys for your help!

<?php

if (isset($_POST['update_btn'])){

   

    if (!empty($_POST['staffno'])) {

        $staffno = trim($_POST['staffno']);

    $appno = $_POST['appno']; // ID of the record to be updated

 

    $update = "UPDATE applicantabove18p2

                SET staffno = '$staffno'

                WHERE appid = '$appid'";

 

    $update1 = "UPDATE applicantabove18p1

                SET staffno = '$staffno'

                WHERE appid = '$appid'";

 

    $update2 = "UPDATE applicantabove18p1a

                SET staffno = '$staffno'

                WHERE appid = '$appid'";

 

    $update3 = "UPDATE applicantabove18att

                SET staffno = '$staffno'

                WHERE appno = '$appid'";

 

    if (mysqli_query($con, $update) && mysqli_query($con, $update1) && mysqli_query($con, $update2) && mysqli_query($con, $update3)) {

    if ($data){

 

        ?>

        <script type="text/javascript">

            alert("Data updated successfully");

            window.open("coordinatorview3.php","_self")

        </script>

        <?php

     }

    } else {

      echo "Update failed: " . mysqli_error($con);

    }

  } else {

    echo "Staff number cannot be empty";

 

}

}

 

?>