Member Avatar for Boskor

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

  • 3 Contributors
  • 5 Replies
  • 292 Views
  • 19 Hours Discussion Span
  • Latest Post Latest Post by Boskor
Member Avatar for urtrivedi

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.

Edited by urtrivedi because: n/a
Member Avatar for rajarajan2017

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

Member Avatar for Boskor

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.

Member Avatar for urtrivedi
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
Edited by urtrivedi because: n/a
Member Avatar for Boskor

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.