While loop to update data base

[jonki]

Hi,

I am trying to get my site to do some simple automatic accounting at the end of every week. I am writing a php file to do this which I will then set as a cron job.

I have a problem with a while loop and perhaps I am not using it correctly. I want to select * from my database and then put in a while loop so that on each run through the loop each entry gets updated.

Heres what I have: This is done every week in 4 week cycles and here is the loop for one week.

$result = mysql_query("select * from db"); //This gives all ALL the data
	while ($row = mysql_fetch_assoc ($result))
		{
		$limiteantiguo = $row["sem2"]; //This is just to work out how much is owed.
		$usuario = $row["usuario"];

	$query = "update db set sem3 = '$limiteantiguo', max3 = '$limiteantiguo' where usuario = '$usuario'";
	$result = mysql_query($query); //run the query 

	// Here are more accounting calculations similar to above but I deleted them because I don't think they are part of the problem.

	}
mysql_free_result($result);
}

Is my use of a while loop in this situation wrong? If so what would be the best way of automatically updating every entry in the database based of accounting information availabe? (bearing in mind I don't want to have to specify how many entries are in my db.)

I really appreciate any help.

Jon

[nemonoman]

It appears from your code that you want to update ALL the rows of the database, yes? And that you are moving data or doing a simple calculation on all rows, yest?

If so, you don't need a while loop:

$query = "update db set sem3 = sem2, max3 =sem2";

would work.

Here's an example of updating all the rows in a database:

UPDATE person
SET total=total+current,
current=0

This example updates only SELECTED rows

UPDATE person
SET age=age+1
WHERE birthday=current_date

You could of course build a while loop to do these functions, but why?

Bulk updates are why SQL databases are fast and powerful.

Wherever possible, make SQL do the work, not PHP.

[jonki]

Thanks for your help but I'm not quite sure if it will do what I want.

I need to get information from a particular row and update it based on that information. The ins and outs are quite complicated but to avoid going into all the detail lets say that I have someone who has put 5 ads on my site. He can change this at will and every week this number may be different. I then have to do a calculation to work out how much he owes me - this is based on other variables in that row on my db as well as the amount of ads (the more ads, the bigger the discount)

So basically I need to go through the database one row at a time and pull out relevant information, do some calculations based on that information and stick new figures back in to the database.

That is why I was trying to use a while loop..... any ideas?

Thanks

Jon

[nemonoman]

UPDATEs can be VERY complex

UPDATE mytable
SET mymoney =
if(adsThisWeek>minAdsPerWeek, adpriceadsThisWeek/CustomerDiscount,
adMinimumAmountDue/CustomerDiscount)
(1+SalesTax)
WHERE customerType='weekly'

I've seen UPDATE statements that extend over several pages of code.

SQL code evaluates in microseconds to seconds, even with thousands of rows affected, and is ALWAYS more efficient than a PHP while loop.

Wherever possible, figure out how to make SQL do the update. This isn't always possible, but it's always preferable.

[jonki]

That is fantastic.

Thanks a lot for your help.

Jon

[jonki]

Hi nemonoman

I really appreciate all your help. Unfortunately the book I am using has absolutley no information on complex update statements and I am trying to work out what those commas mean in your example.

I was wondering if you could explain them briefly.

Thanks

Jon

[nemonoman]

You probably need to take a look at SELECT functions to work out what I'm talking about.

For example:

SELECT CURRENT_DATE()

might return 2004-05-27

Anything you can SELECT can be ALSO be used in an UPDATE or INSERT statement.

UPDATE mytable SET lastupdate = CURRENT_DATE()

See how that works?

You can do calculations using different fields in the table. If you have a column named total and a column called thisweek, you could write

UPDATE mytable SET total = total+thisweek

You can also do calculations on the fly:

UPDATE mytable SET tax=total*.06

Now things get interesting when you start COMBINING functions:

For example DATEDIFF() returns the number of days between 2 dates -- for example to calculate the interest due on a loan, you

take the Yearly interest rate,
divide by the 365 to get the daily rate
find the number of days between now and the last calculation
multiply the principal daily interest rate number of days:

UPDATE mytable SET interestDue=DATEDIFF(lastCalculation,CURRENT_DATE())(interestRate/365)principalAmount

You can combine some updates into one big update:

UPDATE mytable SET interestDue=DATEDIFF(lastCalculation,CURRENT_DATE())(interestRate/365)principalAmount,
lastupdate = CURRENT_DATE(),
total = total+thisweek,
tax=total*.06

To make things really intersting you can do if-then-else type work on the fly:

Suppose you wanted to update a column in your table to reflect the values of certain fields

Like:
Dear Mister Jones, You owe us $5.00.
Dear Ms Smith, You have no balance.
Dear Ms Black, You owe us $567.33.

Etc.

You can use IF constructions:
SELECT IF(customerType='M','Mister','Ms.') FROM mytable.

You can concat strings:

SELECT CONCAT ('You have a balance due of $',balancedue,'.')

You can mix these functions and use them in an update:

UPDATE mytable
SET messageText= CONCAT('Dear ',
IF(customerType='M','Mister','Ms.'),
lastName,
',',
IF(balanceDue>0,
CONCAT('You owe us $',balanceDue,'.'),
'You have no balance.')
)

And so on.

Hope this gets you started.

[jonki]

Did you write all that just for me? I am very grateful. It has cleared up a lot. Hopefully I can do it now without any problems.

Cheers

[jonki]

Should this work?
I am not sure if I am doing this right.

$query2 = "update tenercoche_concesionarios set tarifa = if((max4 < 5) {tarifa = "2";}
					if ((5 <= max4) AND (max4 < 10)) {tarifa = "1.8";}
					if ((10 <= max4) AND (max4 < 20)) {tarifa = "1.5";}
					if ((20 <= max4) AND (max4 < 50)) {tarifa = "1.2";}
					if ((50 <= max4) AND (max4 < 80)) {tarifa = "1.1";}
					if (80 <= max4) {max4 = "1";}"
		

Thanks

[nemonoman]

You're on the right track but your syntax is wrong.

From the mysql manual

IF(expr1,expr2,expr3)
If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL) then IF() returns expr2, else it returns expr3. IF() returns a numeric or string value, depending on the context in which it is used:
mysql> SELECT IF(1>2,2,3);
-> 3
mysql> SELECT IF(1<2,'yes','no');
-> 'yes'
mysql> SELECT IF(STRCMP('test','test1'),'no','yes');
-> 'no'

in other words: always a test with 2 possible results

ONE of those 2 results could be another test

IF(A>5,'It's greater than five',IF(A=5,'A is equal to five','A is less than 5'))

You can keep nesting...in your example code, you'd need a lot of nested ifs.

There's another construct, however: CASE.

From the mysql manual

CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END
The first version returns the result where value=compare-value. The second version returns the result for the first condition, which is true. If there was no matching result value, then the result after ELSE is returned. If there is no ELSE part then NULL is returned:
mysql> SELECT CASE 1 WHEN 1 THEN "one"
WHEN 2 THEN "two" ELSE "more" END;
-> "one"
mysql> SELECT CASE WHEN 1>0 THEN "true" ELSE "false" END;
-> "true"
mysql> SELECT CASE BINARY "B" WHEN "a" THEN 1 WHEN "b" THEN 2 END;
-> NULL

Something like:

UPDATE tenercoche_concesionarios SET tarifa = CASE(max4 < 5 THEN "2", (5 <= max4 AND max4 < 10) THEN "1.8",

etc...

Yes I wrote that all for you. All good deeds balance out in the end.

[jonki]

Many many thanks. I don't know how to repay you - I hope you are right that all good deeds come around.
.....Good Karma thingy.....

(I have been searching for ages for a MySQL manual like the one you quoted from - could you give me the link? - Maybe I won't have to bother you so much in the future .....)

Cheers

Jon

[nemonoman]

mysql manual download

online manual

See especially the section on:
FUNCTIONS AND OPERATORS (chapter 13)