query syntax problem
Moderator: General Moderators
query syntax problem
couting number of rows
i know mysql_num_rows is use to count the rows
i have some data in the table ...a user can have many entries(ie userx can have any number of records or entry)
i am trying to count the number of entries for all users in whole table and then sort them in descending order(according to the record numbers they have) and i want to limit result to first five user(highest-second higest...so on)..
it sound like simple but what is actual mysql query
please help
thanks
i know mysql_num_rows is use to count the rows
i have some data in the table ...a user can have many entries(ie userx can have any number of records or entry)
i am trying to count the number of entries for all users in whole table and then sort them in descending order(according to the record numbers they have) and i want to limit result to first five user(highest-second higest...so on)..
it sound like simple but what is actual mysql query
please help
thanks
u mis understood the questionBadgerC82 wrote:SELECT * FROM table ORDER BY yourfield DESC LIMIT 0,5
that way it will be sorted on order of value of the filed "yourfiled" ...i am talking about number of rows....ok user 1 has 5 entries ,user 2 has 3 rows.....users3 has 7 rows so...
user3
user1
user2
so that way may be wont solve problem
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
Code: Select all
SELECT COUNT(id_field) AS row_count FROM table;that way may be only number will return ..Everah wrote:Code: Select all
SELECT COUNT(id_field) AS row_count FROM table;
but i am wanting to display details of each data
user3 7
user2 5
user1 3
...so on
may be this is what i need
SELECT user, COUNT(*) AS numEntries FROM table GROUP BY user ORDER BY numEntries DESC LIMIT 5
i have not tested i am testing it thanks
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
Code: Select all
SELECT user_id, COUNT(entry_field) AS entry_count FROM entries GROUP BY user_id ORDER BY entry_count DESC LIMIT 0, 5;database query
i have three table
players(players_id,nation,name,gref)//gref is same as player_id of goals
goals (player_id,game_id)
results(game_id,team1,team2,score1,score2)//score1 is score by team1..
in the player profile page i am using get variable
what i want to in player profile page display how many goals player has scored and in which games ...
if the player has scored more than two goals ..rather than two records only one should be displayed and number 2 should be displayed
it should be like this...
here is player profile
goals by this player(all the goals by this player)
game_id team1name v/s team2name goals finalscore
how know three table should be joined but how i could join only two tables better if other than cross join
thanks
any idea
players(players_id,nation,name,gref)//gref is same as player_id of goals
goals (player_id,game_id)
results(game_id,team1,team2,score1,score2)//score1 is score by team1..
in the player profile page i am using get variable
what i want to in player profile page display how many goals player has scored and in which games ...
if the player has scored more than two goals ..rather than two records only one should be displayed and number 2 should be displayed
it should be like this...
here is player profile
goals by this player(all the goals by this player)
game_id team1name v/s team2name goals finalscore
how know three table should be joined but how i could join only two tables better if other than cross join
thanks
any idea
- dibyendrah
- Forum Contributor
- Posts: 491
- Joined: Wed Oct 19, 2005 5:14 am
- Location: Nepal
- Contact:
To know the individual goal by the users, you need to add one more field in the goals table (player_id, game_id, num_goal) . The result will only show the number of goals by both opponents but will not show the number of goals done by individual player.
if you alter the table and add the num_goals, you can do like this :
Please try this . This might help you.
Cheers,
Dibyendra
if you alter the table and add the num_goals, you can do like this :
Code: Select all
select num_goals as NUM_GOALS from players, goals, results where player.player_id = goals.player_id AND results.game_id = goals.game_id WHERE player.player_id = SOME_PLAYER_ID
Cheers,
Dibyendra
Last edited by dibyendrah on Mon May 22, 2006 11:33 pm, edited 1 time in total.
thanks for reply but i am trying to show details of all goals of individual player (not team)dibyendrah wrote:To know the individual goal by the users, you need to add one more field in the goals table (platyer_id, game_id, num_goal) . The result will only show the number of goals by both opponents but will not show the number of goals done by individual player.
if you alter the table and add the num_goals, you can do like this :Please try this . This might help you.Code: Select all
select num_goals as NUM_GOALS from players, goals, results where player.player_id = goals.player_id AND results.game_id = goals.game_id WHERE player.player_id = SOME_PLAYER_ID
Cheers,
Dibyendra
ok if profile of ronaldo is being displayed
i want to display how many goals ronaldo has done in this world cup(it will inserted in goal table)
with details of each game
goal 1 team1/team2
goal2 team/team2...(this will be links to details results page)
and may be i can display total goal by a player counting number of id for player_id of roandlo...
thanks for help
may be if u will be kind enough for this also may be will be better...
- dibyendrah
- Forum Contributor
- Posts: 491
- Joined: Wed Oct 19, 2005 5:14 am
- Location: Nepal
- Contact:
The solution is for knowing how many goals player has scored in specific game. If you want to know how many goals the player has scored in specfic event then you have to re-design the table. I'll come up with solution soon. I need to think about table design and I'll come back to you.
Dibyendra
Dibyendra
Last edited by dibyendrah on Mon May 22, 2006 11:39 pm, edited 1 time in total.
this doesnot seems to be clean coding but this is some where near to solution but i still feel that if ur table design will bet better than i am open to that solution as well
thanks for help
Code: Select all
....................
$query1 = "SELECT goals.player_id,goals.game_id FROM goals,players WHERE (goals.player_id=$gref)";
$result1 = mysql_query ($query1);
while ($row = mysql_fetch_array ($result1, MYSQL_ASSOC)) {
$game=$row['game_id'];
echo " <tr>
<td align=\"center\" ><b>{$row['game_id']}</b></td>
</tr>\n";
$query2 = "SELECT team1,team2,dattim FROM results where game_id=$game";
echo '<table border="0" width="50%" cellspacing="3" cellpadding="2" align="center" bgcolor="#EFEFEF">
<tr>
<td align="left" width="10%" bgcolor="#00659C"></td>
<td align="left" width="40%" bgcolor="#00659C"></td>
</tr>';
$result2 = mysql_query ($query2);
while ($row = mysql_fetch_array ($result2, MYSQL_ASSOC)) {
echo " <tr>
<td align=\"center\" ><b>{$row['team1']}</b></td>
<td align=\"center\" ><b>{$row['team2']}</b></td>
</tr>\n";
} // End of while loop.
echo '</table>';
..........- dibyendrah
- Forum Contributor
- Posts: 491
- Joined: Wed Oct 19, 2005 5:14 am
- Location: Nepal
- Contact:
goals(goals_id,player_id,game_id)dibyendrah wrote:will you please show me the table design so that I can modify as per your requirement.
players(player_id,name,country,clubs,profile,goalref)
results(game_id,team1,team2,score1,score2,foul1,foul2,penalty1.....,summary)
Here in goal table i have made a drop down menu that list all player from table players and add player_id to goals
in players goalref is kept to link goals and player table
i insert goalref id in palyer table and that id is inserted to goals table as player_id
(doing so bcoz i dont have details of all 32x22 player playing...)
may be that code can tell many things thanks
- dibyendrah
- Forum Contributor
- Posts: 491
- Joined: Wed Oct 19, 2005 5:14 am
- Location: Nepal
- Contact: