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 ;
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

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
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