php - How to output correct database row

Solution:

Your query should be written this way:

SELECT 
  u.id userid, 
  u.username username, 
  u.alliance allyid, 
  SUM(v.pop )   AS totalpop, 
  SUM(a.Aap ) AS totalpoint,
  COUNT(ua.alliance ) AS totalusers
FROM users u
INNER JOIN vdata    v ON v.owner     = u.userid
INNER JOIN alidata  a ON a.id        = u.allyid
INNER JOIN users   ua ON ua.alliance = u.allyid
WHERE users.alliance > 0
GROUP BY u.id,
         u.username, 
         u.alliance 
ORDER BY u.totalpoint DESC, 
         u.allyid ASC ;
Copy code

What I have done in this query:

IJOINed the tablesusers,vdata,alidata andusers again instead of these correlated subqueries that you used in your question. Note that: I joined the tableusers one more times to get those users that hasalliance = u.allyid but with a different alias. ThenGROUP BY, with aggregate functions in the same query.

You might also need to useLEFT JOIN instead ofINNER JOIN in case you want to include those unmtached data, i.e those users that has no entries in the others tables for example.

Update 1

To test the query against MySQL directly, and since you are using WAMP. Like in the following steps:

• Ensure that the WAMP server is running:

• From the task par open the phpMyAdmin:

• Select your database that you're working with:

• Navigate to SQL Tab:

• Paste your query in the window:

• Then press the button Go.

Update 1

Try this instead:

SELECT 
  u.id               AS userid, 
  u.username         AS username, 
  u.{-code-18}         AS {-code-14}, 
  SUM(v.pop)         AS totalpop, 
  SUM(a.Aap)         AS totalpoint,
  COUNT(ua.{-code-18}) AS totalusers
FROM s1_users u
LEFT JOIN s1_vdata    v ON v.owner     = u.id
LEFT JOIN s1_alidata  a ON a.id        = u.{-code-18}
LEFT JOIN s1_users   ua ON ua.{-code-18} = u.{-code-18}
WHERE u.{-code-18} > 0 
GROUP BY u.id, 
         u.username, 
         u.{-code-18};
Copy code

SQL Fiddle Demo

This will give you:

| USERID |    USERNAME | ALLYID | TOTALPOP | TOTALPOINT | TOTALUSERS |
Copy code

Note that: I couldn't find a column{-code-14} in thes1_users table, so I joined the tables1_users withs1_alidata with the column{-code-18} ands1_users with itself with the same field{-code-18}. Not sure if this is right or not.

{-code-42}ut there is a big problem in your tables' design. Your tables are .

{-code-27} FriendId {-code-57}
  1       1         0
  1       2         1
  1       3         0
  ...
  ...
  1       19        1
Copy code

• .

• .

• .

• , a great book for SQL {-code-42}asics.

• .

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.