Jump to content

while loop only updates one record

miguels

By miguels
in PHP Coding Help

 Share

Recommended Posts

miguels Newbie

miguels

Posted
Posted

Ok, i've driven myself mad trying to figure this out. While I'm new at php I really think this should work. Could someone look at this and point me in the right direction? 

 

Ok background info: 

 

I have three tables  that I'm selecting from. I want to find the records in TABLE A that match these three criteria and "Join" per say.  the SELECT does exactly what I want and works.  My issue is when I try to LOOP through and pull certain information make adjustments in other tables Table C.. it only adjust as if it's gone through ONLY one loop. It will only do last record.  I want it to loop through the records it's found and Update the field in Table C to the balance of TABLE C + TABLE B price. Then update TABLE A with a value of 1 and LOOP and do it again until all records found in the original select are done.

 

CODE:

 

require_once ('./includes/config.inc.php');



$query = "SELECT 
			table_a.id,
			table_a.table_b_id,
			table_a.user_id,
			table_a.unit_returned,
			table_b.id,
			table_b.title,
			table_b.price,
			table_b.closed,
			table_c.id,
			table_c.nick,
			table_c.balance

	FROM 
			table_a, table_b, table_c
      
	WHERE 
			table_a.unit_returned= 0 
		AND table_b.closed = 1
		AND table_b.id = table_a.table_b_id
		AND table_c.id = table_a.user_id ";



$result = mysql_query($query);
$num = mysql_num_rows($result);
  echo "<table border='1'>";
   
  
   
   while ($row = mysql_fetch_array($result, MYSQL_NUM)) 
   
   {
      echo "<tr><td>$row[0]</td><td>$row[1]</td><td>$row[2]</td><td>$row[3]</td><td>$row[4]</td><td>$row[5]</td><td>$row[6]</td><td>$row[7]</td><td>$row[8]</td><td>$row[9]</td><td>$row[10]</td></tr>";

							$newbalance = $row[6];
                                $newbalance2 =$row[10];
                                $finalbalance = $newbalance+$newbalance2;
							$userid = $row[2];
							$signid = $row[0];

							echo "$newbalance". " $newbalance2" . " $finalbalance"."<br>". "$userid ". "<br>"."$signid"."<br>";

							$query = "table_c SET balance=".$finalbalance." WHERE id=" .$userid ;
							$result = mysql_query($query);
                                

 }

$query = "UPDATE table_a SET unit_returned=1"; //   THIS I WANT TO DO TO ALL RECORDS AS I LOOP AS WELL or at the end seeing as above should find all the records anyways. wouldn't matter to me.
$result = mysql_query($query);

 

ANY HELP WOULD BE GREATLY APPRECIATED

 

 

THANKS!!!! so MUCH!

 

 

 

 

 

Link to comment
https://forums.phpfreaks.com/topic/227308-while-loop-only-updates-one-record/
Share on other sites
miguels Newbie

miguels

Posted
  • Author
Posted

Yea I tried this as well, actually it was originally how I wrote..but in my tables the only action taken is

and what I am displaying here is what happens on back end.

3+ 354 DOES equal 357 but then that 357 but then below in the next loop for the second record it doesn't carry forward that it's now  357 it uses 354 and adds 5 to it.  In sql it actually sets the second entry so you end up with a final balance of 359 when it should be 362.  Maybe this can make it clearer.  At any rate I've tried both suggestion and get the same answer I've been getting. It's as if it's skipping the first record and jumping to second and doing what I wanted it to do but only for that record.

 

3 354 357
56
58
5 354 359
56
59
58	4789	56	0	4789	Description	3	1	56	cookiemonster	354
59	4878	56	0	4878	description 	5	1	56	cookiemonster	354



require_once ('./includes/config.inc.php');



$query = "SELECT 
			table_a.id,
			table_a.table_b_id,
			table_a.user_id,
			table_a.unit_returned,
			table_b.id,
			table_b.title,
			table_b.price,
			table_b.closed,
			table_c.id,
			table_c.nick,
			table_c.balance

	FROM 
			table_a, table_b, table_c
      
	WHERE 
			table_a.unit_returned= 0 
		AND table_b.closed = 1
		AND table_b.id = table_a.table_b_id
		AND table_c.id = table_a.user_id ";



$result = mysql_query($query);
$num = mysql_num_rows($result);
  echo "<table border='1'>";
   
  
   
   while ($row = mysql_fetch_array($result, MYSQL_NUM)) 
   
   {
      echo "<tr><td>$row[0]</td><td>$row[1]</td><td>$row[2]</td><td>$row[3]</td><td>$row[4]</td><td>$row[5]</td><td>$row[6]</td><td>$row[7]</td><td>$row[8]</td><td>$row[9]</td><td>$row[10]</td></tr>";

							$newbalance = $row[6];
                                $newbalance2 =$row[10];
                                $finalbalance = $newbalance+$newbalance2;
							$userid = $row[2];
							$signid = $row[0];

							echo "$newbalance". " $newbalance2" . " $finalbalance"."<br>". "$userid ". "<br>"."$signid"."<br>";

							$query = "table_c SET balance=".$finalbalance." WHERE id=" .$userid ;

                                $query = "UPDATE table_a SET unit_returned=1";

 }


$result = mysql_query($query);

btherl Member

btherl

Posted
Posted

The queries inside the loop are not being executed in your latest code.  The first query is also invalid.  Try this (inside the loop, replacing the existing $query = lines):

 

								$query = "UPDATE table_c SET balance=".$finalbalance." WHERE id=" .$userid ;
                                                              $tabc_result = mysql_query($query);
                                                              if (!$tabc_result) die("Query failed: $query\n" . mysql_error());

                                                              $query = "UPDATE table_a SET unit_returned=1";
                                                              $taba_result = mysql_query($query);
                                                              if (!$taba_result) die("Query failed: $query\n" . mysql_error());

 

Also note there that there is no WHERE on the second query.  You should probably either move that query back out of the loop, or add the condition.

miguels Newbie

miguels

Posted
  • Author
Posted

Ok i'll give that a shot.. as for the Where clause in second part, my theory was I was only looking for the ones that has zero as a value and all records should have 1 when complete. so I just set them all to 1 again although I can see where as the table gets 100k records it will have a load on it so I'll change Thanks!

miguels Newbie

miguels

Posted
  • Author
Posted

Ok I addded the WHERE on the second query for good measure.  I works as it should.. but still only updating first record of loop.

 

the result: see below  this should be 3 + 359 =362 then next line should be 5+362= 367..  the first row gets skipped and the balance gets set to 364 when it should be 367

3 359 362

56

58

5 359 364

56

59

58 4789 56 0 4789 description 3 1 56 cookiemonster 359

59 4878 56 0 4878 description 5 1 56 cookiemonster 359

 

THANKS EVERYONE for you input, this has driven me crazy and still is lol

 

 



 while ($row = mysql_fetch_array($result, MYSQL_NUM)) 

   

   {

      echo "<tr><td>$row[0]</td><td>$row[1]</td><td>$row[2]</td><td>$row[3]</td><td>$row[4]</td><td>$row[5]</td><td>$row[6]</td><td>$row[7]</td><td>$row[8]</td><td>$row[9]</td><td>$row[10]</td></tr>";


								$newbalance = $row[6];

                                $newbalance2 =$row[10];

                                $finalbalance = $newbalance+$newbalance2;

								$userid = $row[2];

								$signid = $row[0];


								echo "$newbalance". " $newbalance2" . " $finalbalance"."<br>". "$userid ". "<br>"."$signid"."<br>";


								$query = "table_c SET balance=".$finalbalance." WHERE id=" .$userid ;

								 $tabc_result = mysql_query($query);

                                 if (!$tabc_result) die("Query failed: $query\n" . mysql_error());

                                

								$query = "UPDATE table_a SET unit_returned=1";

								$taba_result = mysql_query($query);

                                if (!$taba_result) die("Query failed: $query\n" . mysql_error());



	 }



$result = mysql_query($query);


[/Code]

miguels Newbie

miguels

Posted
  • Author
Posted

I wanted to say thanks to all who helped me what I ended up doing was sum the price and then group by tablea userid

Just wanted to post in case other ran into this...

 

 

<?

require_once ('./includes/config.inc.php');



$query = "SELECT 
			Sum(table_b.price,)
			table_a.id,
			table_a.table_b_id,
			table_a.user_id,
			table_a.unit_returned,
			table_b.id,
			table_b.title,
			table_b.price,
			table_b.closed,
			table_c.id,
			table_c.nick,
			table_c.balance

	FROM 
			table_a, table_b, table_c
      
	WHERE 
			table_a.unit_returned= 0 
		AND table_b.closed = 1
		AND table_b.id = table_a.table_b_id
		AND table_c.id = table_a.user_id ";
		GROUP BY table_a.user_id,


$result = mysql_query($query);
$num = mysql_num_rows($result);
  echo "<table border='1'>";
   
  
   
   while ($row = mysql_fetch_array($result, MYSQL_NUM)) 
   
   {
      echo "<tr><td>$row[0]</td><td>$row[1]</td><td>$row[2]</td><td>$row[3]</td><td>$row[4]</td><td>$row[5]</td><td>$row[6]</td><td>$row[7]</td><td>$row[8]</td><td>$row[9]</td><td>$row[10]</td></tr>";
							$sum = $row[0];
							$balance = $row[11];

                                $finalbalance = $newbalance+$newbalance2;
							$userid = $row[3];
							$signid = $row[1];
							$aucid = $row[2];

							echo "$newbalance". " $newbalance2" . " $finalbalance"."<br>". "$userid ". "<br>"."$signid"."<br>";

							$query = "UPDATE table_c SET balance=".$finalbalance." WHERE id=" .$userid ;
							 $tabc_result = mysql_query($query);
                                 if (!$tabc_result) die("Query failed: $query\n" . mysql_error());
                                
							$query = "UPDATE table_a SET unit_returned=1 WHERE unit_returned = 0";
							$taba_result = mysql_query($query);
                                if (!$taba_result) die("Query failed: $query\n" . mysql_error());


 }

 

Thanks to all again!

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.