while loop only updates one record

miguels · February 10, 2011

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!

Pikachu2000 · February 10, 2011

When posting code, please enclose it within the forum's

 . . .

BBCode tags.

KevinM1 · February 10, 2011

You end your while-loop prematurely. Look at where its ending brace is.

btherl · February 11, 2011

miguels, the problem is that you are using $result for a query inside the loop. This overwrites the $result from the outside query.

miguels · February 11, 2011

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 · February 11, 2011

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 · February 11, 2011

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 · February 11, 2011

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 · February 11, 2011

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!

Sign In

while loop only updates one record

Recommended Posts

miguels

Link to comment

Share on other sites

Pikachu2000

Link to comment

Share on other sites

KevinM1

Link to comment

Share on other sites

btherl

Link to comment

Share on other sites

miguels

Link to comment

Share on other sites

btherl

Link to comment

Share on other sites

miguels

Link to comment

Share on other sites

miguels

Link to comment

Share on other sites

miguels

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information