2

Recently started working with MySQL and PHP so my setup is still a bit clunky most likely but here is how my 2 tables look right now:

Table 1 named clanlist

clanlist

Table 2 named clanusers

clanusers

What I need to do is check the number of users for each "clan" and show that number in the clanlist in the members column.

So what it should do is count the number of users who have "1" in their name and update the amount in table "clanlist". So if 3 users have "1" in the "clanname" column it should update the clanlist table and show 3 in the members column. I find it a bit hard to explain and I don't know if there are easier ways to do this so feel free to help me out with anything.

I would like to add that I already tried doing something similar in php code but it was unreliable and it required me to include the php code every time a user opened a webpage. Which obviously causes slower loadtimes for the page.

And one more thing, I saw something about the COUNT function which you can use in a query. But the code was really complex and I couldn't get it to work at all.

Created this in paint real quick: brief description

The column members has to amount of rows in the clanusers table which have the clan_id from clanlist in it.

  • What connects the tables? clanname? Not clan_id? And why might there be a "1" in a VARCHAR(50) column? – Rick James Mar 21 '15 at 4:34
  • @RickJames I edited it, I didn't explain it very well so I just created a picture in paint to show it better. – Crecket Mar 21 '15 at 10:51
0

How about this?

UPDATE clanlist AS c SET members = 
(
  SELECT COUNT(*) FROM clanusers AS u WHERE u.clanname = c.clanname
)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.