Content added Content deleted
imported>mutante mNo edit summary |
imported>mutante mNo edit summary |
||
Line 1: | Line 1: | ||
= How To Generate Automatic Wiki Statistics = |
|||
= How To Generate automatic Wiki Statistics = |
|||
== Create Database == |
== Create Database == |
||
=== Table structures === |
=== Table structures === |
||
==== Mediawikis ==== |
|||
<pre> |
|||
mysql> describe wikistats; |
|||
+----------+--------------+------+-----+---------------------+----------------+ |
|||
| Field | Type | Null | Key | Default | Extra | |
|||
+----------+--------------+------+-----+---------------------+----------------+ |
|||
| id | int(11) | | PRI | NULL | auto_increment | |
|||
| name | varchar(64) | YES | | NULL | | |
|||
| label | varchar(128) | YES | | NULL | | |
|||
| url | varchar(255) | YES | | NULL | | |
|||
| total | int(11) | YES | | NULL | | |
|||
| good | int(11) | | | 0 | | |
|||
| views | int(11) | YES | | NULL | | |
|||
| edits | int(11) | YES | | NULL | | |
|||
| users | int(11) | YES | | NULL | | |
|||
| admins | int(11) | YES | | NULL | | |
|||
| started | date | YES | | NULL | | |
|||
| host | varchar(128) | YES | | NULL | | |
|||
| ts | timestamp | YES | | 0000-00-00 00:00:00 | | |
|||
| color | varchar(32) | YES | | NULL | | |
|||
| linktype | tinyint(4) | YES | | NULL | | |
|||
+----------+--------------+------+-----+---------------------+----------------+ |
|||
15 rows in set (0.03 sec) |
|||
</pre> |
|||
==== Wikipedias ==== |
|||
<pre> |
|||
mysql> describe wikipedias; |
|||
+----------+--------------+------+-----+---------------------+----------------+ |
|||
| Field | Type | Null | Key | Default | Extra | |
|||
+----------+--------------+------+-----+---------------------+----------------+ |
|||
| id | int(11) | | PRI | NULL | auto_increment | |
|||
| lang | varchar(128) | YES | | NULL | | |
|||
| prefix | varchar(16) | YES | MUL | NULL | | |
|||
| total | int(11) | YES | | NULL | | |
|||
| good | int(11) | YES | | NULL | | |
|||
| views | int(11) | YES | | NULL | | |
|||
| edits | int(11) | YES | | NULL | | |
|||
| users | int(11) | YES | | NULL | | |
|||
| admins | int(11) | YES | | NULL | | |
|||
| started | date | YES | | NULL | | |
|||
| ts | timestamp | YES | | 0000-00-00 00:00:00 | | |
|||
| loclang | varchar(128) | YES | | NULL | | |
|||
| mainpage | varchar(64) | YES | | NULL | | |
|||
+----------+--------------+------+-----+---------------------+----------------+ |
|||
13 rows in set (0.03 sec) |
|||
</pre> |
|||
=== Fill it initally === |
=== Fill it initally === |
||
== Keep it updated == |
== Keep it updated == |
||
=== script === |
|||
to be called from the commandline like |
|||
php update_wikipedias.php |
|||
<pre> |
|||
<?php |
|||
# Wikistats by mutante - 2006-02-08 - S23 Wiki - http://s23.org |
|||
# |
|||
# set user agent |
|||
ini_set('user_agent','http://s23.org/wikistats/ | http://meta.wikimedia.org/wiki/User:mutante | mutante@s23.org'); |
|||
# include config |
|||
require_once("config.php"); |
|||
# connect db |
|||
mysql_connect("$dbhost", "$dbname", "$dbpass") or die(mysql_error()); |
|||
echo "Wikistats Updater by S23-mutante \n Connected to MySQL. \n"; |
|||
# select db |
|||
mysql_select_db("wikistats") or die(mysql_error()); |
|||
echo "Selected database 'wikistats' \n."; |
|||
# select all ordered by good |
|||
$query = "select * from wikipedias order by good asc"; |
|||
$result = mysql_query("$query") or die(mysql_error()); |
|||
echo "Sent query: '$query'.\n"; |
|||
# main loop |
|||
while($row = mysql_fetch_array( $result )) { |
|||
if ($row['prefix']!="") { |
|||
$url="http://".$row['prefix'].".wikipedia.org/wiki/Special:Statistics?action=raw"; |
|||
$remotefile=fopen("$url","r"); |
|||
if (!$remotefile) { |
|||
echo "Unable to open remote file $url \n"; |
|||
fclose ($remotefile); |
|||
} else { |
|||
echo "Ok opening $url \n"; |
|||
$buffer = fgets($remotefile); |
|||
echo "Buffer: $buffer"; |
|||
$pieces = explode(";",$buffer); |
|||
$total = explode("=",$pieces[0]); |
|||
$total = $total[1]; |
|||
$good = explode("=",$pieces[1]); |
|||
$good = $good[1]; |
|||
# bug protection |
|||
if ($good>2147483647) { |
|||
$good=0; |
|||
} |
|||
$views = explode("=",$pieces[2]); |
|||
$views = $views[1]; |
|||
$edits = explode("=",$pieces[3]); |
|||
$edits = $edits[1]; |
|||
$users = explode("=",$pieces[4]); |
|||
$users = $users[1]; |
|||
$admins = explode("=",$pieces[5]); |
|||
$admins = $admins[1]; |
|||
$admins = trim($admins); |
|||
echo "Total: $total Good: $good Views: $views Edits: $edits Users: $users Admins: $admins \n"; |
|||
$query2="update wikipedias set total=\"$total\",good=\"$good\",views=\"$views\",edits=\"$edits\",users=\"$users\",admins=\"$admins\",ts=NOW() where id=\"".$row['id']."\";"; |
|||
echo "Query: $query2 \n"; |
|||
$result2 = mysql_query("$query2") or die(mysql_error()); |
|||
echo "Updated MySQL. Return Code: $result2 \n \n"; |
|||
fclose ($remotefile); |
|||
} |
|||
} |
|||
} |
|||
mysql_close(); |
|||
echo "Done. Fnord."; |
|||
?> |
|||
</pre> |
|||
=== cronjob === |
|||
Every 4 hours: |
|||
crontab -l |
|||
# m h dom mon dow command |
|||
00 */4 * * * /usr/local/sbin/update_all.sh |
|||
which calls (among others) |
|||
php /var/www/wikistats/update_wikis.php |
|||
== Display results == |
== Display results == |
||
=== html === |
=== html === |
||
=== wiki === |
=== wiki === |
||
<pre> |
|||
<?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> |
|||
</pre> |
|||
=== csv/ssv === |
=== csv/ssv === |
||
Revision as of 10:19, 5 March 2006
How To Generate Automatic Wiki Statistics
Create Database
Table structures
Mediawikis
mysql> describe wikistats; +----------+--------------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------------------+----------------+ | id | int(11) | | PRI | NULL | auto_increment | | name | varchar(64) | YES | | NULL | | | label | varchar(128) | YES | | NULL | | | url | varchar(255) | YES | | NULL | | | total | int(11) | YES | | NULL | | | good | int(11) | | | 0 | | | views | int(11) | YES | | NULL | | | edits | int(11) | YES | | NULL | | | users | int(11) | YES | | NULL | | | admins | int(11) | YES | | NULL | | | started | date | YES | | NULL | | | host | varchar(128) | YES | | NULL | | | ts | timestamp | YES | | 0000-00-00 00:00:00 | | | color | varchar(32) | YES | | NULL | | | linktype | tinyint(4) | YES | | NULL | | +----------+--------------+------+-----+---------------------+----------------+ 15 rows in set (0.03 sec)
Wikipedias
mysql> describe wikipedias; +----------+--------------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------------------+----------------+ | id | int(11) | | PRI | NULL | auto_increment | | lang | varchar(128) | YES | | NULL | | | prefix | varchar(16) | YES | MUL | NULL | | | total | int(11) | YES | | NULL | | | good | int(11) | YES | | NULL | | | views | int(11) | YES | | NULL | | | edits | int(11) | YES | | NULL | | | users | int(11) | YES | | NULL | | | admins | int(11) | YES | | NULL | | | started | date | YES | | NULL | | | ts | timestamp | YES | | 0000-00-00 00:00:00 | | | loclang | varchar(128) | YES | | NULL | | | mainpage | varchar(64) | YES | | NULL | | +----------+--------------+------+-----+---------------------+----------------+ 13 rows in set (0.03 sec)
Fill it initally
Keep it updated
script
to be called from the commandline like
php update_wikipedias.php
<?php # Wikistats by mutante - 2006-02-08 - S23 Wiki - http://s23.org # # set user agent ini_set('user_agent','http://s23.org/wikistats/ | http://meta.wikimedia.org/wiki/User:mutante | mutante@s23.org'); # include config require_once("config.php"); # connect db mysql_connect("$dbhost", "$dbname", "$dbpass") or die(mysql_error()); echo "Wikistats Updater by S23-mutante \n Connected to MySQL. \n"; # select db mysql_select_db("wikistats") or die(mysql_error()); echo "Selected database 'wikistats' \n."; # select all ordered by good $query = "select * from wikipedias order by good asc"; $result = mysql_query("$query") or die(mysql_error()); echo "Sent query: '$query'.\n"; # main loop while($row = mysql_fetch_array( $result )) { if ($row['prefix']!="") { $url="http://".$row['prefix'].".wikipedia.org/wiki/Special:Statistics?action=raw"; $remotefile=fopen("$url","r"); if (!$remotefile) { echo "Unable to open remote file $url \n"; fclose ($remotefile); } else { echo "Ok opening $url \n"; $buffer = fgets($remotefile); echo "Buffer: $buffer"; $pieces = explode(";",$buffer); $total = explode("=",$pieces[0]); $total = $total[1]; $good = explode("=",$pieces[1]); $good = $good[1]; # bug protection if ($good>2147483647) { $good=0; } $views = explode("=",$pieces[2]); $views = $views[1]; $edits = explode("=",$pieces[3]); $edits = $edits[1]; $users = explode("=",$pieces[4]); $users = $users[1]; $admins = explode("=",$pieces[5]); $admins = $admins[1]; $admins = trim($admins); echo "Total: $total Good: $good Views: $views Edits: $edits Users: $users Admins: $admins \n"; $query2="update wikipedias set total=\"$total\",good=\"$good\",views=\"$views\",edits=\"$edits\",users=\"$users\",admins=\"$admins\",ts=NOW() where id=\"".$row['id']."\";"; echo "Query: $query2 \n"; $result2 = mysql_query("$query2") or die(mysql_error()); echo "Updated MySQL. Return Code: $result2 \n \n"; fclose ($remotefile); } } } mysql_close(); echo "Done. Fnord."; ?>
cronjob
Every 4 hours:
crontab -l # m h dom mon dow command 00 */4 * * * /usr/local/sbin/update_all.sh
which calls (among others)
php /var/www/wikistats/update_wikis.php
Display results
html
wiki
<?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>