Wikistats/rank

http://s23.org/wikistats/rank.php
  position of such wiktionary in all wiktionaries number of all wiktionaries position of such wiktionary in all wmf wikis number of all wmf wikis
 * 4) the second wish was for the following behavior:
 * 5) position.php?family=wiktionaries&position=42 RETURN: cs
 * 6) position.php?family=wmfwikis&position=1 RETURN: enwiki

require_once("config.php"); mysql_connect("$dbhost", "$dbname", "$dbpass") or die(mysql_error); mysql_select_db("$dbdatabase") or die(mysql_error); $count=1; $arcount=0; $lang_check="FALSE"; $languages=array;

if (isset($_GET['lang'])) { $lang=$_GET['lang']; $lang=mysql_real_escape_string($lang); }

$query = "select prefix,lang from wikipedias where prefix is not null order by prefix asc"; $result = mysql_query("$query") or die(mysql_error);

while($row = mysql_fetch_array( $result )) {

$languages[$arcount]=$row[prefix]." - ".$row[lang]; $arcount++; if ($row[prefix]==$lang) { $lang_check="OK"; }

}

if (isset($_GET['lang']) AND $lang_check=="OK"){ $lang=$lang; } elseif (isset($_GET['lang']) AND $lang_check!="OK") { echo "This language does not exist (as a wikipedia)."; exit(1); } else { echo " This script returns the ranking of a Wikimedia foundation project when sorted by size.  (feature requested by Danny_B\n\nimplemented by mutante (2010/01))  usage: ?family=[project family]\n\n?lang=[language]   project family can be one of: \nw, wikt, n, b, q, s, v\n\n(interwiki shortcuts) "; echo " w - wikipedia wikt - wiktionary n - wikinews b - wikibooks q - wikiquote s - wikisource v - wikiversity "; echo "

language should be a language prefix that exists as a wikipedia subdomain: "; foreach ($languages as $language) { $langprefix=explode(" - ",$language); $langprefix=$langprefix[0]; echo "$language \n"; } echo "  output: &lt;lang.project&gt; &lt;rank within project&gt; &lt;number of wikis in project&gt; &lt;global rank&gt; &lt;global number of wikis&gt; en.wikipedia 1 272 1 761 examples: ?family=w&amp;lang=en</a> (Spanish Wikipedia) ?family=wikt&amp;lang=de</a> (English Wiktionary) ?family=v&amp;lang=ru</a> (Russian Wikiversity)</b> Complete tables can be found in wikistats</a>"; exit(0); }

if (isset($_GET['family'])) {

switch ($_GET['family']){ case "w": $table="wikipedias"; $family="wikipedia"; break; case "wikt": $table="wiktionaries"; $family="wiktionary"; break; case "n": $table="wikinews"; $family="wikinews"; break; case "b": $table="wikibooks"; $family="wikibooks"; break; case "q": $table="wikiquotes"; $family="wikiquote"; break; case "s": $table="wikisources"; $family="wikisource"; break; case "v": $table="wikiversity"; $family="wikiversity"; break; case "special": $table="wmspecials"; $family="wmf"; break; default: echo " project family does not exist.\n\nplease use one of: w, wikt, n, b, q, s, v.\n\nlike the shortcuts from http://meta.wikimedia.org/wiki/Help:Interwiki_linking"; exit(1); }

} else { $table="wiktionaries"; $family="wiktionary"; }


 * 1) echo "table $table family $family \n";

$query = "select id,prefix from $table where prefix is not null order by good desc,total desc"; $result = mysql_query("$query") or die(mysql_error); $num_rows = mysql_num_rows($result); while($row = mysql_fetch_array( $result )) {

if ($row[prefix]==$lang) { $rank_project=$count; $number_project=$num_rows; }

$count++; }

$count=1;

$query = <<<FNORD (select prefix,good,lang,loclang,total,edits,admins,users,images,ts,'wikipedias' as type from wikipedias where prefix is not null) union all (select prefix,good,lang,loclang,total,edits,admins,users,images,ts,'wikisources' as type from wikisources) union all (select prefix,good,lang,loclang,total,edits,admins,users,images,ts,'wiktionaries' as type from wiktionaries) union all (select prefix,good,lang,loclang,total,edits,admins,users,images,ts,'wikiquotes' as type from wikiquotes) union all (select prefix,good,lang,loclang,total,edits,admins,users,images,ts,'wikibooks' as type from wikibooks) union all (select prefix,good,lang,loclang,total,edits,admins,users,images,ts,'wikinews' as type from wikinews) union all (select url,good,lang,loclang,total,edits,admins,users,images,ts,'wmspecials' as type from wmspecials) union all (select prefix,good,lang,loclang,total,edits,admins,users,images,ts,'wikiversity' as type from wikiversity) order by good desc,total desc; FNORD;

$result = mysql_query("$query") or die(mysql_error); $num_rows = mysql_num_rows($result);

while($row = mysql_fetch_array( $result )) {

if ($row[prefix]==$lang AND $row[type]==$table) { $rank_global=$count; $number_global=$num_rows; $type=$row[type]; }

$count++; }

echo "$lang.$family $rank_project $number_project $rank_global $number_global\n";

if ($rank_project==""){ echo "\n! this language version does not seem to exist yet in this project"; }

mysql_close; ?> </HighlightSyntax>

query that calculates the rank within mysql
mysql> SELECT v1.prefix, v1.good, COUNT(v2.good) AS Rank FROM wiktionaries v1 JOIN wiktionaries v2 ON v1.good < v2.good OR (v1.good=v2.good and v1.prefix = v2.prefix) GROUP BY v1.prefix, v1.good ORDER BY v1.good DESC, v1.prefix DESC;

++-+--+ ++-+--+ ++-+--+ 172 rows in set (0.03 sec)
 * prefix    | good    | Rank |
 * fr        | 1570222 |    1 |
 * en        | 1562659 |    2 |
 * lt        |  407265 |    3 |
 * tr        |  266041 |    4 |
 * zh        |  252700 |    5 |
 * ru        |  233132 |    6 |
 * vi        |  228403 |    7 |
 * io        |  164196 |    8 |
 * pl        |  145690 |    9 |
 * el        |  143529 |   10 |
 * fi        |  138091 |   11 |
 * hu        |  134541 |   12 |
 * no        |  123168 |   13 |
 * ta        |  104478 |   14 |
 * it        |  102817 |   15 |
 * de        |  101149 |   16 |
 * pt        |   99506 |   17 |
 * sv        |   96828 |   18 |
 * ko        |   88217 |   19 |
 * nl        |   65993 |   20 |
 * lo        |   60653 |   21 |
 * ja        |   49056 |   22 |
 * ku        |   48111 |   23 |
 * ar        |   47408 |   24 |
 * kn        |   42800 |   25 |
 * es        |   42335 |   26 |
 * ro        |   37434 |   27 |
 * et        |   35740 |   28 |
 * id        |   34875 |   29 |
 * te        |   34716 |   30 |
 * gl        |   28682 |   31 |
 * bg        |   26345 |   32 |
 * uk        |   24701 |   33 |
 * ca        |   24075 |   34 |
 * vo        |   22045 |   35 |
 * ml        |   21371 |   36 |
 * is        |   16587 |   37 |
 * fa        |   16462 |   38 |
 * li        |   16281 |   39 |
 * sr        |   15240 |   40 |
 * af        |   14546 |   41 |
 * cs        |   13610 |   42 |
 * sw        |   12997 |   43 |
 * fy        |   12738 |   44 |
 * th        |   12682 |   45 |
 * scn       |   12669 |   46 |
 * simple    |   12561 |   47 |
 * br        |   12074 |   48 |
 * oc        |   11635 |   49 |
 * he        |    9818 |   50 |
 * sl        |    7110 |   51 |
 * hy        |    6995 |   52 |
 * sq        |    6714 |   53 |
 * tt        |    6643 |   54 |
 * la        |    6402 |   55 |
 * hr        |    6292 |   56 |
 * zh-min-nan |   5922 |   57 |
 * da        |    5516 |   58 |
 * tk        |    4231 |   59 |
 * ast       |    4230 |   60 |
 * wa        |    4053 |   61 |
 * ur        |    3839 |   62 |
 * hsb       |    3666 |   63 |
 * kk        |    3514 |   64 |
 * ky        |    3261 |   65 |
 * wo        |    2689 |   66 |
 * eo        |    2251 |   67 |
 * ang       |    2194 |   68 |
 * hi        |    1840 |   69 |
 * ga        |    1707 |   70 |
 * gn        |    1698 |   71 |
 * az        |    1680 |   72 |
 * ia        |    1672 |   73 |
 * co        |    1619 |   74 |
 * sk        |    1430 |   75 |
 * csb       |    1393 |   76 |
 * st        |    1387 |   77 |
 * ms        |    1360 |   78 |
 * mr        |    1304 |   79 |
 * ka        |    1294 |   80 |
 * tl        |    1260 |   81 |
 * ne        |    1219 |   82 |
 * nds       |    1033 |   83 |
 * kl        |    1023 |   84 |
 * sd        |     840 |   85 |
 * ug        |     824 |   86 |
 * ti        |     751 |   87 |
 * eu        |     694 |   88 |
 * mk        |     666 |   89 |
 * cy        |     638 |   90 |
 * ps        |     582 |   91 |
 * an        |     576 |   92 |
 * sh        |     531 |   93 |
 * my        |     530 |   94 |
 * lv        |     512 |   95 |
 * bn        |     510 |   96 |
 * gu        |     480 |   97 |
 * mg        |     429 |   98 |
 * km        |     428 |   99 |
 * ts        |     358 |  100 |
 * qu        |     350 |  101 |
 * bs        |     329 |  102 |
 * fo        |     323 |  103 |
 * am        |     316 |  104 |
 * rw        |     306 |  105 |
 * chr       |     247 |  106 |
 * su        |     236 |  107 |
 * om        |     203 |  108 |
 * mn        |     202 |  109 |
 * nah       |     194 |  110 |
 * ie        |     183 |  111 |
 * yi        |     178 |  112 |
 * be        |     171 |  113 |
 * iu        |     167 |  114 |
 * gd        |     141 |  115 |
 * tg        |     135 |  116 |
 * nn        |     133 |  117 |
 * si        |     132 |  118 |
 * zu        |     128 |  119 |
 * pa        |     122 |  120 |
 * dv        |     120 |  121 |
 * mt        |     119 |  122 |
 * kw        |     114 |  123 |
 * tpi       |     110 |  124 |
 * gv        |     108 |  125 |
 * roa-rup   |     101 |  126 |
 * mi        |      92 |  127 |
 * sg        |      91 |  128 |
 * jv        |      90 |  129 |
 * uz        |      86 |  130 |
 * ik        |      82 |  131 |
 * so        |      71 |  132 |
 * ha        |      59 |  133 |
 * ss        |      46 |  134 |
 * sa        |      43 |  135 |
 * na        |      41 |  136 |
 * ay        |      41 |  136 |
 * jbo       |      31 |  138 |
 * tn        |      22 |  139 |
 * sm        |      21 |  140 |
 * as        |      21 |  140 |
 * lb        |      17 |  142 |
 * ks        |      16 |  143 |
 * fj        |      14 |  144 |
 * ln        |      12 |  145 |
 * mo        |      11 |  146 |
 * za        |       8 |  147 |
 * pi        |       6 |  148 |
 * xh        |       5 |  149 |
 * mh        |       5 |  149 |
 * ba        |       5 |  149 |
 * sn        |       4 |  152 |
 * or        |       4 |  152 |
 * bh        |       4 |  152 |
 * ak        |       4 |  152 |
 * yo        |       3 |  156 |
 * rn        |       3 |  156 |
 * bm        |       3 |  156 |
 * bi        |       3 |  156 |
 * av        |       3 |  156 |
 * to        |       2 |  161 |
 * ab        |       2 |  161 |
 * aa        |       2 |  161 |
 * tw        |       1 |  164 |
 * dz        |       1 |  164 |
 * tokipona  |       0 |  166 |
 * sc        |       0 |  166 |
 * rm        |       0 |  166 |
 * cr        |       0 |  166 |
 * ch        |       0 |  166 |
 * bo        |       0 |  166 |
 * als       |       0 |  166 |

From http://www.artfulsoftware.com/infotree/queries.php?&bw=1024#460