Need help MySQL/PHP

[mikemastah]

I need help pulling stuff from my db I have a table which looks something like this:

 

usernameclanpower

PeteHeroes1600

CharlieSomeclan388

SteveHeroes1201

 

and to make it even harder in another table I've got an attack bonus (I can't combine them)

 

Clanbonus

Heroes100

Someclan60

 

now I need it to say:

 

Clan Nametotal Power

Heroes2901

Someclan448

 

How would I do this?

[Floydian]

Re: Need help MySQL/PHP

select (sum(t2.power) + t2.bonus) as total, t1.Clan from table_2 as t2

left join table_1 as t1 on t2.Clan = t1.Clan

 

t1 (table 1) is the table with the username in it

t2 (table 2) is the other table

you may need to put in a ---> group by t2.Clan <---

try it without that first, and see if it works, if you get a "mixing of group" type error, toss in the group by

that should do ya

[mikemastah]

Re: Need help MySQL/PHP

Whoa that's some SQL, could you explain what it does.. I mean I know what it does... but not all the details and I like details

select (sum(t2.power) + t2.bonus) as total, t1.Clan from table_2 as t2
   left join table_1 as t1 on t2.Clan = t1.Clan

so... (sum(t1.power) is the sum of all the power in t1 right..?

+ t2.bonus + the bonus from t2

as total, t1.Clan from table_2 as t2 ok.. this goes waaaaay too fast

left join table_1 as t1 on t2.Clan = t1.Clan ummm what does this mean..?

I'm not really good with SQL. I only know the basic SELECT,WHERE, UPDATE, INSERT stuff

[Floydian]

Re: Need help MySQL/PHP

First, looking over that sql, you'd definitely need to use that group by clause:

select (sum(t1.power) + t2.bonus) as total, t1.Clan from table_2 as t2

left join table_1 as t1 on t2.Clan = t1.Clan

group by t2.Clan

 

so... (sum(t1.power) is the sum of all the power in t1 right..?

Yes, and using that group by clause it then sums up each individual clan's power.

 

as total, t1.Clan from table_2 as t2 ok.. this goes waaaaay too fast

um?? lol

 

left join table_1 as t1 on t2.Clan = t1.Clan ummm what does this mean..?

You're selecting from table 1 and table 2. You have "from table 2" but to select from table 1 as well, you need to join that table to take 2. Somehow the table 2 in the from clause and the table 1 in the join clause must share somthing in common for which you will use to join them "on".

In this case, they share clan names.

Hope that helps. You should try to run the query. Can't hurt running a select query, even if it doesn't work. ;)

 

EDIT: I had the sum(t1.power) part mistakenly labeled as t2.power.

Kinda confusing to me labeling the tables t1 and t2... lol

[mikemastah]

Re: Need help MySQL/PHP

Ok what if I only want the data from 'heroes' where should I put the WHERE clause or won't that work?

never mind figured it out, works wonderful thanks.

[Floydian]

Re: Need help MySQL/PHP

You're welcome, glad that worked out ;)