0

Hello

I have another problem. Here is previous thread: http://www.daniweb.com/forums/post1237196.html#post1237196

and my code:

$tipster = mysql_query("SELECT SUM(Analysis.Profit), Users.id, Users.Country_name, Users.Username, User_status.id_status
FROM Users
INNER JOIN User_status 
ON Users.User_status_name=User_status.user_status
left outer JOIN Analysis 
ON Users.Username=Analysis.Tipster
WHERE User_status.id_status>=3
GROUP BY Tipster
ORDER BY SUM(Analysis.Profit) DESC"); 
while ($row = mysql_fetch_array($tipster)) {
	$sel_tipster=$row['Username'];
	echo "<tr>";
	echo "<td>";
	echo $sel_tipster;
	echo "</td>";
$profit = mysql_query("SELECT SUM(Analysis.Profit), SUM(Analysis.Stake)
FROM Analysis
WHERE Analysis.Tipster='$sel_tipster'
AND Result>0
GROUP BY Tipster
ORDER BY Profit");
echo "<td>";
if (mysql_num_rows($profit)==0) {
	echo 0; }
while ($row = mysql_fetch_array($profit)) {
	$sel_profit = $row['SUM(Analysis.Profit)'];
	echo $sel_profit;
	echo "</td>";
	}

Now I have all users >= 3 in php table, but if user didn't write any analysis (doesn't exists in table Analysis) he has 0 in php table, but he is at the end of table.

Example:

We have 3 users, one have +5, other -5 and third 0, because he didn't write any analysis yet.

My php table is than:

tipster   profit
tipster 1  +5
tipster 2  -5
tipster 3   0

but I want to have this php table:

tipster   profit
tipster 1  +5
tipster 3   0
tipster 2  -5

Have you get any idea how to write a code?

3
Contributors
9
Replies
10
Views
7 Years
Discussion Span
Last Post by Boskor
0

at the end of query write

order by profit desc

It didn't work. Actually I want to have order by SUM(Profit) tried, but I also in second query with this, but tipster with 0 (if he didn't post analysis) is always on last place. All other tipsters are well ordered by SUM(Profit) (that works fine).

0

What is the purpose of you should have the table as +5,0,-5?

I want to ordered by Profit (from max (+) to min (-) and 0 is between + and -). That is stats to showed the best and the worst tipster.

0

order by profit desc will give what you want, but yours not working, then the query may be wrong. Execute your second query properly in phpadmin and then implement here.

Edited by rajarajan07: n/a

0

which query is not working properly first or second.

or

why you have used two quries

I don't know :/.

Now I have just 1 query:

$tipster = mysql_query("SELECT SUM(Analysis.Profit), Users.id, Users.Country_name, Users.Username, User_status.id_status
FROM Users
INNER JOIN User_status 
ON Users.User_status_name=User_status.user_status
left outer JOIN Analysis 
ON Users.Username=Analysis.Tipster
WHERE User_status.id_status>=3
GROUP BY Tipster
ORDER BY SUM(Analysis.Profit) DESC"); 
while ($row = mysql_fetch_array($tipster)) {
	$sel_tipster=$row['Username'];
	$sel_tipster_id=$row['id'];
	$sel_profit = $row['SUM(Analysis.Profit)'];
	echo "<tr>";
	echo "<td>";
	echo $sel_tipster;
	echo "</td>";
	echo "<td>";
if (!$sel_profit) {
	echo 0; }
	else {
	echo $sel_profit; } 
	echo "</td>";

But still I have such table in php:

tipster   profit
tipster_3  +5
tipster_4  -3
tipster_1  -5
tipster_2   0

This is only example. I don't know how to write such table in php:

tipster   profit
tipster_3  +5
tipster_2   0
tipster_4  -3
tipster_1  -5
0

use ifnull function around profit column.

Also change line 13 to
$sel_profit = $row;

"SELECT SUM(ifnull(Analysis.Profit,0)) profit, Users.id, Users.Country_name, Users.Username, User_status.id_statusFROM UsersINNER JOIN User_status ON Users.User_status_name=User_status.user_statusleft outer JOIN Analysis ON Users.Username=Analysis.TipsterWHERE User_status.id_status>=3GROUP BY TipsterORDER BY SUM(ifnull(Analysis.Profit,0)) DESC"

Edited by urtrivedi: n/a

0

use ifnull function around profit column.

Also change line 13 to
$sel_profit = $row;

"SELECT SUM(ifnull(Analysis.Profit,0)) profit, Users.id, Users.Country_name, Users.Username, User_status.id_statusFROM UsersINNER JOIN User_status ON Users.User_status_name=User_status.user_statusleft outer JOIN Analysis ON Users.Username=Analysis.TipsterWHERE User_status.id_status>=3GROUP BY TipsterORDER BY SUM(ifnull(Analysis.Profit,0)) DESC"

It works now. Thank you again.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.