I have one problem with php and mysql. I have 3 tables (Users, User_status and Analysis) and want to display users and profit in a php table order by profit.

So I wrote this code:

$tipster = mysql_query("SELECT Analysis.Profit, Users.Username, User_status.id
FROM Analysis, Users
INNER JOIN User.status
ON Users.User_status_name=User_status.user_status
WHERE Users.User_status_name=User_status.user_status
AND User_status.id>=3
ORDER BY Profit
"); 
while ($row = mysql_fetch_array($tipster)) {
	$sel_tipster=$row['Username'];
	echo "<tr>";
	echo "<td>";
	echo $row['Username'];
	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");
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>";
	echo "</tr>";
	}
	}

but I get multiple results. I have 4 users in mysql, but in php table I have 6x times these users. If I delete Analysis table from first select query than I've got 4 users.

I want to have only these users than are in db and order by profit. What is wrong with this code?

Table Users: id, Username, User_status_name
Table User_status: id, user_status
User_status_name form Users and user_status from User_status are in relation
Table Analysis: id_an, Profit, Result, Tipster

Recommended Answers

All 5 Replies

I think there is problem with the relation between the tables. If you want to join analysis table with users table then there must be some common column between them. but in analysis table there is no column related to users.

So have some link between these two tables and join on the basis of this columns in your query. Also you may use aggregate functions like sum, average for getting your result and to remove redundant rows from query result.

Member Avatar for rajarajan2017

List your table structure and some of the records inside them.

I forgot to write that Users table is in relation with Analysis table where Users.Username=Analysis.Tipster.

So I've got:

Users
id Username User_status_name
1  boskor   Admin    
2  slopart  Tipster

User_status
id user_status
1  Public
2  Member
3  Tipster
4  Admin

Analysis
id Name           Pick Odds  Tipster Result Stake Profit
1  Boston-Chicago 1    1.9   boskor  85:80   5     +4.5

This is example of my 3 tables. I want to have in php table all users>=3 (user_status(that means: Tipster and Admin)).

I want such table in php:

Tipster Profit
boskor   +4.5
slopart   0

Slopart doesn't have any analysis, so he will be in php table and his profit will be 0.

SELECT Analysis.Profit, Users.Username, User_status.id 
	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>=3
ORDER BY Profit

Thank you. It works.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.