DragonPrime - LoGD Resource Community
Welcome Guest
  • Good afternoon, Guest.
    Please log in, or register.
  • August 30, 2016, 01:45:54 PM
Home Forums News Links Downloads Login Register Advanced Search
* * *
DragonPrime Menu
Login
 
 
Resource Pages
IRC Channels
Search

Pages: [1]   Go Down
  Print  
Author Topic: most populated city sql.  (Read 3684 times)
0 Members and 1 Guest are viewing this topic.
sixf00t4
Mod God
*****
Offline Offline

Posts: 1916



View Profile WWW
« on: June 05, 2005, 01:49:04 PM »

surely there has to be a sql for loop that can be done to select the most populated city.  I just can't get it right.   I would think it would first have to go through and select all the city names, and then count where homecity==city name for all players.  ideas?
Logged

Iwpg
Guest
« Reply #1 on: June 05, 2005, 02:38:25 PM »

Code:
select location, count(*) as count from accounts group by location order by count desc limit 1
should do it.
Logged
Iwpg
Guest
« Reply #2 on: June 05, 2005, 02:55:29 PM »

Arg, on second thoughts, that won't work properly with people in the inn... be back in a moment....
Logged
Iwpg
Guest
« Reply #3 on: June 05, 2005, 03:12:05 PM »

OK:
Code:
$inn = getsetting("innname", LOCATION_INN);
$fields = getsetting("villagename", LOCATION_FIELDS);
$result = db_query("select case location when '".addslashes($inn)."' then '".addslashes($fields)."' else location end as location, count(*) as count from ".db_prefix("accounts")." group by case location when '".addslashes($inn)."' then '".addslashes($fields)."' else location end order by count desc");
$row = db_fetch_assoc($result);
$crowded = $row['location'];
should leave the result in $crowded.  Sorry for the confusion.  (This is assuming you want people in the Inn to count as being in the capital, BTW, if not the original query was fine. Smiley )
Logged
XChrisX
Global Moderator
Mod God
*****
Offline Offline

Posts: 4647

Be aware of the squirrel!


View Profile WWW
« Reply #4 on: June 05, 2005, 09:07:47 PM »

That would help for knowing where people are at the moment...

As far as aI have understood, sixfoot wants to know where people are born...

Code:
SELECT race, COUNT(race) FROM accounts GROUP BY race

Then in PHP you have to find out, which city goes for which race... Wink
Logged

Running for more than three years now:
Iwpg
Guest
« Reply #5 on: June 06, 2005, 05:20:56 AM »

Oh, right, misread the homecity part... in that case, how about
Code:
select p.value as city, count(*) as count from module_userprefs p where p.modulename = 'cities' and p.setting = 'homecity' group by p.value order by count desc limit 1
And that's my final answer, honest.
Logged
XChrisX
Global Moderator
Mod God
*****
Offline Offline

Posts: 4647

Be aware of the squirrel!


View Profile WWW
« Reply #6 on: June 06, 2005, 05:27:56 AM »

if you replace "count(*)" by "count(userid)" I'd be happy... Wink

Is not that much faster... But every little bit helps... Smiley
Logged

Running for more than three years now:
Kendaer
Guest
« Reply #7 on: June 06, 2005, 02:51:44 PM »

count(userid) and count(*) are not any different in speed to my knowledge Smiley
Logged
sixf00t4
Mod God
*****
Offline Offline

Posts: 1916



View Profile WWW
« Reply #8 on: June 07, 2005, 02:52:15 AM »

what's this p. stuff?

I'm taking this won't work :
Code:
   $sql="select p.value as city, count(*) as count from ".db_prefix("module_userprefs")." p where p.modulename = 'cities' and p.setting = 'homecity' group by p.value order by count desc";
    $result = db_query($sql) or die(db_error(LINK));
    $city=translate_inline("City");
    $pop=translate_inline("Population");
    rawoutput("<center><table><tr><td>$city</td><td>$pop</td></tr>");
    for ($i=0;$i<db_num_rows($result);$i++){    
        $row = db_fetch_assoc($result);
        rawoutput("<tr><td>$row['city']</td><td>$row['count']</td></tr>");
    }

i attached the module in progress.
Logged

XChrisX
Global Moderator
Mod God
*****
Offline Offline

Posts: 4647

Be aware of the squirrel!


View Profile WWW
« Reply #9 on: June 07, 2005, 03:01:29 AM »

What's the error message you are getting?
Logged

Running for more than three years now:
sixf00t4
Mod God
*****
Offline Offline

Posts: 1916



View Profile WWW
« Reply #10 on: June 07, 2005, 03:09:16 AM »

Parse error: parse error, expecting `T_STRING' or `T_VARIABLE' or `T_NUM_STRING' in c:\webfiles\apache\htdocs\dragon2\modules\popcities.php on line 44

line 44 is
Code:
rawoutput("<tr><td>$row['city']</td><td>$row['count']</td></tr>");

so it would appear to be something with the $row stuff.
« Last Edit: June 07, 2005, 03:10:32 AM by sixf00t4 » Logged

Excalibur
Member
Mod God
*****
Offline Offline

Posts: 573


I'm a newbie, plz forgive me!


View Profile WWW
« Reply #11 on: June 07, 2005, 03:19:23 AM »

line 44 is
Code:
rawoutput("<tr><td>$row['city']</td><td>$row['count']</td></tr>");
I think it should be:
Code:
rawoutput("<tr><td>".$row['city']."</td><td>".$row['count']."</td></tr>");
Logged

Spock: Random chance seems to have operated in our favor.
McCoy: In plain, non-Vulcan English, we've been lucky.
Spock: I believe I said that, Doctor.
sixf00t4
Mod God
*****
Offline Offline

Posts: 1916



View Profile WWW
« Reply #12 on: June 07, 2005, 05:11:00 AM »

smarty pants.  I gave you the easy one.   Tongue
Logged

Iwpg
Guest
« Reply #13 on: June 07, 2005, 02:08:07 PM »

And that's my final answer, honest.
what's this p. stuff?
OK, so I lied about the final answer part (you there up at the back, what are you snickering about?)  I left that in because I originally built the query up to use more than one table, then realised I only needed one but forgot to take the p out.  Basically, writing "module_userprefs p" renames the table to "p" for the rest of the query, to save typing, and putting "p." before the column names tells the database which table the columns should be from  - but since there's only one table, it's not necessary.  You can take out the "p" and "p." parts and it should do the same thing.
Logged
Pages: [1]   Go Up
  Print  
 
Jump to:  


*
DragonPrime Notices
Welcome to DragonPrime - The LoGD Resource Community!

Support Us
No funds raised yet this year
Your help is greatly appreciated!
Who's Online
46 Guests, 0 Users
DragonPrime LoGD
Recent Topics
Home Forums News Links Downloads Login Register Advanced Search