query syntax problem

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
rami
Forum Contributor
Posts: 217
Joined: Thu Sep 15, 2005 8:55 am

query syntax problem

Post 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
BadgerC82
Forum Commoner
Posts: 25
Joined: Tue Feb 07, 2006 6:53 am

Post by BadgerC82 »

SELECT * FROM table ORDER BY yourfield DESC LIMIT 0,5
rami
Forum Contributor
Posts: 217
Joined: Thu Sep 15, 2005 8:55 am

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Code: Select all

SELECT COUNT(id_field) AS row_count FROM table;
rami
Forum Contributor
Posts: 217
Joined: Thu Sep 15, 2005 8:55 am

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.... :wink:
rami
Forum Contributor
Posts: 217
Joined: Thu Sep 15, 2005 8:55 am

Post by rami »

double posted so edited by writer
Last edited by rami on Sat May 20, 2006 8:19 am, edited 1 time in total.
rami
Forum Contributor
Posts: 217
Joined: Thu Sep 15, 2005 8:55 am

database query

Post 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
User avatar
dibyendrah
Forum Contributor
Posts: 491
Joined: Wed Oct 19, 2005 5:14 am
Location: Nepal
Contact:

Post 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
Last edited by dibyendrah on Mon May 22, 2006 11:33 pm, edited 1 time in total.
rami
Forum Contributor
Posts: 217
Joined: Thu Sep 15, 2005 8:55 am

Post 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...
User avatar
dibyendrah
Forum Contributor
Posts: 491
Joined: Wed Oct 19, 2005 5:14 am
Location: Nepal
Contact:

Post 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
Last edited by dibyendrah on Mon May 22, 2006 11:39 pm, edited 1 time in total.
rami
Forum Contributor
Posts: 217
Joined: Thu Sep 15, 2005 8:55 am

Post 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
User avatar
dibyendrah
Forum Contributor
Posts: 491
Joined: Wed Oct 19, 2005 5:14 am
Location: Nepal
Contact:

Post by dibyendrah »

will you please show me the table design so that I can modify as per your requirement.
rami
Forum Contributor
Posts: 217
Joined: Thu Sep 15, 2005 8:55 am

Post 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
User avatar
dibyendrah
Forum Contributor
Posts: 491
Joined: Wed Oct 19, 2005 5:14 am
Location: Nepal
Contact:

Post 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.
Post Reply