easier way, that works needed.

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
zyklon
Forum Commoner
Posts: 49
Joined: Mon Jul 31, 2006 7:14 pm
Location: MA, USA

easier way, that works needed.

Post 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.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

hint:

Code: Select all

SELECT clan, Sum(power) FROM `user` GROUP BY clan
User avatar
zyklon
Forum Commoner
Posts: 49
Joined: Mon Jul 31, 2006 7:14 pm
Location: MA, USA

Post by zyklon »

thank you, i will try it out. :)
bdlang
Forum Contributor
Posts: 395
Joined: Tue May 16, 2006 8:46 pm
Location: Ventura, CA US

Post 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.
User avatar
zyklon
Forum Commoner
Posts: 49
Joined: Mon Jul 31, 2006 7:14 pm
Location: MA, USA

Post 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.
bdlang
Forum Contributor
Posts: 395
Joined: Tue May 16, 2006 8:46 pm
Location: Ventura, CA US

Post 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. :)
User avatar
zyklon
Forum Commoner
Posts: 49
Joined: Mon Jul 31, 2006 7:14 pm
Location: MA, USA

Post 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.
Post Reply