mysql help please!
Moderator: General Moderators
-
jasondavis
- Forum Commoner
- Posts: 60
- Joined: Sat Feb 04, 2006 5:35 pm
mysql help please!
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
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
- nickman013
- Forum Regular
- Posts: 764
- Joined: Sun Aug 14, 2005 12:02 am
- Location: Long Island, New York
-
jasondavis
- Forum Commoner
- Posts: 60
- Joined: Sat Feb 04, 2006 5:35 pm
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 ;

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

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 ;

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

- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
Code: Select all
SELECT COUNT(*) FROM `aboard` WHERE `intMSUserId` = '12341234'Code: Select all
SELECT COUNT(*) FROM `aboard` GROUP BY `intMSUserId`-
jasondavis
- Forum Commoner
- Posts: 60
- Joined: Sat Feb 04, 2006 5:35 pm
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
Code: Select all
SELECT `intMSUserId`, COUNT(`intMSUserId`) AS `trains` FROM `aboard` GROUP BY `intMSUserId`-
jasondavis
- Forum Commoner
- Posts: 60
- Joined: Sat Feb 04, 2006 5:35 pm
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.'';
?>- nickman013
- Forum Regular
- Posts: 764
- Joined: Sun Aug 14, 2005 12:02 am
- Location: Long Island, New York
add
to the SQL query. I dont know if that will make the top 5 but it will filter so it only shows 5.
Code: Select all
LIMIT 0 , 5- raghavan20
- DevNet Resident
- Posts: 1451
- Joined: Sat Jun 11, 2005 6:57 am
- Location: London, UK
- Contact:
feyd wrote: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`
Code: Select all
SELECT `intMSUserId`, COUNT(`intMSUserId`) AS `trains`
FROM `aboard`
GROUP BY `intMSUserId`
order by `trains` desc
limit 0, 5