row doesn't exists mysql echo number 0 php table

[Boskor]

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?

[urtrivedi]

at the end of query write

order by profit desc

[Boskor]

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).

[rajarajan2017]

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

[Boskor]

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.

[urtrivedi]

which query is not working properly first or second.

or

why you have used two quries

[rajarajan2017]

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.

[Boskor]

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

[urtrivedi]

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"

[Boskor]

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.