Forum core source dump (as of July 31)

lib/MySQL.class.php

Note: I did away with the Database class. Considering how little it was doing, I didn’t see a need to still keep it.

/**
* MySQL Database access class.
* Inherits "Database".
*
* @author Eksith Rodrigo
* @version 0.1
* @license http://www.opensource.org/licenses/mit-license.php MIT License
* @access public
*/

final class MySQL
{
	static private $instance;
	private $stmt;
	
	private $props;
	
	private $connection;
	private $hostname;
	private $database;
	private $username;
	private $password;
	
	// Classes can only be created using the getInstance function
	protected function __construct($host=null, $name=null, $user=null, $pass=null)
	{
		$this->hostname = $host;
		$this->database = $name;
		$this->username = $user;
		$this->password = $pass;
	}
	
	// Singleton instance
	static function getInstance($host, $name, $user, $pass)
	{
		if(!isset(self::$instance))
			self::$instance = new MySQL($host, $name, $user, $pass);
		
		return self::$instance;
	}
	
	protected function __set($name, $value)
	{
		if(isset($this->props[$name]))
			$this->props[$name] = $value; 
	}
	
	protected function __get($name)
	{
		if(isset($this->props[$name]))
			return $this->props[$name];
		
		return null;
	}
	
	
	
	
	/******************************************************************************
		Connection and query execution
	******************************************************************************/
	
	// Check if the database connection is established
	public function Connected()
	{
		if(is_resource($this->connection))
			return true;
		
		return false;
	}
	
	// Open database connection
	public function Open()
	{
		$p = $this->connectionCheck();
		if(empty($p))
			$this->connection = new mysqli($this->hostname, $this->username, $this->password, $this->database);
		
		if($this->connection->connect_error)
			exit($this->connection->connect_errno);
	}
	
	// Close database connection
	public function Close()
	{
		$this->connection->close();
		$this->connection = null;
	}
	
	// Execute non-query and return affected rows
	public function nonQuery($sql)
	{
		$this->connection->query($sql);
		return $this->connection->affected_rows;
	}
	
	// Execute non-query and return last inserted ID
	public function nonQueryLastID($sql)
	{
		$this->connection->query($sql);
		return $this->connection->insert_id;
	}
	
	// Check if the database object exists
	private function connectionCheck()
	{
		if(is_null($this->database))
			return("MySQL database not present");
		
		if(is_null($this->hostname))
			return("MySQL hostname not present");
		
		return '';
	}
	
	
	
	
	/******************************************************************************
		User related data
	******************************************************************************/
	
	// Get user login data by name
	public function getLoginData($user)
	{
		$data = array();
		$sql = "SELECT id, password, password_salt ". 
			"(SELECT groups.privileges FROM groups ".
			"WHERE(groups.group_id  = usersgroups.group_id AND usersgroups.user_id = users.id)) AS privileges ".
			"FROM users WHERE username = ?";
		
		if ($stmt = $this->connection->prepare($sql))
		{
			$stmt->bind_param('s', $user);
			$stmt->execute();
			$stmt->bind_result($uid, $pass, $salt, $dname, $priv);
			while($stmt->fetch())
			{
				$data['id'] = $uid;
				$data['pass'] = $pass;
				$data['salt'] = $salt;
				$data['priv'] = (empty($priv))? '000000000000000000000' : $priv;
			}
			$stmt->close();
		}
		
		return $data;
	}
	
	// Get user ID / Check if user already exists
	public function getUserID($user)
	{
		$r = 0;
		$sql = "SELECT id FROM users WHERE username = ?";
		if ($stmt = $this->connection->prepare($sql))
		{
			$stmt->bind_param("s", $user);
			$stmt->execute();
			$stmt->bind_result($r);
			$stmt->fetch();
			
			$stmt->close();
		}
		return $r;
	}
	
	// Save user data
	public function saveUser($id, $pass, $pass_salt, $email, $dname='', $web='', $bio='', $sig='', $avatar='', $enabled=true)
	{
		// TODO
		$r = 0;
		$sql = "UPDATE users SET password = ?, password_salt = ?, email = ?, enabled = ?, display_name = ?, web = ?, ".
			"sig = ?, avatar = ?, bio = ? WHERE id = ?;";
		if ($stmt = $this->connection->prepare($sql))
		{
			$stmt->bind_param("sssisssssi", $pass, $pass_salt, $email, (($enabled)? 1 : 0), $dname, $web, $sig, $avatar, $bio, $id);
			$stmt->execute();
			
			$r = $stmt->affected_rows;
			
			$stmt->close();
		}
		return r;
	}
	
	// Save group privileges
	public function groupPrivileges($id, $priv)
	{
		$r = 0;
		$sql = "UPDATE groups SET privileges = ? WHERE id = ?;";
		if ($stmt = $this->connection->prepare($sql))
		{
			$stmt->bind_param("si", implode('', $priv), $id);
			$stmt->execute();
			
			$r = $stmt->affected_rows;
			
			$stmt->close();
		}
		return r;
	}
	
	// Assign a user into a different group
	public function assignToGroup($userID, $groupID)
	{
		$r = 0;
		$sql = "DELETE FROM usersgroups WHERE user_id = ?";
		if ($stmt = $this->connection->prepare($sql))
		{
			$stmt->bind_param("i", $userID);
			$stmt->execute();
			
			$r = $stmt->affected_rows;
			
			$stmt->close();
		}
		if($r > 0)
		{
			$sql = "INSERT INTO usersgroups(user_id, group_id) VALUES(?, ?)";
			if($stmt = $this->connection->prepare($sql))
			{
				$stmt->bind_param("ii", $userID, $groupID);
				$stmt->execute();
				
				$r = $stmt->affected_rows;
				
				$stmt->close();
			}
		}
		return r;
	}
	
	
	// Register a new user
	public function createUser($user, $pass, $pass_salt, $email, $enabled=true)
	{
		$r = 0;
		$sql = "INSERT INTO users(username, password, password_salt, created, enabled, email) ".
			"VALUES(?, ?, ?, NOW(), ?, ?);";
			
		if ($stmt = $this->connection->prepare($sql))
		{
			$stmt->bind_param("sssis", $user, $pass, $pass_salt, $enabled, $email);
			$stmt->execute();
			
			$r = $stmt->insert_id;
			
			$stmt->close();
		}
		
		// If the user was created, then sort into the signup group
		if($r > 0)
		{
			$sql = "INSERT INTO usersgroups(`user_id`, `group_id`) VALUES (?, (SELECT id FROM groups WHERE signup = 1));";
		}
		
		return r;
	}
	
	

	
	/******************************************************************************
		Posting forum, topic and reply data
	******************************************************************************/
	
	// Creating a new forum
	public function createForum($title, $content, $content_p, $order)
	{
		$r = 0;
		return r;
	}
	
	
	
	
	/******************************************************************************
		Getting forum, topic and reply data
	******************************************************************************/
	
	// Get forums by ID
	public function getForums($id)
	{
		$forums = array();
		$sql = "SELECT id, parent_id, title, content, content_plain, topic_count, reply_count, last_id, ". 
			"last_author, last_date FROM forums WHERE parent_id = ? ORDER BY display_order, id DESC;";
		
		if ($stmt = $this->connection->prepare($sql))
		{
			$stmt->bind_param('i', $id);
			$stmt->execute();
					
			$this->bindForums($stmt, $forums);
			
			$stmt->close();
		}
		return $forums;
	}
	
	// Get topics by forum ID with limited range and/or recent topics only
	public function getTopics($id, $start, $end, $recent=false)
	{
		$topics = array();
		$sql = "";
		
		if($recent)
			$sql = "SELECT m.id, m.forum_id, m.title, f.title, m.content, m.content_plain, m.created, m.view_count, ".
				"m.reply_count, m.author, m.user_id, m.last_id, m.last_author, m.last_date, m.locked, m.sticky, ".
				"m.global_sticky FROM messages AS m INNER JOIN forums AS f ON m.forum_id = f.id ".
				"WHERE (m.parent_id = 0) ". 
				"ORDER BY m.last_date, m.id ASC LIMIT ?,?;";
		else
			$sql = "SELECT m.id, m.forum_id, m.title, f.title, m.content, m.content_plain, m.created, m.view_count, ".
				"m.reply_count, m.author, m.user_id, m.last_id, m.last_author, m.last_date, m.locked, m.sticky, ".
				"m.global_sticky FROM messages AS m INNER JOIN forums AS f ON m.forum_id = f.id ".
				"WHERE ((m.forum_id = ? OR m.global_sticky = ?) AND m.sticky = ? AND m.parent_id = 0) ". 
				"ORDER BY m.last_date, id ASC LIMIT ?,?;";
		
		if ($stmt = $this->connection->prepare($sql))
		{
			if($recent)
				$stmt->bind_param('ii', $start, $end);
			else
				$stmt->bind_param('iiiii', $id, $gb, $st, $start, $end);
			
			// It's the first page
			if($start < = 0)
			{
				// First, get global sticky topics
				$gb = 1;
				$st = 0;
				$stmt->execute();
						
				$this->bindTopics($stmt, $topics);
				
				if(!$recent)
				{
					// Local sticky topics come next
					$gb = 0;
					$st = 1;
					$stmt->execute();
					
					$this->bindTopics($stmt, $topics);
				}
				
				// Reduce the start limit by the number of sticky topics
				$start = $start - count($topics);
			}
			
			// Ordinary topics
			$st = 0;
			$gb = 0;
			$stmt->execute();
			
			$this->bindTopics($stmt, $topics);
			
			$stmt->close();
		}
		
		return $topics;
	}
	
	// Get replies by topic ID under limited range
	public function getReplies($id, $start, $end)
	{
		$replies = array();
		$sql = "SELECT id, forum_id, parent_id, title, content, content_plain, created, ".
			"author, user_id, last_id, last_author, last_date, locked FROM messages ".
			"WHERE (id = ? OR parent_id = ?) ORDER BY created, id ASC LIMIT ?,?;";
		
		if ($stmt = $this->connection->prepare($sql))
		{
			// Seemed to cause some problems without this duplication
			$id1 = $id;
			$stmt->bind_param('iiii', $id, $id1, $start, $end);
			$stmt->execute();
			
			$this->bindReplies($stmt, $replies, $id);
			
			$stmt->close();
		}
		
		return $replies;
	}
	
	
	
	
	/******************************************************************************
		Anti abuse functions
	******************************************************************************/
	
	
	/**
	* Check if a sufficient time has passed since the last request.
	* If the user isn't flooding, the check returns true. 
	* 
	* @param date $limit The origin time to compare to.
	* @param string $ip IP address.
	* @return bool $r If the user is flooding, false if else true. Defaults to false.
	*/
	public function floodCheck(&$limit, &$ip)
	{
		$dt = null;	// Store the date returned
		$r = false;	// Store return value
		
		// Check if ip already exists
		$sql = "SELECT last_access FROM floodcheck WHERE (ip = ?);";
		
		if ($stmt = $this->connection->prepare($sql))
		{
			$stmt->bind_param('s', $ip);
			$stmt->execute();
			$stmt->bind_result($dt);
			$stmt->close();
		}
		
		if(empty($dt))
			$dt = date(mktime(0, 0, 0, 1, 1, 1970));
		else
			$dt = date($dt);
		
		if ($dt < $limit)
		{
			// User is not flooding. Passes check.
			$r = true;
			
			// We don't want to do any clean-ups during an actual flood.
			// Save that for when burden is lower. I.E. Now...
			$sql = "DELETE FROM floodcheck WHERE last_access = (NOW()-?);";
			
			if ($stmt = $this->connection->prepare($sql))
			{
				$stmt->bind_param('s', $limit);
				$stmt->execute();
				$stmt->close();
			}
		}
		else
		{
			// User is flooding.
			
			// As per above, don't do anything but update the flood value
			$sql = "UPDATE floodcheck SET last_access = NOW() WHERE ip = ?;";
			if ($stmt = $this->connection->prepare($sql))
			{
				$stmt->bind_param('s', $ip);
				$stmt->execute();
				$stmt->close();
			}
		}
		return $r;
	}
	
	
	
	/******************************************************************************
		Array binding of fetched results
	******************************************************************************/
	
	// Bind replies into array
	private function bindReplies(&$stmt, &$replies, &$id)
	{
		$stmt->bind_result($r_id, $r_forum_id, $r_parent_id, $r_t, $r_content, $r_content_p, $r_created, $r_author, $r_user_id, 
					$r_last_id, $r_last_a, $r_last_d, $r_locked);
		
		while($stmt->fetch())
		{
			$replies[$r_id]['parent_id'] = $r_parent_id;
			$replies[$r_id]['forum_id'] = $r_forum_id;
			$replies[$r_id]['title'] = $r_t;
			$replies[$r_id]['content'] = $r_content;
			$replies[$r_id]['content_plain'] = $r_content_p;
			$replies[$r_id]['created'] = $r_created;
			$replies[$r_id]['author'] = $r_author;
			$replies[$r_id]['user_id'] = $r_user_id;
			$replies[$r_id]['last_id'] = $r_last_id;
			$replies[$r_id]['last_author'] = $r_last_a;
			$replies[$r_id]['last_date'] = $r_last_d;
			$replies[$r_id]['locked'] = $r_locked;
		}
	}
	
	// Bind topics into array
	private function bindTopics(&$stmt, &$topics)
	{
		$stmt->bind_result($topic_id, $t_forum_id, $t_title, $t_forum_t, $t_content, $t_content_p, $t_created, $t_views, $t_replies, 
				$t_author, $t_user_id, $t_last_id, $t_last_author, $t_last_date, $t_locked, $t_sticky, $t_global);
				
		while($stmt->fetch())
		{
			$topics[$topic_id]['forum_id'] = $t_forum_id;
			$topics[$topic_id]['title'] = $t_title;
			$topics[$topic_id]['forum_title'] = $t_forum_t;
			$topics[$topic_id]['content'] = $t_content;
			$topics[$topic_id]['content_plain'] = $t_content_p;
			$topics[$topic_id]['created'] = $this->longDate($t_created);
			$topics[$topic_id]['view_count'] = $t_views;
			$topics[$topic_id]['reply_count'] = $t_replies;
			$topics[$topic_id]['author'] = $t_author;
			$topics[$topic_id]['user_id'] = $t_user_id;
			$topics[$topic_id]['last_id'] = $t_last_id;
			$topics[$topic_id]['last_author'] = $t_last_author;
			$topics[$topic_id]['last_date'] = $t_last_date;
			$topics[$topic_id]['locked'] = $t_locked;
			$topics[$topic_id]['sticky'] = $t_sticky;
			$topics[$topic_id]['global_sticky'] = $t_global;
		}
	}
	
	// Bind forums into array
	private function bindForums(&$stmt, &$forums)
	{
		$stmt->bind_result($forum_id, $parent_id, $f_title, $f_content, $f_content_plain, $f_topic_count, $f_reply_count, 
			$f_last_id, $f_last_author, $f_last_date);
					
		while ($stmt->fetch())
		{
			$forums[$forum_id]['parent'] = $parent_id;
			$forums[$forum_id]['title'] = $f_title;
			$forums[$forum_id]['content'] = $f_content;
			$forums[$forum_id]['content_plain'] = $f_content_plain;
			$forums[$forum_id]['topic_count'] = $f_topic_count;
			$forums[$forum_id]['reply_count'] = $f_reply_count;
			$forums[$forum_id]['last_id'] = $f_last_id;
			$forums[$forum_id]['last_author'] = $f_last_author;
			$forums[$forum_id]['last_date'] = $f_last_date;
		}
	}

	/**
	* Utility function to generate long dates on replies (full day names, commas etc...)
	*
	* @param string $dt Raw datestamp from database
	* @return string $txt Long date
	*/
	private function longDate($dt)
	{
		return date('l, F jS, Y', strtotime($dt));
	}
}
Advertisements

6 thoughts on “Forum core source dump (as of July 31)

  1. Pingback: I have developer ADHD « This page intentionally left ugly

  2. This was a very interesting read for me!
    Although I have a question,
    I am a beginning php programmer, currently developing drupal modules on my internship, but looking to develop some sort of forum script/cms/framework/… . I’m not planning on actually making it public, it’s just a personal project to teach myself some things. So, my question…, I saw in your main.php you do:
    Core::getInstance and Modules::getInstance, is it the same as doing:
    $core = new Core();
    $core->getInstance(…); ? and did you have to define ‘Core’ somewhere or does php automatically recognise it as a class and searches for it (with the autoload function you wrote)?
    thanks in advance!

    You can contact me on my email if you want

    • Hi Jelle

      Yes, the basic idea is to let the autoload function load the class dynamically so you wouldn’t need to declare it elsewhere.

      Technically $core = new Core(); wouldn’t work in this case becuse the __construct function in that class is set to private.

      So you would always have to use the getInstance method when you want to create a new instance unless you’re calling new Core() inside the Core class itself.

  3. Pingback: (Snippet) Blocking IPs from a list file with PHP « This page intentionally left ugly

  4. Pingback: PHP Plugin/Module system | This page intentionally left ugly

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s