Multiple Update Query

Question

I have written two different codes in php and I want to merge it with only one code because both codes are the same, only UPDATE Query is different. Can any one help me? and the second problem is if Condition does'nt work why is that?

code1:

 <?php

include("Database/connection.php");

$sql = "SELECT *  FROM registration,billing_month";
$result = $link->query($sql);

while ($row = $result->fetch_assoc()) {
    $regid = $row['Reg_id'];
    $duedate = $row['Bill_due_date'];
    $currentbill = $row['Current_Bill'];
    $arrears = $row['Arrears'];
    $updatearrears = $arrears + $currentbill;

    if (date('Y-m-d') > $duedate) {
        $sql_update = "UPDATE  registration SET Arrears= $updatearrears WHERE Reg_id = $regid";

        if (mysqli_query($link, $sql_update)) {
            //   echo "Updated";
        } else {
            // echo "Could not updated";
        }
    }
}
?>

Code2:

   <?php

    include("Database/connection.php");

    $sql = "SELECT *  FROM registration,billing_month";
    $result = $link->query($sql);

    while ($row = $result->fetch_assoc()) {
        $regid = $row['Reg_id'];
        $billingid = $row['Bill_id'];
        $duedate = $row['Bill_due_date'];
        $currentbill = $row['Current_Bill'];
        $updatebill = 0;

        if (date('Y-m-d') > $duedate) {
            $sql_update = "UPDATE  registration SET Current_Bill= $updatebill WHERE Reg_id = $regid";

            if (mysqli_query($link, $sql_update)) {
                //   echo "Updated";
            } else {
                // echo "Could not updated";
            }
        }
    }
    ?>

`UPDATE registration SET Arrears= '$updatearrears', Current_Bill= '$updatebill' ..` — Sougata Bose, Aug 19 '15 at 13:18
Don't do this. The separate SELECT is entirely unnecessary, and you should never execute a query inside a loop. — Strawberry, Aug 19 '15 at 13:23
You are potentially open to SQL injections with this code. Always use prepared statements. https://en.wikipedia.org/wiki/SQL_injection#Second_Order_SQL_Injection — chris85, Aug 19 '15 at 13:25
u mean i close while loop first and then execute update query? @Strawberry — Imran Khan, Aug 19 '15 at 13:35
How `registration` table joins `billing_month`? What is the common field? — shudder, Aug 19 '15 at 13:51
there is no common field in 'registration' and 'billing_month' however the value of 'billing_month' '1.Current_Dues' and '2.Surcharge' is updated to 'registration' table '1.Current_bill' and '2.Current_Surcharge' when current date is greater then 'due_date' and 'due_date' is a field in 'billing_month' — Imran Khan, Aug 19 '15 at 14:00
In this instance, there's no requirement for a loop at all, just a query, but yes - where using a loop, use it to build the whole query, and then execute that query outside the loop. — Strawberry, Aug 19 '15 at 17:29

score 0 · Accepted Answer · edited May 23 '17 at 12:14

0

Use strtotime() to compare your data. Also add quotes in your value.

Before update into database use

 $updatebill=mysqli_real_escape_string($link,$updatebill);

if (strtotime(date('Y-m-d')) > strtotime($duedate)) 
{
  $sql_update = "UPDATE  `registration` SET `Current_Bill`= '".$updatebill ."' WHERE `Reg_id` = $regid";

}

To check error in your page use

ini_set('display_errors',1);
ini_set('display_startup_errors',1);
error_reporting(-1);

And Prevent you query by sql injection

edited May 23 '17 at 12:14

Community

1
1

answered Aug 19 '15 at 13:22

Saty

22,443
7
33
51

and can you tell me how to combine this two php codes – Imran Khan Aug 19 '15 at 13:34
just write your second query inside you first code because both code are same and having same condition – Saty Aug 19 '15 at 13:36
brother multiple query can,t work like this. `$updatebill=mysqli_real_escape_string($link,$updatebill); if (strtotime(date('Y-m-d')) > strtotime($duedate)) { $sql_update = "UPDATE `registration` SET `Current_Bill`= '".$updatebill ."' WHERE `Reg_id` = $regid"; $sql_update = "UPDATE `billing_month` SET `Current_Dues`= $updatebill WHERE `Bill_id` = $billid"; }` – Imran Khan Aug 19 '15 at 14:41

Multiple Update Query

1 Answers1