Location: PHPKode > projects > Cadence Guestbook Host > cadence/class/queries.php
<?php

/************************************
 * Cadence
 * Remotely Hosted Guestbook Script.
 * (c) 2006, Dennis Pedrie
 * www.CadenceBook.com
 * queries.php
 ***********************************
 * Cadence Guestbook is licensed under
 * a Creative Commons License.
 * More information is available by visiting
 * http://creativecommons.org/licenses/by/3.0/
 * or the LICENSE file in the Cadence Root Folder
 ***********************************/

/**
* Query Class. Seperates MySQL from PHP.<br />
* This allows for easy access to the queries in the event they need editing.<br />
* It also allows for cleaner, more readable code.<br />
* All queries must be surrounded by quotation marks, and returned to the PHP code.<br />
* Conditionals are allowed.
*
* @name queries
* @package	Cadence Guestbook
* @author   Dennis Pedrie
* @copyright Dennis Pedrie
* @version	1.0
*/

class queries {
	
	/**
	 * Queries for Guestbook Settings
	 *
	 * @name getsettings
	 * @author Dennis Pedrie
	 * @version 1.0 BUILD 1000
	 * @param int Guestbook ID
	 * @return string
	 *
	 **/
	function getsettings($book) {
		return "SELECT * FROM ". TABLE_PREFIX ."gbooks WHERE gbook_id = '$book'";
	}
	
	/**
	 * Queries for Guestbook Posts.
	 *
	 * @name getposts
	 * @author Dennis Pedrie
	 * @version 1.0 BUILD 1000
	 * @param int Guestbook ID
	 * @return string
	 *
	 **/
	
	function getposts($book) {
		global $gbook;
		
		//No Validation
		if($gbook->val_type == 0) {
			return "SELECT * FROM ". TABLE_PREFIX ."posts WHERE post_gbook = '$book' ORDER BY post_id DESC";
		}
		
		//E-Mail Validation
		if($gbook->val_type == 1) {
			return "SELECT * FROM ". TABLE_PREFIX ."posts WHERE post_email_approved = '1' AND post_gbook = '$book' ORDER BY post_id DESC";
		}
		
		//Admin Validation
		if($gbook->val_type == 2) {
			return "SELECT * FROM ". TABLE_PREFIX ."posts WHERE post_admin_approved = '1' AND post_gbook = '$book' ORDER BY post_id DESC";
		}
		
		//Both
		if($gbook->val_type == 3) {
			return "SELECT * FROM ". TABLE_PREFIX ."posts WHERE post_admin_approved = '1' AND post_email_approved = '1' AND post_gbook = '$book' ORDER BY post_id DESC";
		}
		
	}
	
	/**
	 * Verifies Post Hash. Used to approve Posts
	 *
	 * @name getappid
	 * @author Dennis Pedrie
	 * @version 1.0 BUILD 1000
	 * @param string Post Hash
	 * @return string
	 *
	 **/
	function getappid($approve) {
		return "SELECT post_id FROM ". TABLE_PREFIX ."posts WHERE post_hash = '$approve'";
	}
	
	/**
	 * Approve The Post
	 *
	 * @name approve
	 * @author Dennis Pedrie
	 * @version 1.0 BUILD 1000
	 * @param int Post ID
	 * @return string
	 *
	 **/
	function approve($id) {
		return "UPDATE ". TABLE_PREFIX ."posts SET post_approved = 1 WHERE post_id = '$id'";
	}
	
	/**
	 * Inserts Comment into the database
	 *
	 * @name addcomment
	 * @author Dennis Pedrie
	 * @version 1.0 BUILD 1000
	 * @param string Display Name
	 * @param string E-Mail Address
	 * @param string IP Address
	 * @param string Post Content
	 * @param int Unix Timestamp Post Date
	 * @param string User's Browser
	 * @param string Post Hash
	 * @param int Guestbook ID
	 * @param int Comment Pre-Approved?
	 * @return string
	 *
	 **/
	function addcomment($name,$email,$ip,$content,$date,$browser,$hash,$book,$approved,$logged) {
		return "INSERT INTO ". TABLE_PREFIX ."posts(post_author,post_email,post_ip,post_content,post_date,post_browser,post_hash,post_gbook,post_is_gbook_user) 
				VALUES('$name','$email','$ip','$content','$date','$browser','$hash','$book','$logged')";
	}
	
	/**
	 * Retreives Style Vars
	 *
	 * @name getstyle
	 * @author Dennis Pedrie
	 * @version 1.0 BUILD 1000
	 * @param int Style ID
	 * @return string
	 *
	 **/
	function getstyle($style) {
		return "SELECT * FROM ". TABLE_PREFIX ."style
		WHERE style_id = '$style'";
	}
	
	/**
	 * Checks if Guestbook ID is valid
	 *
	 * @name checkgbook
	 * @author Dennis Pedrie
	 * @version 1.0 BUILD 1000
	 * @param int Guestbook ID
	 * @return string
	 *
	 **/
	function checkgbook($book) {
		return "SELECT gbook_id FROM ". TABLE_PREFIX ."gbooks WHERE gbook_id = '$book'";
	}
	
	/**
	 * Retreives Custom Style ID
	 *
	 * @name customstyle
	 * @author Dennis Pedrie
	 * @version 1.0 BUILD 1000
	 * @param int Style ID
	 * @return string
	 *
	 **/
	function customstyle($styleid) {
		return "SELECT style_id FROM ". TABLE_PREFIX ."style WHERE style_id = '$styleid'";
	}
	
	/**
	 * Returns Custom Styles to choose from
	 *
	 * @name style_choose
	 * @author Dennis Pedrie
	 * @version 1.0 BUILD 1000
	 * @param int Guestbook ID
	 * @return string
	 *
	 **/
	function style_choose($book) {
		return "SELECT style_id,style_name FROM ". TABLE_PREFIX ."style WHERE style_public = 1 OR gb_id = '$book' ORDER BY style_id ASC";
	}
	
	/**
	 * Check Valid Login.<br />
	 * This is run on every page load.
	 *
	 * @name checklogin
	 * @author Dennis Pedrie
	 * @version 1.0 BUILD 1000
	 * @param string E-Mail
	 * @param string Encoded Password
	 * @param int Guestbook ID
	 * @return string
	 *
	 **/
	function checklogin($email,$hash,$book) {
		return "SELECT gbook_id FROM ". TABLE_PREFIX ."gbooks WHERE gbook_email = '$email' AND gbook_pass = '$hash'"; // AND gbook_id = '$book'";
	}
	
	/**
	 * Check Valid Login.<br />
	 * This is run only when the user is attempting to login.<br />
	 * Checks all information against the database.
	 *
	 * @name getposts
	 * @author Dennis Pedrie
	 * @version 1.0 BUILD 1000
	 * @param string E-Mail
	 * @param string Un-Encoded Password
	 * @param int Guestbook ID
	 * @return string
	 *
	 **/
	function dologin($email,$pass,$book) {
		return "SELECT gbook_display_name FROM ". TABLE_PREFIX ."gbooks WHERE gbook_email = '$email' AND gbook_pass = password('$pass')";
	}
	
	/**
	 * Get User Hash.<br />
	 * Run after we know the user is logged in. We just need to get info for the cookie.
	 *
	 * @name gethash
	 * @author Dennis Pedrie
	 * @version 1.0 BUILD 1000
	 * @param string E-Mail
	 * @param string Un-Encoded Password
	 * @param int Guestbook ID
	 * @return string
	 *
	 **/
	function gethash($email,$pass,$book) {
		return "SELECT gbook_pass FROM ". TABLE_PREFIX ."gbooks WHERE gbook_email = '$email' AND password('$pass') = gbook_pass";
	}
	
	/**
	 * Checks if the user has been banned.<br />
	 * Checks IP, E-Mail.<br />
	 * Run when a user attempts to submit a comment.
	 *
	 * @name isbanned
	 * @author Dennis Pedrie
	 * @version 1.0 BUILD 1000
	 * @param string E-Mail Address
	 * @param string IP Address
	 * @return string
	 *
	 **/
	function isbanned($email,$ip) {
		return "SELECT ban_gbook_id FROM ". TABLE_PREFIX ."banned WHERE ban_email = '$email' OR ban_ip = '$ip' AND ban_gbook_id = '". intval($_GET['book']) ."'";
	}
	
	/**
	 * Check whether this is a local or global ban.<br />
	 * Run when a user attempts to submit a comment.
	 *
	 * @name gban
	 * @author Dennis Pedrie
	 * @version 1.0 BUILD 1000
	 * @param string E-Mail Address
	 * @param string IP Address
	 * @return string
	 *
	 **/
	function gban($email,$ip) {
		return "SELECT ban_global FROM ". TABLE_PREFIX ."banned WHERE ban_email = '$email' OR ban_ip = '$ip' AND ban_global = '1'";
	}
	
	/**
	 * Query for Badword list.
	 *
	 * @name badwords
	 * @author Dennis Pedrie
	 * @version 1.0 BUILD 1000
	 * @return string
	 *
	 **/
	function badwords() {
		return "SELECT badword,replacement FROM ". TABLE_PREFIX ."badwords";
	}
	
	/**
	 * Query for ACP News
	 *
	 * @name acpnews
	 * @author Dennis Pedrie
	 * @version 1.0 BUILD 1000
	 * @return string
	 *
	 **/
	function acpnews() {
		global $CONFIG;
		return "SELECT * FROM gb_news ORDER BY news_id LIMIT 0,". $CONFIG['num_news'];
	}
	
	/**
	 * Update ACP Notes
	 *
	 * @name acpnotes
	 * @author Dennis Pedrie
	 * @version 1.0 BUILD 1000
	 * @return string
	 *
	 **/
	function acpnotes($notes) {
		global $book;
		return "UPDATE ". TABLE_PREFIX ."gbooks SET gbook_notes = '$notes' WHERE gbook_id = '$book'";
	}
	
	function editpost($content, $author, $id) {
		global $book;
		return "UPDATE ". TABLE_PREFIX ."posts SET post_content = '$content', post_author = '$author' WHERE post_id = '$id'";
	}
	
	function getposttoedit($edit) {
		global $book;
		return "SELECT * FROM ". TABLE_PREFIX ."posts WHERE post_gbook = '$book' AND post_id = '$edit'";
	}
	
	function geteditposts($st,$end,$perpage,$orderby,$order) {
		global $book;
		$st = ($st == 0 || empty($st) || !isset($st)) ? $st = 0 : $st = $st;
		$end = $st + 1 * $perpage;
		return "SELECT post_id,post_author,post_email,post_ip,post_date,post_hash,post_admin_approved,post_email_approved FROM ". TABLE_PREFIX ."posts WHERE post_gbook = '$book' ORDER BY $orderby $order LIMIT $st,$end";
	}
	
	function delete($delete,$auth) {
		return "DELETE FROM ". TABLE_PREFIX ."posts WHERE post_id = '$delete' AND post_hash = '$auth'";
	}
	
	function updatepostcount($dir) {
		global $book;
		$dir = ($dir == "up") ? "+" : "-";
		return "UPDATE ". TABLE_PREFIX ."gbooks SET gbook_posts = gbook_posts ". $dir ." 1 WHERE gbook_id = '$book'";
	}
}

?>
Return current item: Cadence Guestbook Host