Page 1 of 1
query syntax problem
Posted: Fri May 19, 2006 10:55 am
by rami
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
Posted: Fri May 19, 2006 10:59 am
by BadgerC82
SELECT * FROM table ORDER BY yourfield DESC LIMIT 0,5
Posted: Fri May 19, 2006 12:08 pm
by rami
BadgerC82 wrote:SELECT * FROM table ORDER BY yourfield DESC LIMIT 0,5
u mis understood the question
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
Posted: Fri May 19, 2006 12:10 pm
by RobertGonzalez
Code: Select all
SELECT COUNT(id_field) AS row_count FROM table;
Posted: Fri May 19, 2006 12:25 pm
by rami
Everah wrote:Code: Select all
SELECT COUNT(id_field) AS row_count FROM table;
that way may be only number will return ..
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
Posted: Fri May 19, 2006 12:41 pm
by RobertGonzalez
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;
This should return a result of COUNT number of entries grouped by user id limited to the first 5 records in descending order. I think....

Posted: Sat May 20, 2006 8:17 am
by rami
double posted so edited by writer
database query
Posted: Sat May 20, 2006 8:18 am
by rami
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
Posted: Sun May 21, 2006 4:28 am
by dibyendrah
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 :
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
Please try this . This might help you.
Cheers,
Dibyendra
Posted: Mon May 22, 2006 1:45 am
by rami
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 :
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
Please try this . This might help you.
Cheers,
Dibyendra
thanks for reply but i am trying to show details of all goals of individual player (not team)
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...
Posted: Mon May 22, 2006 4:58 am
by dibyendrah
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
Posted: Mon May 22, 2006 11:43 am
by rami
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
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>';
..........
thanks for help
Posted: Mon May 22, 2006 11:47 pm
by dibyendrah
will you please show me the table design so that I can modify as per your requirement.
Posted: Tue May 23, 2006 4:29 am
by rami
dibyendrah wrote:will you please show me the table design so that I can modify as per your requirement.
goals(goals_id,player_id,game_id)
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
Posted: Thu May 25, 2006 12:11 am
by dibyendrah
rami thanks for the table design. Sorry for my late reply. I'll try to re-design the table from my best option. I'll get back to you when I come to the result.