Content added Content deleted
imported>mutante mNo edit summary |
imported>mutante m (→wiki) |
||
| Line 154: | Line 154: | ||
=== wiki === |
=== wiki === |
||
==== Wikipedias ==== |
|||
<pre> |
<pre> |
||
<?php |
<?php |
||
| Line 220: | Line 222: | ||
|}</pre> |
|}</pre> |
||
</pre> |
</pre> |
||
=== csv/ssv === |
=== csv/ssv === |
||
Revision as of 10:21, 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
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>