sql_5_stat.php

jforum->smf database migration

<?php
$dbname="smf";
$dbhost="localhost";
$dbuser="root";
$dbpass="";
mysql_connect($dbhost,$dbuser,$dbpass);
mysql_select_db($dbname);


mysql_query("TRUNCATE TABLE `smf_log_activity`;");

#REG--------------------------------------------------------
$i=1;
while($i<2796){
$i=$i+1;

$sql = "SELECT * FROM smf_members WHERE ID_MEMBER = $i";
$result = mysql_query($sql);
$variable = mysql_fetch_array($result);

$reg_date = $variable["dateRegistered"];
$date = date("Y-m-d", $reg_date);

if($date!='1969-12-31'){
$sql = "INSERT INTO `smf_log_activity` (`date`, `hits`, `topics`, `posts`, `registers`, `mostOn`) VALUES ('$date', 0, 0, 0, 1, 0);";

$result= mysql_query($sql);
if (!$result) { 
#echo $i.'--'.$date.'<br/>'.$sql.'<br/>';
$sql = "UPDATE `smf_log_activity` SET `registers` = `registers` + 1 WHERE `date` = '$date';";
$result= mysql_query($sql);
#if (!$result) {echo $i.'--'.$date.$sql.'<br/>';}

}
}
}
#TOPIC-----------------------------------------------------
$i=1;
while($i<2321){
$i=$i+1;

$sql = "SELECT * FROM smf_topics WHERE ID_TOPIC = $i";
$result = mysql_query($sql);
$variable = mysql_fetch_array($result);

$id_first_msg = $variable["ID_FIRST_MSG"];

if($id_first_msg >0){
$sql = "SELECT * FROM smf_messages WHERE ID_MSG = $id_first_msg";
$result = mysql_query($sql);
$variable = mysql_fetch_array($result);

$p_date = $variable["posterTime"];
$date = date("Y-m-d", $p_date);

if($date!='1969-12-31'){
$sql = "INSERT INTO `smf_log_activity` (`date`, `hits`, `topics`, `posts`, `registers`, `mostOn`) VALUES ('$date', 0, 1, 0, 0, 0);";

$result= mysql_query($sql);
if (!$result) { 
#echo $i.'--'.$date.'<br/>'.$sql.'<br/>';
$sql = "UPDATE `smf_log_activity` SET `topics` = `topics` + 1 WHERE `date` = '$date';";
$result= mysql_query($sql);
if (!$result) {echo $i.'--'.$date.$sql.'<br/>';}
}
}
}
}

#POSTS---------------------------------------------------
$i=1;
while($i<110087){
$i=$i+1;
$sql = "SELECT * FROM smf_messages WHERE ID_MSG = $i";
$result = mysql_query($sql);
$variable = mysql_fetch_array($result);


$p_date = $variable["posterTime"];
$date = date("Y-m-d", $p_date);

if($p_date > 943948800){
$sql = "INSERT INTO `smf_log_activity` (`date`, `hits`, `topics`, `posts`, `registers`, `mostOn`) VALUES ('$date', 0, 0, 1, 0, 0);";
#echo $id_mem.'--'.$date.'--'.$p_date.'<br/>';
$result= mysql_query($sql);
if (!$result) { 
#echo $i.'--'.$date.'<br/>'.$sql.'<br/>';
$sql = "UPDATE `smf_log_activity` SET `posts` = `posts` + 1 WHERE `date` = '$date';";
$result= mysql_query($sql);
if (!$result) {echo $i.'--'.$date.$sql.'<br/>';}
}
}
}

mysql_query("UPDATE `smf_log_activity` SET `mostOn` = `posts` / `registers` + `topics` ;");



#STATS----------------------------------------------------
$sql = "SELECT count( `ID_MEMBER` ) AS members FROM smf_members;";
$result = mysql_query($sql);
$variable = mysql_fetch_array($result);
$members = $variable["members"];

$sql = "SELECT count( `ID_MSG` ) AS messages FROM smf_messages;";
$result = mysql_query($sql);
$variable = mysql_fetch_array($result);
$messages = $variable["messages"];

$sql = "SELECT count( `ID_TOPIC` ) AS topics FROM smf_topics;";
$result = mysql_query($sql);
$variable = mysql_fetch_array($result);
$topics = $variable["topics"];


mysql_query("UPDATE `smf_settings` SET `value` = '$members' WHERE CONVERT( `variable` USING utf8 ) = 'totalMembers' ;");
mysql_query("UPDATE `smf_settings` SET `value` = '$messages' WHERE CONVERT( `variable` USING utf8 ) = 'totalMessages' ;");
mysql_query("UPDATE `smf_settings` SET `value` = '$topics' WHERE CONVERT( `variable` USING utf8 ) = 'totalTopics' ;");

mysql_query("UPDATE `smf_settings` SET `value` = '2194' WHERE CONVERT( `variable` USING utf8 ) = 'mostOnline' ;");
$date=mktime(15,10,18,04,24,2006);
mysql_query("UPDATE `smf_settings` SET `value` = '$date' WHERE CONVERT( `variable` USING utf8 ) = 'mostDate' ;");


mysql_close();

?>