mysql help please!

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
jasondavis
Forum Commoner
Posts: 60
Joined: Sat Feb 04, 2006 5:35 pm

mysql help please!

Post by jasondavis »

Table = aboard
fields= intTrainId intMSUserId
I need to display the top 10 of intMSUserId in the intTrainId field

for instance my intMSUserId is 10021628
I am in intTrainId 1,2,3,4,5 so for me it would show I was in 5 times

now lets say your intMSUserId = 12345765 and you are in intTrainId 1,5,9
it would show you as being in 3
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

I think we're going to need a better understanding of how your table(s) are laid out.

And please work on using more descriptive titles for threads.
User avatar
nickman013
Forum Regular
Posts: 764
Joined: Sun Aug 14, 2005 12:02 am
Location: Long Island, New York

Post by nickman013 »

yeah we need a better explaination of what you need help with, also just click export and get the SQL code so we can see how your DB is set up.
jasondavis
Forum Commoner
Posts: 60
Joined: Sat Feb 04, 2006 5:35 pm

Post by jasondavis »

sorry its hard for me to expalin
basicly the values intMSUserId are in the values of intTrainId several times so I would like to count the times.
this is a myspace train script so for instance I would like to count the number of times I was in a train
heres is an image how my table is layed out though

CREATE TABLE `aboard` (
`intId` int(10) unsigned NOT NULL auto_increment,
`intTrainId` int(11) default NULL,
`intMSUserId` int(11) default NULL,
PRIMARY KEY (`intId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=132340 ;


Image

now you see where intTrainId changes to 2 which is train number 2 and I am in it again sofar it would count me as being in 2 trains
Image
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Code: Select all

SELECT COUNT(*) FROM `aboard` WHERE `intMSUserId` = '12341234'
for a single user

Code: Select all

SELECT COUNT(*) FROM `aboard` GROUP BY `intMSUserId`
for all users.
jasondavis
Forum Commoner
Posts: 60
Joined: Sat Feb 04, 2006 5:35 pm

Post by jasondavis »

thanks for the help so far can you help me display this like;
for the top 5

User ID 1232 6 trains
User ID 6445 55 trains
User ID 4545 322 trains
User ID 4646 4 trains
User ID 4564 645 trains
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Code: Select all

SELECT `intMSUserId`, COUNT(`intMSUserId`) AS `trains` FROM `aboard` GROUP BY `intMSUserId`
you can hopefully figure out how to do it from there. ;)
jasondavis
Forum Commoner
Posts: 60
Joined: Sat Feb 04, 2006 5:35 pm

Post by jasondavis »

Code: Select all

<?php
include "inc/globals.php";
include "inc/db.php";

$sql="SELECT COUNT(*) FROM `aboard` WHERE `intMSUserId` = '10021628'"; 
$result = mysql_fetch_row(mysql_query($sql));
$totaltrains = intval($result[0]);
echo ''.$totaltrains.'';
?>
this code is almost working it will show that I user id 10021628 has been in 3396 rounds of the train but I need for it to show not only me but the top 5 people as in the 5 people that have been in the Most train rounds
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

my previous post still stands.
User avatar
nickman013
Forum Regular
Posts: 764
Joined: Sun Aug 14, 2005 12:02 am
Location: Long Island, New York

Post by nickman013 »

add

Code: Select all

LIMIT 0 , 5
to the SQL query. I dont know if that will make the top 5 but it will filter so it only shows 5.
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

feyd wrote:

Code: Select all

SELECT `intMSUserId`, COUNT(`intMSUserId`) AS `trains` FROM `aboard` GROUP BY `intMSUserId`
you can hopefully figure out how to do it from there. ;)

Code: Select all

SELECT `intMSUserId`, COUNT(`intMSUserId`) AS `trains` 
FROM `aboard` 
GROUP BY `intMSUserId`
order by `trains` desc
limit 0, 5
Post Reply