CODEWITHSUNDEEP
phpmysqlsql
Michael L Watson
Michael L Watson

Reputation: 980

Nested queries syntax improvement

What's the correct syntax to achieve this using 1 query instead of 2?

$result = mysql_query(SELECT * FROM users);

while($row = mysql_fetch_array($result)){
    $result = mysql_query(SELECT SUM(balance) FROM users_account WHERE uid=$row[id]);
}

Upvotes: 2

Views: 107

Answers (3)

Bajrang
Bajrang

Reputation: 8629

This will return all records form user_account table with user info :-

    SELECT SUM(ua.balance),u.*,ua.* FROM users_account ua
    left join users u on ua.users_account=u.id
    GROUP BY u.id

Upvotes: 0

dani herrera
dani herrera

Reputation: 51705

You should inner join both tables and group by user table. You can add more columns in SELECT clause, remember to add also same columns to GROUP BY clause to get a standard SQL statement.

$query = " SELECT u.uid, SUM(a.balance) 
           FROM users_account a
           INNER JOIN users  u
           ON u.uid = a.uid
           GROUP BY u.uid";
$result = mysql_query($query);
while($row = mysql_fetch_array($result)){
     ...

Arrange $query concatenating strings if needed.

Upvotes: 1

djot
djot

Reputation: 2947

$result = mysql_query(SELECT a.*, b.SUM(balance) AS user_balance FROM users a, users_account b WHERE uid=$row[id]);

Upvotes: 0

Related Questions