mysql_queries.php

来自「sabreipb 2.1.6 utf-8中文版本！」· PHP 代码 · 共 746 行 · 第 1/2 页

	
	function stats_get_todays_posters( $a )
	{
		return "SELECT COUNT(*) as tpost, m.id, m.name, m.members_display_name, m.joined, m.posts
				 FROM ".SQL_PREFIX."posts p
					LEFT JOIN ".SQL_PREFIX."members m ON (m.id=p.author_id )
					LEFT JOIN ".SQL_PREFIX."topics t ON (t.tid=p.topic_id)
				WHERE t.forum_id in(".implode(",", $a['ids']).") and post_date > {$a['time_low']}
				GROUP BY p.author_id
				ORDER BY tpost DESC LIMIT 0,10";
	}
	
	function ucp_mod_ip_tool_one( $a )
	{
		return "SELECT p.pid, t.forum_id
				FROM ".SQL_PREFIX."posts p
				LEFT JOIN ".SQL_PREFIX."topics t ON(t.tid=p.topic_id)
				WHERE t.forum_id IN({$a['the_forums']}) AND {$a['sql']}";
	}
	
	# Changed: added m.members_display_name
	function ucp_get_all_announcements( $a )
	{
		return "SELECT a.*, m.id, m.name, m.members_display_name
				 FROM ".SQL_PREFIX."announcements a
				  LEFT JOIN ".SQL_PREFIX."members m on (a.announce_member_id=m.id)
				 ORDER BY announce_end DESC";
	}
	
	function ucp_get_all_announcements_byid( $a )
	{
		return "SELECT a.*, m.*, me.*
				 FROM ".SQL_PREFIX."announcements a
				  LEFT JOIN ".SQL_PREFIX."members m on (a.announce_member_id=m.id)
				  LEFT JOIN ".SQL_PREFIX."member_extra me on (me.id=m.id)
				 WHERE a.announce_id={$a['id']}";
	}
	
	function ucp_get_forum_tracker( $a )
	{
		return "SELECT t.*, f.*
				FROM ".SQL_PREFIX."forum_tracker t
				 LEFT JOIN ".SQL_PREFIX."forums f ON (t.forum_id=f.id)
				WHERE t.member_id={$a['mid']}
				ORDER BY f.position";
	}
	
	function ucp_get_topic_tracker( $a )
	{
		return "SELECT s.topic_track_type, s.trid, s.member_id, s.topic_id, s.last_sent, s.start_date as track_started, t.*, f.id as forum_id, f.name as forum_name
				FROM ".SQL_PREFIX."tracker s, ".SQL_PREFIX."topics t, ".SQL_PREFIX."forums f
				WHERE s.member_id={$a['mid']} AND t.tid=s.topic_id AND f.id=t.forum_id {$a['date_query']}
				ORDER BY f.id, t.last_post DESC";
	}
	
	# Changed, removed group stuff
	function mlist_count( $a )
	{
		# Attempt to optimize on the fly...
		
		$query_bit_1 = '';
		$query_bit_2 = '';
		
		if ( strstr( $a['query'], 'p.field_' ) )
		{
			$query_bit_2 = "LEFT JOIN ".SQL_PREFIX."pfields_content p ON (p.member_id=m.id)";
		}
		
		if ( strstr( $a['query'], 'me.' ) )
		{
			$query_bit_1 = "LEFT JOIN ".SQL_PREFIX."member_extra me ON me.id=m.id";
		}
		
		if ( $a['query'] )
		{
			$a['query'] = 'WHERE '.$a['query'];
		}
		
		return "SELECT COUNT(*) as total_members FROM ".SQL_PREFIX."members m
				{$query_bit_1}
				{$query_bit_2}
				{$a['query']}";
	}
	
	# Changed, removed group stuff
	function mlist_get_members( $a )
	{
		if ( $a['query'] )
		{
			$a['query'] = 'WHERE '.$a['query'];
		}
		
		return "SELECT m.*,me.*,p.* FROM ".SQL_PREFIX."members m
				LEFT JOIN ".SQL_PREFIX."member_extra me ON me.id=m.id
				LEFT JOIN ".SQL_PREFIX."pfields_content p ON (p.member_id=m.id)
				{$a['query']}
				ORDER BY m.{$a['sort']} {$a['order']}
				LIMIT {$a['limit_a']}, {$a['limit_b']}";
	}
	
	function forum_get_attachments( $a )
	{
		return "SELECT a.*, t.*, p.topic_id, p.pid
				 FROM ".SQL_PREFIX."attachments a
				  LEFT JOIN ".SQL_PREFIX."posts p ON ( a.attach_pid=p.pid )
				  LEFT JOIN ".SQL_PREFIX."topics t ON ( t.tid=p.topic_id )
				 WHERE a.attach_pid != 0 AND p.topic_id={$a['tid']}
				 ORDER BY a.attach_date";
	
	}
	
	function buddy_posts_last_visit( $a )
	{
		return "SELECT COUNT(*) as posts 
				FROM ".SQL_PREFIX."posts p USE INDEX (topic_id)
				 LEFT JOIN ".SQL_PREFIX."topics t ON (p.topic_id=t.tid)		 
				 WHERE t.forum_id IN({$a['forum_string']})
				AND p.queued=0 AND p.post_date > {$a['last_visit']}";
 	}
	
	function generic_get_all_member( $a )
	{
		return "SELECT g.*, m.*, me.*
				FROM ".SQL_PREFIX."members m
				 LEFT JOIN ".SQL_PREFIX."member_extra me ON (me.id=m.id)
				 LEFT JOIN ".SQL_PREFIX."groups g ON (g.g_id=m.mgroup)
				WHERE m.id={$a['mid']}";
	}
	
	function moderate_get_topics( $a )
	{
		return "SELECT p.*,t.forum_id FROM ".SQL_PREFIX."posts p LEFT JOIN ".SQL_PREFIX."topics t ON (t.tid=p.topic_id) WHERE pid IN (".implode(",", $a['pids']).")";
	}
	
	function moderate_concat_title( $a )
	{
		return "UPDATE ".SQL_PREFIX."topics SET title=CONCAT('{$a['pre']}', title, '{$a['end']}') WHERE tid IN(".implode( ",", $a['tids'] ).")";
	}
	
	# UPDATE 2.1
	function mod_func_get_last_post( $a )
	{
		return "SELECT p.post_date, p.topic_id, p.author_id, p.author_name, p.pid, t.forum_id, m.id, m.members_display_name
				FROM ".SQL_PREFIX."posts p
				 LEFT JOIN ".SQL_PREFIX."topics t ON (p.topic_id=t.tid)
				 LEFT JOIN ".SQL_PREFIX."members m ON (p.author_id=m.id)
				WHERE topic_id={$a['tid']} and queued=0
				ORDER BY pid DESC LIMIT 0,1";
	}
	
	function mod_func_get_attach_count( $a )
	{
		return "SELECT COUNT(*) as count FROM ".SQL_PREFIX."attachments a
			     LEFT JOIN ".SQL_PREFIX."posts p on (a.attach_pid=p.pid)
			    WHERE p.topic_id={$a['tid']}";
	
	}
	
	function mod_func_get_topic_tracker( $a )
	{
		return "SELECT tr.*, m.id, m.mgroup, m.org_perm_id, t.tid, t.forum_id, g.g_id, g.g_perm_id
				 FROM ".SQL_PREFIX."tracker tr
				 LEFT JOIN ".SQL_PREFIX."topics t ON (tr.topic_id=t.tid)
				 LEFT JOIN ".SQL_PREFIX."members m on (m.id=tr.member_id)
				 LEFT JOIN ".SQL_PREFIX."groups g on (g.g_id=m.mgroup)
				WHERE tr.topic_id".$a['tid'];
	}
	
	function register_get_dead_validating( $a )
	{
		return "SELECT v.vid, v.member_id, m.posts
				   FROM ".SQL_PREFIX."validating v
				 LEFT JOIN ".SQL_PREFIX."members m ON (v.member_id=m.id)
				WHERE v.new_reg=1
				AND v.coppa_user <> 1
				AND v.entry_date < {$a['less_than']}
				AND v.lost_pass <> 1";
	}
	
	function search_get_all_user_count( $a )
	{
		return "SELECT count(*) as count
				FROM ".SQL_PREFIX."posts p
				 LEFT JOIN ".SQL_PREFIX."topics t ON (p.topic_id=t.tid AND t.approved=1)
				WHERE p.queued=0 AND t.forum_id IN({$a['forums']}) AND p.author_id={$a['mid']}";
	}
	
	function search_get_all_user_query( $a )
	{
		return "SELECT p.*, t.*, t.posts as topic_posts, t.title as topic_title, m.*, me.*
				FROM ".SQL_PREFIX."posts p
				 LEFT JOIN ".SQL_PREFIX."topics t ON (p.topic_id=t.tid AND t.approved=1)
				 LEFT JOIN ".SQL_PREFIX."members m ON (m.id=p.author_id)
				 LEFT JOIN ".SQL_PREFIX."member_extra me ON (me.id=p.author_id)
				WHERE p.queued=0 AND t.forum_id IN({$a['forums']}) AND p.author_id={$a['mid']}
				ORDER BY post_date DESC";
	}
	
	function search_get_last_ten( $a )
	{
		return "SELECT p.*, t.*, t.posts as topic_posts, t.title as topic_title, m.*, me.*
				FROM ".SQL_PREFIX."posts p
				 LEFT JOIN ".SQL_PREFIX."topics t ON (p.topic_id=t.tid)
				 LEFT JOIN ".SQL_PREFIX."members m ON (m.id=p.author_id)
				 LEFT JOIN ".SQL_PREFIX."member_extra me ON (me.id=p.author_id)
				WHERE p.queued=0 AND t.forum_id IN({$a['forums']}) AND p.author_id={$a['mid']}
				ORDER BY post_date DESC
				LIMIT 0,10";
	}
	
	function search_main_in_titles( $a )
	{
		return "SELECT t.*, t.posts as topic_posts, t.title as topic_title, p.pid, p.author_id, p.author_name, p.post_date, p.post, m.*, me.*
				FROM ".SQL_PREFIX."topics t
				  LEFT JOIN ".SQL_PREFIX."posts p ON (t.tid=p.topic_id AND p.new_topic=1)
				  LEFT JOIN ".SQL_PREFIX."members m ON (m.id=p.author_id)
				  LEFT JOIN ".SQL_PREFIX."member_extra me ON (me.id=p.author_id)
				WHERE t.tid IN({$a['topics']})
				ORDER BY p.post_date DESC
				LIMIT {$a['limit_a']},25";
	}
	
	function search_main_in_posts( $a )
	{
		return "SELECT t.*, t.posts as topic_posts, t.title as topic_title, p.pid, p.author_id, p.author_name, p.post_date, p.post, p.post_htmlstate, m.*, me.*
				FROM ".SQL_PREFIX."posts p
				  LEFT JOIN ".SQL_PREFIX."topics t ON (t.tid=p.topic_id)
				  LEFT JOIN ".SQL_PREFIX."members m ON (m.id=p.author_id)
				  LEFT JOIN ".SQL_PREFIX."member_extra me ON (me.id=p.author_id)
				WHERE p.pid IN({$a['posts']})
				ORDER BY p.post_date DESC
				LIMIT {$a['limit_a']},25";
	}
	
	function poll_get_poll_with_topic( $a )
	{
		return "SELECT f.allow_pollbump, t.*, p.pid as poll_id,p.choices,p.starter_id,p.votes
				FROM ".SQL_PREFIX."polls p, ".SQL_PREFIX."topics t, ".SQL_PREFIX."forums f
				WHERE t.tid={$a['tid']} and p.tid=t.tid and t.forum_id=f.id";
	}
	
	/**
	* contact_member_report_get_mods
	* Changed: 2.1.0.BETA4
	*	Added: m.members_disable_pm
	*
	*/
	function contact_member_report_get_mods( $a )
	{
		return "SELECT m.id, m.members_display_name as name, m.members_disable_pm, m.email, m.mgroup, moderator.member_id, moderator.group_id
				FROM ".SQL_PREFIX."moderators moderator, ".SQL_PREFIX."members m
				WHERE moderator.forum_id={$a['fid']}
				AND (moderator.member_id=m.id OR moderator.group_id=m.mgroup)";
	}
	
	/**
	* contact_member_report_get_cpaccess
	* Changed: 2.1.0.BETA4
	*	Added: m.members_disable_pm
	*
	*/
	function contact_member_report_get_cpaccess( $a )
	{
		return "SELECT m.id, m.members_display_name as name, m.email, m.members_disable_pm FROM ".SQL_PREFIX."members m, ".SQL_PREFIX."groups g WHERE g.g_access_cp=1 AND m.mgroup=g.g_id";
	}
	
	/**
	* contact_member_report_get_supmod
	* Changed: 2.1.0.BETA4
	*	Added: m.members_disable_pm
	*
	*/
	function contact_member_report_get_supmod( $a )
	{
		return "SELECT m.id, m.members_display_name as name, m.email, m.members_disable_pm FROM ".SQL_PREFIX."members m, ".SQL_PREFIX."groups g WHERE g.g_is_supmod=1 AND m.mgroup=g.g_id";
	}
	
	
	
	function print_page_get_members( $a )
	{
		return "SELECT g.*, m.* FROM ".SQL_PREFIX."members m, ".SQL_PREFIX."groups g WHERE m.id in ({$a['mem_ids']}) AND m.mgroup=g.g_id";
	}
	
	function stats_who_posted( $a )
	{
		return "SELECT COUNT(p.pid) as pcount, p.author_id, p.author_name FROM ".SQL_PREFIX."posts p
 				WHERE p.topic_id={$a['tid']} AND queued=0 GROUP BY p.author_id ORDER BY pcount DESC";
	}
	
	function warn_get_data( $a )
	{
		return "SELECT l.*,  p.id as punisher_id, p.members_display_name as punisher_name
				 FROM ".SQL_PREFIX."warn_logs l
				  LEFT JOIN ".SQL_PREFIX."members p ON ( p.id=l.wlog_addedby )
				WHERE l.wlog_mid={$a['mid']} ORDER BY l.wlog_date DESC LIMIT {$a['limit_a']}, {$a['limit_b']}";
	}
	
	function warn_get_forum( $a )
	{
		return "SELECT t.tid, t.title, f.id, f.name FROM ".SQL_PREFIX."topics t, ".SQL_PREFIX."forums f WHERE tid={$a['tid']} AND t.forum_id=f.id";
	}
	
	function portal_get_poll_join( $a )
	{
		return "SELECT t.tid, t.title, t.state, t.last_vote, p.*, v.member_id as member_voted
				FROM ".SQL_PREFIX."topics t, ".SQL_PREFIX."polls p
				LEFT JOIN ".SQL_PREFIX."voters v ON (v.member_id={$a['mid']} and v.tid=t.tid)
				WHERE t.tid={$a['tid']} AND p.tid=t.tid";
	}
	
	function portal_get_monster_bitch( $a )
	{
		return "SELECT t.*, p.*, me.avatar_location, m.view_avs, me.avatar_size, me.avatar_type,
				m.id as member_id, m.members_display_name as member_name, m.mgroup
				FROM ".SQL_PREFIX."topics t
				 LEFT JOIN ".SQL_PREFIX."members m ON (t.starter_id=m.id)
				 LEFT JOIN ".SQL_PREFIX."member_extra me on (m.id=me.id)
				 LEFT JOIN ".SQL_PREFIX."posts p ON (t.topic_firstpost=p.pid)
				WHERE t.forum_id IN (-1{$a['csite_article_forum']}) {$a['qe']}
				AND t.approved=1 AND (t.moved_to IS NULL or t.moved_to='')
				ORDER BY t.pinned DESC, t.start_date DESC
				LIMIT 0,{$a['limit']}";
	}
	
	function help_search( $a )
	{
		return "SELECT id, title, description
				 FROM ".SQL_PREFIX."faq
				WHERE LOWER(title) LIKE '%{$a['search_string']}%' or LOWER(text) LIKE '%{$a['search_string']}%'
				ORDER BY title";
	}
	
	#-- NEW FOR RC1 --#
	
	function login_getmember( $a )
	{
		return "select id, name, members_display_name, members_created_remote, email, mgroup, member_login_key, ip_address, login_anonymous
				from ".SQL_PREFIX."members
				where LOWER(name)='{$a['username']}'";
	}
	
	
	
	function post_get_topic_review( $a )
	{
		return "SELECT p.*, m.members_display_name, m.mgroup
				 FROM ".SQL_PREFIX."posts p
				 LEFT JOIN ".SQL_PREFIX."members m ON (m.id=p.author_id)
				WHERE topic_id={$a['tid']} and queued=0
				ORDER BY pid DESC
				LIMIT 0,10";
	}
	
	# REMOVED
	/*function post_forum_tracker_all( $a )
	{
		#post
		
		return "SELECT m.name, m.email, m.id, m.language, m.last_activity, m.org_perm_id, g.g_perm_id
				FROM ".SQL_PREFIX."members m, ".SQL_PREFIX."groups g
				WHERE m.mgroup IN ({$a['groups']})
				AND m.mgroup=g.g_id
				AND m.id <> {$a['mid']}
				AND m.allow_admin_mails=1
				AND m.last_activity < {$a['last_post']}";
	}*/
	
    
} // end class


?>