Page 1 of 1

mysql help please!

Posted: Sun Feb 05, 2006 6:32 pm
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

Posted: Sun Feb 05, 2006 6:39 pm
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.

Posted: Sun Feb 05, 2006 6:48 pm
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.

Posted: Sun Feb 05, 2006 6:50 pm
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

Posted: Sun Feb 05, 2006 7:01 pm
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.

Posted: Sun Feb 05, 2006 7:29 pm
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

Posted: Sun Feb 05, 2006 7:36 pm
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. ;)

Posted: Sun Feb 05, 2006 9:33 pm
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

Posted: Sun Feb 05, 2006 9:37 pm
by feyd
my previous post still stands.

Posted: Sun Feb 05, 2006 9:50 pm
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.

Posted: Mon Feb 06, 2006 2:51 am
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