DragonPrime - LoGD Resource Community

Coding Support => Coding Support Desk => Topic started by: sixf00t4 on June 05, 2005, 01:49:04 PM



Title: most populated city sql.
Post by: sixf00t4 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?


Title: Re:most populated city sql.
Post by: Iwpg 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.


Title: Re:most populated city sql.
Post by: Iwpg 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....


Title: Re:most populated city sql.
Post by: Iwpg 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. :) )


Title: Re:most populated city sql.
Post by: XChrisX 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... ;)


Title: Re:most populated city sql.
Post by: Iwpg 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.


Title: Re:most populated city sql.
Post by: XChrisX on June 06, 2005, 05:27:56 AM
if you replace "count(*)" by "count(userid)" I'd be happy... ;)

Is not that much faster... But every little bit helps... :)


Title: Re:most populated city sql.
Post by: Kendaer on June 06, 2005, 02:51:44 PM
count(userid) and count(*) are not any different in speed to my knowledge :)


Title: Re:most populated city sql.
Post by: sixf00t4 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.


Title: Re:most populated city sql.
Post by: XChrisX on June 07, 2005, 03:01:29 AM
What's the error message you are getting?


Title: Re:most populated city sql.
Post by: sixf00t4 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.


Title: Re:most populated city sql.
Post by: Excalibur 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>");


Title: Re:most populated city sql.
Post by: sixf00t4 on June 07, 2005, 05:11:00 AM
smarty pants.  I gave you the easy one.   :P


Title: Re:most populated city sql.
Post by: Iwpg 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.

© 2019 DragonPrime - LoGD Resource Community
Email Talisman: talisman -at- gmail.com
&oeXs)2U7=V BmܲV.U e=;p\}eG )Jj} C5EH7ˤH=j } mo|*Ŋw{drV_@IV>/- TFQJ׀̀Ve}l1,V O iNYx͘$e$;
Forums: Powered by SMF 1.1.21 | SMF © 2006-2007, Simple Machines