db.php | searchcode

/db/db.php

https://bitbucket.org/houmanf/meyoubook
PHP | 479 lines | 402 code | 74 blank | 3 comment | 70 complexity | 5f582a725246531bd9b853ef159f53d0 MD5 | raw file

<?php
include_once $_SERVER['DOCUMENT_ROOT']."/config.php";

class db {
	public static function insertBook($book) {
		$err = false;
		$isbns = explode(", ", $book["isbn"]);
		$isbn10 = $isbns[0];
		$isbn13 = $isbns[1];

		$length = str_replace(" pages","",$book['length']);

		$con = mysql_connect(SQLSERVER,SQLUSER,SQLPASS);
		if (!$con) {
			$err = true;
		}
		mysql_select_db(DBNAME, $con);

		if (!$err) {

			// scape the bad chars
			$isbn10 = mysql_escape_string($isbn10);
			$isbn13 = mysql_escape_string($isbn13);
			$title = mysql_escape_string($book['title']);
			$authors = mysql_escape_string($book['authors']);
			$publisher = mysql_escape_string($book['publisher']);
			$subjects = mysql_escape_string($book['subjects']);
			$editors = mysql_escape_string($book['editors']);
			$year = mysql_escape_string($book['year']);
			$cloud = mysql_escape_string($book['cloud']);
			$length = mysql_escape_string($book['length']);
			$bookGoogleID = mysql_escape_string($book['bookGoogleID']);
			$frontCover = mysql_escape_string($book['frontCover']);

			// save in the db
			$q1 = "INSERT INTO `book` (`isbn10`,`isbn13`,`title`,`authors`,`publisher`,`subjects`,`editors`,`year`,`cloud`,`length`,`bookgoogleid`,`frontcover`) VALUES ('" .$isbn10. "','" .$isbn13. "','" .$title. "','" .$authors. "','" .$publisher. "','" .$subjects. "','" .$editors. "','" .$year. "','" .$cloud. "','" .$length. "','" .$bookGoogleID. "','" .$frontCover. "')";
			$r1 = mysql_query($q1);
		}

		mysql_close($con);

		if ($err) {
			return false;
		} else {
			return true;
		}
	}

	public static function searchByISBN ($isbn) {
		$err = false;
		$con = mysql_connect(SQLSERVER,SQLUSER,SQLPASS);
		if (!$con) {
			$err = true;
		}
		mysql_select_db(DBNAME, $con);

		if (!$err) {
			$isbn = mysql_real_escape_string($isbn);
			$q1 = "SELECT `title`,`authors`,`publisher`,`length`,`isbn10`, `isbn13`, `frontcover` FROM `book` WHERE `isbn10`='".$isbn."' OR `isbn13`='".$isbn."';";

			$r1 = mysql_query($q1);
			if (mysql_num_rows($r1)==1) {
				$row = mysql_fetch_array($r1);
				$length = "n/a";
				if ($row["length"]>0) {
					$length = $row["length"]." pages";
				}
				$book = array("isbn" => $row["isbn10"].", ".$row["isbn13"], "title" => $row["title"], "authors" => $row["authors"], "publisher" => $row["publisher"], "length" => $length, "frontCover" => $row["frontcover"]);
			} else {
				$book = false;
			}
		}

		mysql_close($con);
		if (!$err) {
			return $book;
		} else {
			return false;
		}
	}

	public static function insertDistances($values) {

		$err = array();

		$q = "INSERT INTO `distances` (`zipcode1`,`zipcode2`,`distance`) VALUES ".$values.";";

		$r = mysql_query($q);

		if (!$r) {
			$err['insert'] = 1;
		}

		if ($err==null) {
			return true;
		} else {
			return  false;
		}
	}

	public static function addSellingBook($isbn, $email, $zipcode, $price, $condition, $pin) {
		$err = array();
		$con = mysql_connect(SQLSERVER,SQLUSER,SQLPASS);

		if (!$con) {
			$err['sqlConnection'] = 1;
		}
		mysql_select_db(DBNAME, $con);

		if ($err==null) {

			if (!is_numeric($price)) {
				$err['price']=1;
			}

			if ($err==null) {

				$isbn = mysql_real_escape_string($isbn);
				$email = mysql_real_escape_string($email);
				$zipcode = mysql_real_escape_string($zipcode);
				$price = mysql_real_escape_string($price);
				$condition = mysql_real_escape_string($condition);
				$pin = mysql_real_escape_string($pin);

				$startTrans = "START TRANSACTION";
				mysql_query($startTrans);
				$autoCommit = "SET autocommit = 0";
				mysql_query($autoCommit);

				$q1 = "INSERT IGNORE INTO `user` (`email`) VALUES ('" .$email. "');";
				$q2 = "INSERT IGNORE INTO `UB` (`uid`,`bid`,`zipcode`,`price`, `condition`, `pin`) VALUES ((SELECT uid FROM `user` WHERE email='" .$email. "'),(SELECT bid FROM `book` WHERE isbn10='" .$isbn. "' OR isbn13='" .$isbn. "'),'" .$zipcode. "','" .$price. "','" .$condition. "','" .$pin. "');";

				$r1 = mysql_query($q1);
				$r2 = mysql_query($q2);
				$ubid = mysql_insert_id($con);

				if (!$r1 || !$r2) {
					$err['insert'] = 1;
					$rollbackTrans = "ROLLBACK;";
					mysql_query($rollbackTrans);
				} else {
					$commitTrans = "COMMIT;";
					mysql_query($commitTrans);
				}
			}
		}
		//		print_r($err);
		mysql_close($con);
		if ($err==null) {
			return $ubid;
		} else {
			return false;
		}
	}

	public static function searchByTitle ($title,$zipcode) {
		$err = array();
		$con = mysql_connect(SQLSERVER,SQLUSER,SQLPASS);
		if (!$con) {
			$err['sqlConnection'] = 1;
		}
		mysql_select_db(DBNAME, $con);

		if ($err==null) {
			$title = mysql_real_escape_string($title);
			$zipcode = mysql_real_escape_string($zipcode);
			if (strlen($title)==0) {
				$q1 = "SELECT * FROM (SELECT zipcode2, distance FROM `distances` WHERE zipcode1='".$zipcode."') d INNER JOIN  `UB` ub INNER JOIN `book` b INNER JOIN `user` u
						ON ub.uid = u.uid AND ub.bid = b.bid AND d.zipcode2=ub.zipcode
						WHERE ub.available = 1
						ORDER BY b.title, price ASC, d.distance LIMIT ".SEARCH_TITLE_LIMIT.";";
			} else {
				$q1 = "SELECT * FROM (SELECT zipcode2, distance FROM `distances` WHERE zipcode1='".$zipcode."') d INNER JOIN  `UB` ub INNER JOIN `book` b INNER JOIN `user` u INNER JOIN `book_FT` bft
						ON ub.uid = u.uid AND ub.bid = b.bid AND d.zipcode2=ub.zipcode AND bft.bid=ub.bid 
						WHERE ub.available = 1 AND MATCH (bft.title) AGAINST ('".$title."')
						ORDER BY b.title, price ASC, d.distance LIMIT ".SEARCH_TITLE_LIMIT.";";
			}
			$r1 = mysql_query($q1);
			if (!$r1) {
				$err['select'] = 1;
			} else {
				$books = $r1;
			}
		}

		mysql_close($con);
		if ($err==null) {
			return $books;
		} else {
			return false;
		}
	}

	static function insertUBUandUBUE ($buyerEmail, $ubid, $message, $spin, $bpin) {
		$err = null;
		$con = mysql_connect(SQLSERVER,SQLUSER,SQLPASS);
		if (!$con) {
			$err['sqlConnection'] = 1;
		}
		mysql_select_db(DBNAME, $con);

		if ($err==null) {

			$buyerEmail = mysql_real_escape_string($buyerEmail);
			$ubid = mysql_real_escape_string($ubid);
			$message = mysql_real_escape_string($message);
			$spin = mysql_real_escape_string($spin);
			$bpin = mysql_real_escape_string($bpin);

			$startTrans = "START TRANSACTION";
			mysql_query($startTrans);
			$autoCommit = "SET autocommit = 0";
			mysql_query($autoCommit);

			$q1 = "INSERT IGNORE INTO `user` (`email`) VALUES ('" .$buyerEmail. "');";
			$r1 = mysql_query($q1);

			$q2 = "INSERT IGNORE INTO `UBU` (`ubid`,`uid`,`spin`,`bpin`) VALUES (" .$ubid. ", (SELECT uid FROM `user` WHERE email='" .$buyerEmail. "'),'".$spin."','".$bpin."');";
			$r2 = mysql_query($q2);
			$ubuid = mysql_insert_id($con);

			$q3 = "INSERT INTO `email` (`message`) VALUES ('" .$message. "');";
			$r3 = mysql_query($q3);
			$eid = mysql_insert_id($con);

			$q4 = "INSERT INTO `UBUE` (`ubuid`,`eid`) VALUES (".$ubuid.",".$eid.");";
			$r4 = mysql_query($q4);

			if (!$r1 || !$r2 || !$r3 || !$r4) {
				$err['insert'] = 1;
				$rollbackTrans = "ROLLBACK;";
				mysql_query($rollbackTrans);
			} else {
				$commitTrans = "COMMIT;";
				mysql_query($commitTrans);
			}
		}
		return (($err==null) ? $ubuid : false);
	}

	static function insertUBUE ($ubuid, $message) {
		$err = array();
		$con = mysql_connect(SQLSERVER,SQLUSER,SQLPASS);
		if (!$con) {
			$err['sqlConnection'] = 1;
		}
		mysql_select_db(DBNAME, $con);

		if ($err==null) {

			$ubuid = mysql_real_escape_string($ubuid);
			$message = mysql_real_escape_string($message);

			mysql_query("START TRANSACTION");
			mysql_query("SET autocommit = 0");

			$q1 = "INSERT INTO `email` (`message`) VALUES ('" .$message. "');";
			$r1 = mysql_query($q1);
			$eid = mysql_insert_id($con);

			$q2 = "INSERT INTO `UBUE` (`ubuid`,`eid`) VALUES (".$ubuid.",".$eid.");";
			$r2 = mysql_query($q2);

			if (!$r1 || !$r2) {
				$err['insert'] = 1;
				mysql_query("ROLLBACK;");
			} else {
				mysql_query("COMMIT;");
			}
		}
		return (($err==null) ? $ubuid : false);
	}

	static function getUB ($ubid) {
		$err = array();
		$con = mysql_connect(SQLSERVER,SQLUSER,SQLPASS);
		if (!$con) {
			$err['sqlConnection'] = 1;
		}
		mysql_select_db(DBNAME, $con);

		if ($err==null) {

			$ubid = mysql_real_escape_string($ubid);

			$q1 = "SELECT `frontCover`, `price`, `condition`, `title`, `authors`, `isbn10`, `isbn13`, `publisher`, `length`, `email`, `zipcode`, ub.`available`, `pin` as ubpin FROM `UB` ub INNER JOIN `book` b INNER JOIN `user` u ON ub.bid = b.bid AND ub.uid = u.uid
					WHERE ub.id='".$ubid."';";
			$r1 = mysql_query($q1);

			if (mysql_num_rows($r1)==1) {
				$row = mysql_fetch_array($r1);
				switch ($row['condition']) {
					case 'n' :
						$condition = 'New';
						break;
					case 'ln' :
						$condition = 'Like new';
						break;
					case 'vg' :
						$condition = 'Very good';
						break;
					case 'g' :
						$condition = 'Good';
						break;
					case 'b' :
						$condition = 'Bad';
						break;
				}
				$length = "n/a";
				if ($row["length"]>0) {
					$length = $row["length"]." pages";
				}
				$ub = array("frontCover" => $row["frontCover"], "price" => $row["price"], "condition" => $condition, "title" => $row["title"], "authors" => $row["authors"], "isbn10" => $row["isbn10"], "isbn13" => $row["isbn13"], "publisher" => $row["publisher"], "length" => $length, "email" => $row["email"], "zipcode" => $row["zipcode"], "available" => $row["available"], "ubPin" => $row["ubpin"]);
			} else {
				$ub = false;
			}
		}
		return $ub;
	}


	static function getUBdoubleCheck ($ubid, $pin) {
		$err = array();
		$con = mysql_connect(SQLSERVER,SQLUSER,SQLPASS);
		if (!$con) {
			$err['sqlConnection'] = 1;
		}
		mysql_select_db(DBNAME, $con);

		if ($err==null) {

			$ubid = mysql_real_escape_string($ubid);
			$pin = mysql_real_escape_string($pin);

			$q1 = "SELECT `frontCover`, `price`, `condition`, `title`, `authors`, `isbn10`, `isbn13`, `publisher`, `length`, `email`, `zipcode`, ub.`available`, `pin` as ubpin FROM `UB` ub INNER JOIN `book` b INNER JOIN `user` u ON ub.bid = b.bid AND ub.uid = u.uid
					WHERE ub.id='".$ubid."' AND ub.pin='".$pin."';";
			$r1 = mysql_query($q1);

			if (mysql_num_rows($r1)==1) {
				$row = mysql_fetch_array($r1);
				switch ($row['condition']) {
					case 'n' :
						$condition = 'New';
						break;
					case 'ln' :
						$condition = 'Like new';
						break;
					case 'vg' :
						$condition = 'Very good';
						break;
					case 'g' :
						$condition = 'Good';
						break;
					case 'b' :
						$condition = 'Bad';
						break;
				}
				$length = "n/a";
				if ($row["length"]>0) {
					$length = $row["length"]." pages";
				}
				$ub = array("frontCover" => $row["frontCover"], "price" => $row["price"], "condition" => $condition, "title" => $row["title"], "authors" => $row["authors"], "isbn10" => $row["isbn10"], "isbn13" => $row["isbn13"], "publisher" => $row["publisher"], "length" => $length, "email" => $row["email"], "zipcode" => $row["zipcode"], "available" => $row["available"], "ubPin" => $row["ubpin"]);
			} else {
				$ub = false;
			}
		}
		return $ub;
	}


	static function removeUB ($ubid, $pin) {
		$err = array();
		$con = mysql_connect(SQLSERVER,SQLUSER,SQLPASS);
		if (!$con) {
			$err['sqlConnection'] = 1;
		}
		mysql_select_db(DBNAME, $con);

		if ($err==null) {

			$ubid = mysql_real_escape_string($ubid);
			$pin = mysql_real_escape_string($pin);

			$q1 = "UPDATE `UB` ub SET available = '0'
					WHERE id='".$ubid."' AND pin='".$pin."';";
			$r1 = mysql_query($q1);
			if (mysql_affected_rows() != 1) {
				$err['alreadyRemoved'] = false;
			}
			if ($err==null) {
				$out = true;
			}
		} else {
			$our = false;
		}
		return $out;
	}

	static function getCommunicationInfo($ubuid,$pin) {
		$con = mysql_connect(SQLSERVER,SQLUSER,SQLPASS);
		if (!$con) {
			$err['sqlConnection'] = 1;
		}
		mysql_select_db(DBNAME, $con);

		if ($err==null) {
			$ubuid = mysql_real_escape_string($ubuid);
			$pin = mysql_real_escape_string($pin);

			$q1 = "SELECT useller.email semail, ub.id as ubid, ub.pin as ubpin, ubuyer.email bemail, e.message, e.date, spin, bpin, title, `condition`, price FROM `UBU` ubu INNER JOIN `UBUE` ubue INNER JOIN `UB` ub INNER JOIN `book` b INNER JOIN `email` e INNER JOIN `user` ubuyer INNER JOIN `user` useller
					ON ubu.ubid = ub.id AND ub.uid = useller.uid AND b.bid=ub.bid AND ubu.uid = ubuyer.uid AND ubue.ubuid = ubu.id AND ubue.eid = e.eid
					WHERE ubu.id = ".$ubuid." AND (spin = '".$pin."' OR bpin = '".$pin."') ORDER BY e.date DESC";

			$r1 = mysql_query($q1);

			$row = mysql_fetch_array($r1);

			$sEmail = $row['semail'];
			$bEmail = $row['bemail'];
			$sPin = $row['spin'];
			$bPin = $row['bpin'];
			$title = $row['title'];
			switch ($row['condition']) {
				case 'n' :
					$condition = 'New';
					break;
				case 'ln' :
					$condition = 'Like new';
					break;
				case 'vg' :
					$condition = 'Very good';
					break;
				case 'g' :
					$condition = 'Good';
					break;
				case 'b' :
					$condition = 'Bad';
					break;
			}
			$price = $row['price'];
			$history = $row['message'];
			$ubPin = $row['ubpin'];
			$ubid = $row['ubid'];
			while ($row = mysql_fetch_array($r1)) {
				$history .= "\r\n--------------------\r\n".$row['message'];
			}
			$out = array('sEmail' => $sEmail, 'bEmail' => $bEmail, 'history' => $history, 'sPin' => $sPin, 'bPin' => $bPin, 'title' => $title, 'price' => $price, 'condition' => $condition, 'ubPin' => $ubPin, 'ubid' => $ubid);
		}
		return (($err==null) ? $out : false);
	}

	static function validZipcode($zipcode) {
		$err = array();
		$con = mysql_connect(SQLSERVER,SQLUSER,SQLPASS);
		if (!$con) {
			$err['sqlConnection'] = 1;
		}
		mysql_select_db(DBNAME, $con);

		if ($err==null) {
			$zipcode = mysql_real_escape_string($zipcode);			

			$q1 = "SELECT `zipcode` FROM `zipcode` WHERE zipcode='".$zipcode."';";

			$r1 = mysql_query($q1);
			if (!$r1) {
				$err['select'] = 1;
			}

			if (mysql_num_rows($r1)==1) {
				$out = true;
			}
		}
		if (!$err==null) {
			$out = false;
		}
		return $out;
	}
}