Loop In Updating Table Data With Php And MySQL

Question

I am developing an application that updates data in a table based on values selected from another table. I had searched the net,a nd tried to modify some samples to fit my needs, but still not working.

Below is some of my code:

$sync_2 = "
  select pin,ddt_1
  from purchases
  where (amount_to_repay-amount_repaid)>0
    and status like 'U%'
    and counter=1
";
$sync_2_res = mysqli_query($link, $sync_2);
$row = mysqli_fetch_assoc($sync_2_res);
$data = mysqli_num_rows($sync_2_res);

$i;
for ($i = 0; $i <= $data; $i++) {
  $pin = $row['pin'];
  $ddt = $row['ddt_1'];
  $sql = "
    update deductions
    set amount = '$ddt_1'
    where pin = '$pin';
  $result = mysqli_query($link,$sql);
}

Nothing is getting populated. Do I have to change the type of loop?.

your missing a ' on $row['ddt'] this would cause the script to give an error. — Nick
– Nick, Commented Apr 4, 2012 at 12:43
You have a couple of syntax errors above, which are a lot more obvious when you indent you code sensibly. — DaveRandom
– DaveRandom, Commented Apr 4, 2012 at 12:48

DaveRandom · Accepted Answer · 2012-04-04 12:55:16Z

2

Try this code, which should do the whole operation in a single query:

$query = "
  UPDATE `deductions` `d`
  JOIN `purchases` `p` ON `p`.`pin` = `d`.`pin`
  SET `d`.`amount` = `p`.`ddt_1`
  WHERE
    (`p`.`amount_to_repay` - `p`.`amount_repaid`) > 0
    AND `p`.`status` LIKE 'U%'
    AND `p`.`counter` = 1
";
$result = mysqli_query($link, $query);

answered Apr 4, 2012 at 12:55

DaveRandom

88.8k11 gold badges159 silver badges174 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Navneet Pandey · Accepted Answer · 2012-04-04 12:46:47Z

1

AS YOU HAVE GIVEN there might be problem in your variable change $ddt_1 to $ddt in sql statement

 for($i=0;$i<=$data;$i++)

 {
 $pin=$row['pin'];
 $ddt=$row['ddt_1];
 $sql="update
  deductions
 set amount='$ddt'
 where pin='$pin';
$result=mysqli_query($link,$sql);
    }

answered Apr 4, 2012 at 12:46

Navneet Pandey

1331 silver badge9 bronze badges

Comments

Baba · Accepted Answer · 2012-04-04 12:59:14Z

0

Improved PHP example

$mysqli = new mysqli ( "localhost", "root", "", "test" );
$sql  = "SELECT pin , ddt_1  from purchases WHERE  amount_to_repay-amount_repaid  >  0  AND  status like 'U%' AND counter = 1";
$sql2 = "update deductions  set amount = '%s'  where pin = '%s'; ";
$result = $mysqli->query($sql);
$row = null ;
while ($row = $result->fetch_assoc())
{
     $mysqli->query(sprintf ( $sql, $row ['pin'], $row ['ddt_1'] ));
}

OLD POST

Replace

 $ddt=$row['ddt_1];

With

 $ddt=$row['ddt_1'];

Replace

set amount='$ddt_1'

With

set amount='$ddt'

I hope this helps

Thanks

:)

edited Apr 4, 2012 at 12:59

answered Apr 4, 2012 at 12:48

Baba

95.3k29 gold badges172 silver badges222 bronze badges

1 Comment

Baba Over a year ago

yes because you did not close most of your strings .. i just added an improved PHP option for you

Annabel · Accepted Answer · 2012-04-04 13:17:32Z

The first thing to note is that mysqli_fetch_assoc() only fetches one row, the row where the cursor is. Then it moves the cursor one row on. When the cursor gets to the end of the data mysqli_fetch_assoc() returns NULL.

So to iterate through a set of results people usually use a while loop as this is neatest:

$sync_2_res = mysqli_query($link, $sync_2);

if($sync_2_res == null) {
    echo "The sync2 query failed: " . mysqli_error($link);
    exit();
}

while($row = mysqli_fetch_assoc($result) {
    // do something with $row
}

Also its a good idea to check that the query worked and returned a result and not FALSE.

Next, I think there is a typo in the 2nd query: $dtt_1 should be $dtt, and are you really sure that the amount field should be set to a string?

Anyway here's how I would rewrite your code:

$sync_2= "select pin,ddt_1 from purchases "
    . "where (amount_to_repay-amount_repaid)>0 "
    . "and status like 'U%' and counter=1";

$sync_2_res=mysqli_query($link,$sync_2);

// check the query worked
if(!$sync_2_res) {
    echo "The sync2 query failed: " . mysqli_error($link);
    exit();
}

while($row = mysqli_fetch_assoc($result) {
    // do something with $row
    $pin=$row['pin'];
    $ddt=$row['ddt_1'];
    // note change from $ddt_1 to $dtt
    $sql="update deductions set amount='$ddt' where pin='$pin';";

    $result=mysqli_query($link,$sql);
    // check query worked
   if(!$result) {
      echo "The 2nd query failed: " . mysqli_error($link);
   }
}

Collectives™ on Stack Overflow

Loop In Updating Table Data With Php And MySQL

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related