Location: PHPKode > scripts > Usenet Downloader > usenet-downloader/save_group_listings.php
<?
set_time_limit(0);

include_once("config.inc.php");

class GetGroupListings
	{
/*****************************************************************************************/		
		function MarkUpdated($server_id)
			{
				mysql_connect($hostname,$user_name,$password);
				mysql_select_db($db_name");
				
				$update_str="UPDATE mp_usenet_servers SET processed_on_id='yes' WHERE id=".$server_id;
				mysql_query($update_str);
			}
/*****************************************************************************************/			
		function GetServerId($script_id,$server_name)
			{
				mysql_connect($hostname,$user_name,$password);
				mysql_select_db($db_name");
				
				$select_str="SELECT * FROM mp_usenet_servers WHERE processed_on='".$server_name."' AND is_searched=1 AND processed_on_id='".$script_id."'";
				$result=mysql_query($select_str);
				if(mysql_num_rows($result)>0)
					{
						$row=mysql_fetch_assoc($result);
						return $row['id'];
					}
				else 
					{
						$select_str="SELECT * FROM mp_usenet_servers WHERE (processed_on='' OR processed_on='".$server_name."') AND is_searched=1 AND processed_on_id='no'";	
						$result=mysql_query($select_str);
						
						if(mysql_num_rows($result)==0)return -1;
						
						$row=mysql_fetch_assoc($result);
						$server_id=$row['id'];
						
						$update_str="UPDATE mp_usenet_servers SET processed_on='".$server_name."', processed_on_id='".$script_id."' WHERE id=".$server_id;
						mysql_query($update_str);
						
						return $server_id;
					}
			}
/*****************************************************************************************/			
		function GetListings($server_id)
			{		
				include_once("connect.inc");				
				
				mysql_connect($listings_hostname,$listings_user_name,$listings_password);
				mysql_select_db($listings_db_name");
		
				$table_number=1;
				$table_name="usenet_listings_s".$server_id."_t";
				while($this->TablePresent($table_name.$table_number))$table_number++;
				
				if($table_number>1)$table_number--;
				
				$table_name=$table_name.$table_number;
				
				mysql_connect($hostname,$user_name,$password);
				mysql_select_db($db_name");
		
				$select_str="SELECT s.name as server_name,g.id as group_id,g.name as group_name FROM mp_usenet_groups g,mp_usenet_servers s WHERE FIND_IN_SET(s.id,g.server_id)>=1 AND s.id=".$server_id;
			
				//echo $select_str;
				$result=mysql_query($select_str);
				
				$current_server_name="";
				$common = new nntpcommon;
				$group_details_arr=array();
				$group_information=array();
				$count=0;
				$insert_count=0;
				$total_insert_counter=0;
				$insert_str="";
				while($row=mysql_fetch_assoc($result))
					{
						$group_information[$count]['group_id']=$row['group_id'];
						$group_information[$count]['group_name']=$row['group_name'];
						$group_information[$count]['server_name']=$row['server_name'];
						$count++;
					}
				mysql_connect($listings_hostname,$listings_user_name,$listings_password);
				mysql_select_db($listings_db_name");
		
				$this->CreateTable($table_name);		
				for($count1=0;$count1<count($group_information);$count1++)
					{
						mysql_connect($listings_hostname,$listings_user_name,$listings_password);
						mysql_select_db($listings_db_name");
		
						$group_id=$group_information[$count1]['group_id'];
						$group_name=$group_information[$count1]['group_name'];
						$server_name=$group_information[$count1]['server_name'];
						
						$select_str="SELECT article_id FROM ".$table_name." WHERE group_id=".$group_id;
						$result=mysql_query($select_str);				
						$article_arr=array();
						if(mysql_num_rows($result)>0){while($row=mysql_fetch_assoc($result)){$article_arr[]=$row['article_id'];}}
		
						if($current_server_name!=$server_name)
							{
								$nntp=ConnectToServer($server_name);						
								$current_server_name=$server_name;
								if($nntp===false)
									{	
										mysql_connect($hostname,$user_name,$password);
										mysql_select_db($db_name");			
										$update_str="UPDATE mp_usenet_servers SET processed_on_id='down' WHERE id=".$server_id;
										mysql_query($update_str);
										echo $update_str;flush();
										return false;
									}
							}																				
						
						$results=array();
						$empty_count=0;
						$group_details=$nntp->getgroupinfo($group_name);
						echo "first : ".$group_details['first']."<br/>";
						echo "last : ".$group_details['last'];				
						
						mysql_connect($listings_hostname,$listings_user_name,$listings_password);
						mysql_select_db($listings_db_name");
		
						for($count=$group_details['first'];$count<=$group_details['last'];$count++)
							{		
								if($group_details['first']==$group_details['last']||!is_numeric($group_details['last']))break;						
								
								$article_id=$nntp->nextarticle();
		
								if(in_array($article_id,$article_arr))continue;
				
								if(!is_numeric($article_id))continue;
		
								$head=$nntp->getarticlehead($group_name,$article_id);
								$head=$common->parseheader($head);
								if(isset($head['author']))$from=$head['author'];
								else $from="";
								$subject=trim($head['subject']);												
								
								$email=$common->parseemail($head['from']);
								$email=$email[0];
								
								$reply_email=$email['address'];
								$reply_name=$email['name'];
								$created_on=date('Y-m-d H:i:s',time());
								
								if($from=="")$from=$reply_name;
								
								$content=utf8_encode($nntp->getarticlebody($group_name, $article_id));
								
								if($empty_count==100)break;
								if($content==''){$empty_count++;continue;}
								
								$empty_count=0;
								$article_id=$count;
								$insert_str.="(".$group_id.",".$article_id.",'".mysql_escape_string($subject)."','".mysql_escape_string($content)."','".mysql_escape_string($from)."','".mysql_escape_string($reply_name)."','".mysql_escape_string($reply_email)."',-1,-1,0,'".$created_on."'),";
								
								$insert_count++;						
								$article_arr[]=$article_id;
		
								if($insert_count==100)
									{								
										$insert_str="INSERT INTO ".$table_name."(group_id,article_id,subject,content,author,reply_name,reply_email,member_id,parent_id,child_count,created_on) VALUES".trim($insert_str,',');
										mysql_query($insert_str);
										$insert_count=0;
										//echo $insert_str."<br/>";exit;
										$insert_str="";								
									}						
								$total_insert_counter++;
								if($total_insert_counter%100000==0)
									{
										$select_str="SELECT count(*) AS total_rows FROM ".$table_name;
										$result=mysql_query($select_str);
										$row=mysql_fetch_assoc($result);
										$total_rows=$row['total_rows'];
										if($total_rows>=1000000)
											{
												$table_number++;
												$table_name="usenet_listings_s".$server_id."_t".$table_number;
												$this->CreateTable($table_name);
											}
									}
							}	
						if($insert_str!="")
							{
								$insert_str="INSERT INTO ".$table_name."(group_id,article_id,subject,content,author,reply_name,reply_email,member_id,parent_id,child_count,created_on) VALUES".trim($insert_str,',');
								mysql_query($insert_str);
							}						
						$empty_count=0;
						
						$select_str="SELECT * FROM ".$table_name." WHERE group_id=".$group_id." AND subject NOT LIKE '%re:%'";
						$result=mysql_query($select_str);
						while($row=mysql_fetch_assoc($result))
							{
								$id=$row['id'];
								$subject=mysql_escape_string(trim($row['subject']));
								$select_str="SELECT * FROM ".$table_name." WHERE group_id=".$group_id." AND subject LIKE 're: ".$subject."%' AND id!=".$id;
								$result1=mysql_query($select_str);
								$row_count=mysql_num_rows($result1);
								if($row_count>1)
									{																
										while($row=mysql_fetch_assoc($result1))
											{
												$child_id=$row['id'];
												$update_str="UPDATE ".$table_name." SET parent_id=".$id." WHERE id=".$child_id;
												mysql_query($update_str);
											}
										$update_str="UPDATE ".$table_name." SET child_count=".$row_count." WHERE id=".$id;
										mysql_query($update_str);
									}
							}
						$select_str="SELECT count(*) as total FROM ".$table_name." WHERE group_id=".$group_id;
						$result=mysql_query($select_str);
						$row=mysql_fetch_assoc($result);
						$article_count=($row['total']!='')?$row['total']:0;
		
						mysql_connect($hostname,$user_name,$password);
						mysql_select_db($db_name");					
						
						$update_str="UPDATE mp_usenet_groups SET is_searched=1,article_count=".$article_count." WHERE id=".$group_id;
						mysql_query($update_str);
						
						if($article_count>0)
							{
								$update_str="UPDATE mp_usenet_servers SET group_count=group_count+1 WHERE id=".$server_id;
								mysql_query($update_str);
								
								$update_str="UPDATE mp_usenet_groups SET server_id=".$server_id." WHERE id=".$group_id;
								mysql_query($update_str);
							}
					}	
				return true;	
			}
/*****************************************************************************************/			
		function CreateTable($table_name)
			{
				$is_present=false;
				$result=mysql_list_tables("usenet");
				while($row=mysql_fetch_assoc($result))
					{
						$name=$row['Tables_in_usenet'];
						if($table_name==$name)$is_present=true;
					}
				if(!$is_present)
					{
						$table_str='CREATE TABLE IF NOT EXISTS `'.$table_name.'` (`id` int(10) unsigned NOT NULL auto_increment,`group_id` int(11) NOT NULL,`article_id` int(11) NOT NULL,`subject` text character set utf8 collate utf8_unicode_ci NOT NULL,`content` longtext character set utf8 collate utf8_unicode_ci NOT NULL,`author` text character set utf8 collate utf8_unicode_ci NOT NULL,`reply_name` varchar(255) character set utf8 collate utf8_unicode_ci NOT NULL,`reply_email` varchar(255) NOT NULL,
						  `member_id` int(11) NOT NULL default \'-1\',`parent_id` int(11) NOT NULL,`child_count` int(11) NOT NULL,`created_on` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,PRIMARY KEY  (`id`),UNIQUE KEY `group_id` (`group_id`,`article_id`),
		  				KEY `group_id_2` (`group_id`)) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1';
						mysql_query($table_str);
					}
			}
/*****************************************************************************************/			
		function TablePresent($table_name)
			{
				$result=mysql_list_tables("usenet");
				while($row=mysql_fetch_assoc($result))
					{
						$name=$row['Tables_in_usenet'];
						if($table_name==$name)return true;
					}
				return false;
			}
/*****************************************************************************************/			
	}
?>
Return current item: Usenet Downloader