CODEWITHSUNDEEP
phpmysql
HungryDB
HungryDB

Reputation: 575

complex mysql query need result sorted

I have 3 mysql tables. I want to display leaderboard for team in descending order of d_money for particular day (like day 1, day 2, day 3.)

user(u_id(p),name)
team(t_id(p),u_id(f),t_name,t_money,days_money) and 
history(t_id(f),day,d_money).

First i collected all t_id's in $tid_arr. Then for each t_id, i wrote query to get t_name and its days money.(for particular day. Here - day 1). It displays the result. But i want the result sorted (descending order of d_money). But i couldn't find the soultion.

 $query = $con->prepare("SELECT t_id FROM team");   
$query->execute();

$tid_arr = $query->fetchAll();



 echo "<table border='1'>";

foreach($tid_arr as $tid)
 {
    $que = $con->prepare("SELECT d_money, t_name FROM team, history WHERE history.t_id=$tid['t_id'] AND team.t_id=history.t_id` AND history.day='1'");
                     $que->execute();


                               while($info = $que->fetch(PDO::FETCH_NUM))
                              {

                                     echo "<tr>";
                                     echo  "<td>".$info[0]."</td>";
                                     echo  "<td>".$info[1]."</td>";
                                     echo "</tr>";
              }
}
                echo "</table>";

Upvotes: 2

Views: 64

Answers (2)

M.Nagy
M.Nagy

Reputation: 21

Add a Join and Order By clause to your SQL statement to be 

echo "<table border='1'>";
$que = $con->prepare("SELECT T.t_id, H.d_money, T.t_name
                  FROM team T INNER JOIN history H ON T.t_id=H.t_id
                  WHERE H.day='1'
                  ORDER BY d_money DESC");
$que->execute();

while ($info = $que->fetch(PDO::FETCH_NUM)) {
 echo "<tr>";
 echo  "<td>".$info[0]."</td>";
 echo  "<td>".$info[1]."</td>";
 echo "</tr>";
}

echo "</table>";

Upvotes: 0

Raptor
Raptor

Reputation: 54212

If you want to order by d_money then t_id, you can use the following query. The 1st query is unnecessary and should be removed.

SELECT d_money, t_name FROM team a 
LEFT JOIN history b ON a.t_id=b.t_id
WHERE history.day='1' 
ORDER BY d_money DESC, t_id DESC

p.s. user table is not used ?

Upvotes: 2

Related Questions