Skip to content

Instantly share code, notes, and snippets.

@vamonke

vamonke/AY17-18 S1.md

Last active November 30, 2018 02:35
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save vamonke/d8792fc7b6db8fa492c81622b11530c7 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save vamonke/d8792fc7b6db8fa492c81622b11530c7 to your computer and use it in GitHub Desktop.
Download ZIP
IM4717 AY17/18 Sem 1
Raw
AY17-18 S1.md

IM4717 Web Application Design AY17/18 Sem 1

I have no idea if the answers are correct. Please ask any question/queries in the comments sections below.

Raw
Q1(d).md

d. PHP and MySQL

i. Database design

Books

  • isbn | varchar(255) | PRIMARY
  • author | varchar(255)
  • title | varchar(255)
  • price | float
  • qty_sold | int
  • catagory | varchar(255)

Orders

  • order_id | int | PRIMARY | AUTO INCREMENT
  • isbn | varchar(255)
  • uid | varchar(255)
  • qty_ordered | int

Users

  • name | varchar(255)
  • uid | varchar(255) | PRIMARY
  • password | varchar(255)
  • isAdmin | Boolean

ii. Update qty_sold

$sql = "SELECT SUM(qty_ordered) AS new_qty
        FROM order WHERE isbn = '1234-1234-1234'"; // sql command (query)
$results = mysqli_query($conn, $sql); // run query in MySQL to get results table
$row = mysql_fetch_assoc($result); // get row from results table
$new_qty = $row['new_qty']; // get value from row
$sql = "UPDATE books SET qty_sold = $new_qty
        FROM order WHERE isbn = '1234-1234-1234'";
mysqli_query($conn, $sql); // run sql command in MySQL

iii. Get titles of top 5 most popular books

$sql = 'SELECT titles FROM books ORDER BY qty_sold DESC LIMIT 5';
$results = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
    while ($row = mysqli_fetch_assoc($result)) {
        echo $row['title']."<br/>";
    }
} // else: no books

iv. User authentication

// Get username and password from POST
$userid = $_POST['userid'];
$password = $_POST['password'];
$password = md5($password);

// Get user with matching UID & password from MySQL
$sql = "SELECT * FROM user WHERE uid = '$userid' AND password = '$password'";
$results = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) == 1) {
  // User found
  $row = mysql_fetch_assoc($result); // get first row of result
  if ($row['isAdmin']) // true/false
    echo 'User is admin';
  else
    echo 'User is customer';
} else {
  echo 'Wrong userid/password':
}
Raw
Q1(e).md

e. Update and display books in same page

Sequence

Part (a): Book search

  • User enters search type and search term
  • Redirect to results page

Part (b): Result of book search

  • Search type and search term stored in $_POST
  • Save search type and search term in $_SESSION for later use
  • Display books according to search type and search term

Part (c): Enter new price

  • User enters new price for a book
  • Redirect to results page

Part (d): Price update

  • New price and ISBN in $_POST
  • Update book according to new price and ISBN
  • Display books according to search type and search term from $_SESSION

results.php

  1. Check for isbn and price in $_POST. If they exist, update price.
  2. If result page came from search i.e. part (b), get search type and search term from $_POST, and save them in $_SESSION to be used later in part (d). If result page came from price update i.e. part (d), get search type and search term from $_SESSION.
  3. Display books according to search type and search term.

Code

// Start session
session_start();

// Check for isbn and price in $_POST
if (isset($_POST['isbn']) && isset($_POST['price'])) {
  $isbn = $_POST['isbn'];
  $price = $_POST['price'];
  // Update price
  $sql = "UPDATE books SET price = $price WHERE isbn = '$isbn'";
  mysqli_query($conn, $sql);
}

echo '<h1>eBookStore Search Results</h1>';

// Get search type and search term
if (isset($_POST['type']) && isset($_POST['term']) {
  // part (b)
  // Get search type and search term from $_POST
  $type = $_POST['type'];
  $term = $_POST['term'];
  // Store in session for future use (part (d))
  $_SESSION['type'] = $type;
  $_SESSION['type'] = $term;
} else {
  // part (d)
  // Reuse search type and search term from session
  $type = $_SESSION['type'];
  $term = $_SESSION['term'];
}

// Display search results
$sql = "SELECT * FROM books WHERE '$type' = '$term'";
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
  // Books found
  $counter = 1;
  while ($row = mysqli_fetch_assoc($result)) {
      echo "<b>".$counter.". Title: ".$row['title']."</b><br/>";
      echo "Author: ".$row['author']."<br/>";
      echo "ISBN: ".$row['isbn']."<br/>";
      echo "Price: ".$row['price']."<br/>";
      echo "<form action='result.php'>";
      echo "  <input type='hidden' name='isbn' value='".$row['isbn']."'>";
      echo "  <input type='text' name='price'>";
      echo "  <input type='submit' value='Update'>";
      echo "</form>";
      $counter++;
  }
} // else: No books found
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment