php while loop

Bananna man

Hey,

Im trying to do a while loop and update my databse for each individual row that is affected in the while loop. My code is as follows:

		while ($rows = mysql_fetch_array($results)) {
		extract ($rows);
		$total_points = $answer_points + $percentage_points + $bonus_points + $sum_of_points;
		echo $total_points;
		echo "<br>";
		}

		$sql = "UPDATE ".$game."_".$week."_survey_results
		SET total_points_".$game."_".$week." = ".$total_points."
		WHERE survey_user_id
		LIKE '%".$survey_user_id."%'";
		$results = mysql_query($sql)
			or die(mysql_error());

If i move the closing curley bracket to the end of the update code it will not work and if i leave it where it is the update only happens to the last variable in the while loop. Anyone know what i can do to sort this out?

Thanks

steveland?

How about something like:

		while ($row = mysql_fetch_array($results)) {
                $answer_points = $row["answer_points"];
                $percentage_points = $row["percentage_points"];
                $bonus_points = $row["bonus_points"];
                $sum_of_points = $row["sum_of_points"];

		$total_points = $answer_points + $percentage_points + $bonus_points + $sum_of_points;
		echo "$total_points";
		echo "<br>";


		 mysql_query("UPDATE ".$game."_".$week."_survey_results
		SET total_points_".$game."_".$week." = ".$total_points."
		WHERE survey_user_id
		LIKE '%".$survey_user_id."%'") or die(mysql_error());
                }

Can't be completely sure since I don't know what the original query was or how you assigned variables to the rows you choose from the database...

I could also be completely wrong

Bananna man

Thanks for the reply. I tried moving the curly bracket to the very end of the query too, this seems like the logical step but for some reason when you do this it just changes the last row in the databse. I'd dsay their is probably some kind of function for this but i havent got a clue!!

Also, I have been learning PHP on my own from a book and have covered alot of the basics now. I was wondering how to take the next step, can anyone recommend a good book for more advanced php programming?

rsynnott

I believe that it is considered NAUGHTY to perform a query, then another, and then try to use results from the first one. You could get around this by using two database connections, but that's horrible.

How about

UPDATE bla SET total_points = answer_points + bla_ponts + bla2_points;

In fact, why are you keeping total_points in the database, when it is simply calculated from other values in the database?

Bananna man

Now there's the piece of lateral thinking i was missing, cheers Rsynnott!!!

kdouglas

WHERE survey_user_id LIKE '%".$survey_user_id."%'")

using a LIKE statement and wildcards (%) in a WHERE for an update is a bit dodgy, use = instead

Bananna man

Can anyone recommend a good book for a bit more advanced php. I've got a ahnadle on some of the basics now and want to take it a step further.