Content added Content deleted
imported>mutante mNo edit summary |
imported>mutante mNo edit summary |
||
Line 143:
?>
</pre>
=== Query for combined statistics ===
[[MySQL]] query for combined statistics from multiple tables:
mysql> (select prefix,good,"wikipedia" as type from wikipedias) union all (select prefix,good,"wikisource" as type from wikisources) union all (select prefix,good,"wiktionary" as type from wiktionaries) union all (select prefix,good,"wikiquote" as type from wikiquotes) union all (select prefix,good,"wikibooks" as type from wikibooks) union all (select prefix,good,"wikinews" as type from wikinews) order by good desc;
[http://dev.mysql.com/doc/refman/5.0/en/union.html MySQL - Union Syntax]
Back to [[Wikistats]]
|
Revision as of 02:52, 8 March 2006
-->Wikistats->Display scripts
html
Wikis
<?php # Wikistats by mutante - 2006-02 - S23 Wiki - http://s23.org $listname="List of largest Wikis"; $listtable="wikistats"; $wikioutput="wikis_wiki.php"; $wikipage="http://meta.wikimedia.org/wiki/List_of_largest_wikis"; # require_once("config.php"); mysql_connect("$dbhost", "$dbname", "$dbpass") or die(mysql_error()); # echo "Connected to MySQL.<br />"; mysql_select_db("wikistats") or die(mysql_error()); # echo "Selected database 'wikistats'.<br />"; $query = "select * from $listtable order by good desc"; $result = mysql_query("$query") or die(mysql_error()); # echo "Sent query: '$query'.<br /><br />"; ?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" lang="en" xml:lang="en"> <?php echo "<head><title>WikiStats by S23 - $listname</title>\n<meta name=\"author\" content=\"mutante@s23.org\" />\n<link href=\"wikistats.css\" rel=\"stylesheet\" type=\"text/css\" /></head>\n\n<body>\n<table><tr><th class=\"head\" colspan=\"11\">$listname</th></tr><tr><th class=\"sub\">No.</th><th class=\"sub\">Type</th><th class=\"sub\">Wiki</th><th class=\"sub\">Good</th><th class=\"sub\">Total</th><th class=\"sub\">Views</th><th class=\"sub\">Edits</th><th class=\"sub\">User</th><th class=\"sub\">Admins</th><th class=\"sub\">Started</th><th class=\"sub\">Last updated</th></tr>\n"; $count=1; while($row = mysql_fetch_array( $result )) { echo "<tr><td class=\"number\">$count</td><td class=\"text\" style=\"color:black;background-color:".$row['color'].";\"></td><td class=\"text\"><a href=\"".$row['url']."\">".$row['label']."</a></td><td class=\"number\"><a href=\"".$row['url']."Special:Statistics?action=raw\">".$row['good']."</a></td><td class=\"number\">".$row['total']."</td><td class=\"number\">".$row['views']."</td><td class=\"number\"><a href=\"".$row['url']."Special:Recentchanges\">".$row['edits']."</a></td><td class=\"number\"><a href=\"".$row['url']."Special:Listusers\">".$row['users']."</a></td><td class=\"number\"><a href=\"".$row['url']."Special:Listadmins\">".$row['admins']."</a></td><td class=\"timestamp\">".$row['started']."</td><td class=\"timestamp\">".$row['ts']."</td></tr>\n"; $count++; } echo "</table>\n\n<table><tr><th class=\"head\" colspan=\"2\">Legend</th></tr><tr><th class=\"sub\">color</th><th class=\"sub\">class</th></tr><tr><td class=\"text\" style=\"color:black;background-color:#ffffff\"></td><td>Wikipedia</td></tr><tr><td class=\"text\" style=\"color:black;background-color:#ff8080\"></td><td>Wiktionary</td></tr><tr><td class=\"text\" style=\"color:black;background-color:#ffcc11\"></td><td>Other Wikimedia</td></tr><tr><td class=\"text\" style=\"color:black;background-color:#90EE90\"></td><td>Non-Wikimedia</td></tr></table>\n\n<ul><li><a class=\"foot\" href=\"$wikioutput\">Table in Wiki Syntax</a></li><li>In use on: <a class=\"foot\" href=\"$wikipage\">$wikipage</a></li><li><a class=\"foot\" href=\"index.php\">Back to Index</a></li></ul>\n"; ?> <p class="footer">created by <a class="foot" href="http://s23.org/wiki/User:mutante">mutante</a> of <a class="foot" href="http://s23.org/wiki/Main_Page">S23</a> - 2006-02-22 <a class="foot" href="http://validator.w3.org/check?uri=referer"> <img style="border:0;width:60px;" src="http://www.w3.org/Icons/valid-xhtml10" alt="Valid XHTML 1.0 Strict" /></a> <a class="foot" href="http://jigsaw.w3.org/css-validator/"> <img style="border:0;width:60px;" src="http://jigsaw.w3.org/css-validator/images/vcss" alt="Valid CSS!" /></a> </p> </body></html>
wikisyntax
Wikipedias
<?php # Wikistats by mutante - 2006-02 - S23 Wiki - http://s23.org # require_once("config.php"); mysql_connect("$dbhost", "$dbname", "$dbpass") or die(mysql_error()); # echo "Connected to MySQL.<br />"; mysql_select_db("wikistats") or die(mysql_error()); # echo "Selected database 'wikistats'.<br />"; $query = "select * from wikipedias order by good desc,total desc,edits desc"; $result = mysql_query("$query") or die(mysql_error()); # echo "Sent query: '$query'.<br /><br />"; ?> <pre> <?php $count=1; while($row = mysql_fetch_array( $result )) { if ($row['prefix']!="") { ?> |- | style="text-align: right;" | <?php echo $count; ?> | style="text-align: right; | <?php echo "[[w:".$row['lang']." language|".$row['lang']."]]"; ?> | style="text-align: right; | <?php echo "[[w:".$row['lang']." language|".$row['loclang']."]]"; ?> | style="text-align: right; | <?php echo "[[:".$row['prefix'].":|".$row['prefix']."]]"; ?> | style="text-align: right;" | <?php echo "[http://".$row['prefix'].".wikipedia.org/wiki/Special:Statistics?action=raw '''".$row['good']; ?>'''] | style="text-align: right;" | <?php echo $row['total']; ?> | style="text-align: right;" | <?php echo "[http://".$row['prefix'].".wikipedia.org/wiki/Special:Recentchanges ".$row['edits']; ?>] | style="text-align: right;" | <?php echo "[http://".$row['prefix'].".wikipedia.org/wiki/Special:Listadmins ".$row['admins']; ?>] | style="text-align: right;" | <?php echo "[http://".$row['prefix'].".wikipedia.org/wiki/Special:Listusers ".$row['users']; ?>] <?php } else { if ($count>1) { echo "|} \n"; } ?> === <?php echo $row['lang']; ?> === {| border="1" cellpadding="2" cellspacing="0" style="width:75%; background: #f9f9f9; border: 1px solid #aaaaaa; border-collapse: collapse; white-space: nowrap; text-align: left" |- ! No. ! Language ! Language (local) ! Wiki ! Articles ! Total ! Edits ! Admins ! Users <?php } if ($row['prefix']!="") { $count++; } } mysql_close(); ?> |}</pre>
csv/ssv
Wikisources
<?php header("Content-type: application/octet-stream"); header("Content-Disposition: attachment; filename=wikisources.csv"); header("Pragma: no-cache"); header("Expires: 0"); require_once("config.php"); mysql_connect("$dbhost", "$dbname", "$dbpass") or die(mysql_error()); mysql_select_db("wikistats") or die(mysql_error()); $query = "select * from wikisources order by good desc,total desc"; $result = mysql_query("$query") or die(mysql_error()); $count=1; $cr = "\n"; echo "rank,id,prefix,language,good,total,edits,views,admins,users,timestamp $cr"; while($row = mysql_fetch_array( $result )) { echo "$count,".$row['id'].",".$row['prefix'].",".$row['lang'].",".$row['good'].",".$row['total'].",".$row['edits'].",".$row['views'].",".$row['admins'].",".$row['users'].",".$row['ts']."$cr"; $count++; } mysql_close(); ?>
Query for combined statistics
MySQL query for combined statistics from multiple tables:
mysql> (select prefix,good,"wikipedia" as type from wikipedias) union all (select prefix,good,"wikisource" as type from wikisources) union all (select prefix,good,"wiktionary" as type from wiktionaries) union all (select prefix,good,"wikiquote" as type from wikiquotes) union all (select prefix,good,"wikibooks" as type from wikibooks) union all (select prefix,good,"wikinews" as type from wikinews) order by good desc;
Back to Wikistats