MyCollegeExchange is the project that eventually inspired me to develop UniversityLite, a rapid deployment e-commerce tool to market university products and information to university students over the web using PHP and other tools. UniversityLite has it’s own section dedicated on the homepage for more information.

Getting back to MyCollegeExchange. Sometimes you just want to build a website from scratch. No CMS, no templates. Not always the prettiest, but the result here became MyCollegeExchange and features a website that allows students to buy and sell used textbooks on College Campuses. The website was built using PHP, JavaScript, HTML, CSS and has a SQL backend.

MyCollegeExchange Website

Some of the noted areas I had to develop for the site were:

InnoDB SQL Database Using Referential Integrity

In order to allow books, members and transactions to be kept as separate entities, I constructed a database with certain constraints. The books table uses an auto incrementing Primary Key that is referenced by the selling table. Additionally the members table has a Primary Key also referenced by the selling table. This table is then later joined using JOIN to generate all the necessary components for who is selling what, when and where.  The database has been normalized to the 3rd Normal Form.

The website has been developed to be easily deployed in a new setting by creating the necessary SQL database based on variables set in a settings file that is called during installation:

$data='";
			file_put_contents("./data/settings.php",$data);
			echo '
';
			$con=mysql_connect($_POST['hostname'],$_POST['dbuname'],$_POST['dbpwd']);
			echo '
';
			if ($con)
			{
				if(mysql_select_db($_POST['dbname'], $con))
				{
					$sql = "CREATE TABLE IF NOT EXISTS `user_info` (
							`Username` varchar(32) NOT NULL DEFAULT '',
							`Password` varchar(32) DEFAULT NULL,
							`email` varchar(40) NOT NULL,
							PRIMARY KEY (`Username`)
							) ENGINE=MyISAM DEFAULT CHARSET=latin1;";
					mysql_query($sql,$con);

The database is queried and updated using mysql_connect, mysql_select_db, mysql_query and mysqli_query PHP statements. For example, this snippet updates the selling table when posting a book using the Post form on the website:

$db = mysqli_connect("$LOGIN_DB_HOSTNAME", "$LOGIN_DB_UNAME", "$LOGIN_DB_PWD", "$LOGIN_DB_NAME");
$postSelling = "INSERT INTO selling (BookNumber, Username, Date, Price, ConditionOfBook, Description)
VALUES ('$booknumber','$user','$todaysdate','$price','$conditionofbook','$description')";

Search 

Algorithm

In order to generate search results, I developed a simple search algorithm that uses SQL JOIN statements to link our three tables.

if(isset($_POST['submit'])){
  if(isset($_GET['go']))			{
  if(preg_match("/^[  a-zA-Z]+/", $_POST['name'])){
  $name=$_POST['name'];
  
  //connect  to the database
  $db=mysql_connect  ("$LOGIN_DB_HOSTNAME", "$LOGIN_DB_UNAME",  "$LOGIN_DB_PWD") or die ('I cannot connect to the database  because: ' . mysql_error());
  //-select  the database to use
  $mydb=mysql_select_db("$LOGIN_DB_NAME");
  //-query  the database table
  $sql = "SELECT user_info.Username, books.BookTitle, books.ISBN, books.Author, selling.Date, selling.Price FROM selling
    JOIN books
        ON books.BookNumber = selling.BookNumber
    JOIN user_info
        ON user_info.Username = selling.Username 
	WHERE books.BookTitle LIKE '%" . $name . "%' OR books.Author LIKE '%" . $name ."%' OR books.ISBN LIKE '%" . $name ."%'";

  $result=mysql_query($sql);

  while($row=mysql_fetch_array($result)){
          $BookTitle =$row['BookTitle'];
          $Author=$row['Author'];
          $ISBN=$row['ISBN'];
		  $Price=$row['Price'];
		  
  echo "
" . "$" . $Price . " " . $BookTitle . "
";
  echo "Author: " . $Author . " ISBN: " . $ISBN . "
";
  echo nl2br("\n");
 }

}}}

Automatic Page Generation For Results

Once we get our search results based on the JOIN operation, each result becomes a clickable link that takes the user to auto-generated PHP page with more detailed results:

if (isset($_GET['id'])) {
   $ISBN = $_GET['id'];

  $db=mysql_connect  ("$LOGIN_DB_HOSTNAME", "$LOGIN_DB_UNAME",  "$LOGIN_DB_PWD") or die ('Can't Connect: ' . mysql_error());
  $mydb=mysql_select_db("$LOGIN_DB_NAME");
  $sql = "SELECT user_info.Username, user_info.Email, books.*, selling.* FROM selling
    JOIN books
        ON books.BookNumber = selling.BookNumber
    JOIN user_info
        ON user_info.Username = selling.Username 
	WHERE books.ISBN LIKE '%" . $ISBN ."%'";
  //-run  the query against the mysql query function
  $result=mysql_query($sql); 
    while($row=mysql_fetch_array($result)){
          $BookTitle =$row['BookTitle'];
          $Author=$row['Author'];
          $ISBN=$row['ISBN'];
		  $Price=$row['Price'];
		  $Username=$row['Username'];
		  $Date=$row['Date'];
		  $Email=$row['Email'];
		  $ConditionOfBook=$row['ConditionOfBook'];
		  $Description=$row['Description'];
		  
  echo "

Live Form Validation

The forms used to register, post and manage posts have automatic line validation as the user progresses through the form. Each form calls a javascript function for validation. For example, here we have the javascript functions to validate the Post a Book form:

function validate_book()
  {
  if (flag6==1 && flag7==1 && flag8==1 && flag9==1 && flag10==1)
  {
  alert("Everything Checked Out. Your book is posted!");
  return true;
  }
  else
  {
  alert("We found some errors in your book information.");
  }
  }

function validate_booktitle()
{
	var booktitle = document.getElementById("booktitle").value;
	var valbooktitle = booktitle.search(/^[a-zA-Z0-9_]+/);
  if (booktitle = ""||valbooktitle != 0)
    {
	document.getElementById("booktitleInfo").className="error";
		document.getElementById("booktitle").className="error";
		document.getElementById("booktitleInfo").innerHTML="Title must be only letters or numbers";
	}
  else 
  {
  document.getElementById("booktitleInfo").className="success";
		document.getElementById("booktitle").className="success";
		document.getElementById("booktitleInfo").innerHTML="Title looks good!";
		flag6=1;
  }
  }
  
function validate_bookauthor()
{
	var bookauthor = document.getElementById("bookauthor").value;
	var valbookauthor = bookauthor.search(/[a-zA-Z]+/);
  if (bookauthor = ""||valbookauthor != 0)
    {
	document.getElementById("bookauthorInfo").className="error";
		document.getElementById("bookauthor").className="error";
		document.getElementById("bookauthorInfo").innerHTML="Author must contain only letters";
	}
  else 
  {
  document.getElementById("bookauthorInfo").className="success";
		document.getElementById("bookauthor").className="success";
		document.getElementById("bookauthorInfo").innerHTML="Author looks good!";
		flag7=1;
  }
  }
  
function validate_bookisbn()
{
	var bookisbn = document.getElementById("bookisbn").value;
	var valbookisbn = bookisbn.search(/^\d{10,13}$/);
  if (bookisbn = ""||valbookisbn != 0)
    {
	document.getElementById("bookisbnInfo").className="error";
		document.getElementById("bookisbn").className="error";
		document.getElementById("bookisbnInfo").innerHTML="ISBN must be at 10 or 13 numbers only";
	}
  else 
  {
  document.getElementById("bookisbnInfo").className="success";
		document.getElementById("bookisbn").className="success";
		document.getElementById("bookisbnInfo").innerHTML="ISBN checks out!";
		flag8=1;
  }
  }
  
function validate_bookprice()
{
	var bookprice = document.getElementById("bookprice").value;
	var valbookprice = bookprice.search(/^\d{1,}$/);
  if (bookprice = ""||valbookprice != 0)
    {
	document.getElementById("bookpriceInfo").className="error";
		document.getElementById("bookprice").className="error";
		document.getElementById("bookpriceInfo").innerHTML="Please use whole dollars only such as 8. Pennies are for pansies";
	}
  else 
  {
  document.getElementById("bookpriceInfo").className="success";
		document.getElementById("bookprice").className="success";
		document.getElementById("bookpriceInfo").innerHTML="What a great price!";
		flag9=1;
  }
  }
  

function validate_bookdescription()
{
	var bookdescription = document.getElementById("bookdescription").value;
	var valbookdescription = bookdescription.search(/^[a-zA-Z0-9_]+/);
  if (bookdescription = ""||valbookdescription != 0)
    {
	document.getElementById("bookdescriptionInfo").className="error";
		document.getElementById("bookdescription").className="error";
		document.getElementById("bookdescriptionInfo").innerHTML="Please use only letters or numbers";
	}
  else 
  {
  document.getElementById("bookdescriptionInfo").className="success";
		document.getElementById("bookdescription").className="success";
		document.getElementById("bookdescriptionInfo").innerHTML="Cool!";
		flag10=1;
  }
  }

Business Plan

The website concept was guided with a business and marketing plan that looked at competition, funding, etc.