Page 1 of 1
easier way, that works needed.
Posted: Sun Jun 17, 2007 12:16 pm
by zyklon
ok, i have this code:
Code: Select all
$max = mysql_fetch_array(mysql_query("SELECT MAX(id) as id FROM user"));
$maxu=$max['id'];
$max = mysql_fetch_array(mysql_query("SELECT MAX(cid) as id FROM claninf"));
$maxc=$max['id'];
for($j=1;$j<=$maxc;$j++){
$add=0;
for($i=1;$i<=$maxu;$i++){
$d=mysql_query("SELECT power from user where id='$i' and clan='$j'");
$add+=$d['power'];
}
mysql_query("UPDATE claninf set cpower='$add' where cid='$j'");
}
this is part of a mmo i am working on, its part of the ticker, and it is related to the clan system
i want it to take the power of all the users in each respective clan, add them up, and then make the clans power equal to them all added up.
but i cant figure out how to do it, in an easier way, and this part apparently isn't working as well. all the columns and rows, and all that are in existence.
Posted: Sun Jun 17, 2007 12:27 pm
by volka
hint:
Code: Select all
SELECT clan, Sum(power) FROM `user` GROUP BY clan
Posted: Sun Jun 17, 2007 12:41 pm
by zyklon
thank you, i will try it out.

Posted: Sun Jun 17, 2007 12:42 pm
by bdlang
Don't take this the wrong way, but this is the oddest combination of SQL and PHP I've seen. How long does that script take to run?
Where to begin.... you have some extremely inefficient code there, based on the fact that you have N records as calculated by the two MAX() statements. But that's not how it works; assuming the `id` and `cid` fields are auto_increment PK fields for those tables, you can have a MAX(id) value of 100000 and still only have 100 records depending on how many records have been deleted. So your initial logic is flawed regarding how many records you need to loop through. Next is the golden rule (well, my golden rule anyway) that a query should never be run in any type of loop, you should always use proper SQL to retrieve the resultset you want,not rely on PHP to loop through a bunch of similar queries. That's why I ask how long the script takes to run. You have a loop within a loop of N * N queries.
In editing this response I see volka has already provided a start (using SQL) to finding your solution.
Please keep in mind that you should really learn SQL before attempting to use it in your scripts. An analogy I like to use is building a car; the PHP script is your car and the SQL is the engine, what really powers it. You wouldn't spend hours of labor into the design of a car, installing custom leather upholstery, a wood panel dash, making the sheet metal as straight and perfect as possible, spraying on coats of gloss paint and then after all this loving care you put in a greasy smoky underpowered lawnmower engine, would you? Same thing with PHP/SQL.
Posted: Sun Jun 17, 2007 12:46 pm
by zyklon
i did that other code, in order to get what i was trying to do across. it wouldn't work. it was getting the point i wanted to do across. i knew i could do it in sql, i just didn't know how too.
Posted: Sun Jun 17, 2007 12:47 pm
by bdlang
zyklon wrote:i knew i could do it in sql, i just didn't know how too.
Completely understood, not a problem, just trying to be helpful.

Posted: Sun Jun 17, 2007 12:51 pm
by zyklon
so the line volka gave me, sums all them up.
what i want to do is sum them up, and then place them into the power of the clan in a different table.
clan in the user column is the clan id or cid.