function show_record($dbc, $id)
{
# Create a query to get the name and price sorted by price
$query = 'SELECT id, lname, fname FROM presidents WHERE id = ' . $id;
# Execute the query
$results = mysqli_query($dbc, $query);
check_results($results);
# Show results
if ($results) {
# But...wait until we know the query succeed before
# rendering the table start.
echo '<H1>Presidents</H1>';
echo '<TABLE>';
echo '<TR>';
echo '<TH>ID</TH>';
echo '<TH>First Name</TH>';
echo '<TH>Last Name</TH>';
echo '</TR>';
# For each row result, generate a table row
while ($row = mysqli_fetch_array($results, MYSQLI_ASSOC)) {
echo '<TR>';
echo '<TD>' . $row['id'] . '</TD>';
echo '<TD>' . $row['fname'] . '</TD>';
echo '<TD>' . $row['lname'] . '</TD>';
echo '</TR>';
}
# End the table
echo '</TABLE>';
# Free up the results in memory
mysqli_free_result($results);
}
}
function validate($user_id, $pass)
{
global $dbc;
if (empty($user_id)) {
return -2;
} else {
if (empty($pass)) {
return -3;
}
}
if ($user_id != 'admin') {
$hash = crypt($pass, $email);
} else {
$hash = $pass;
}
# Make the query
$query = "SELECT id, user_id, email, pass FROM users WHERE user_id='" . $user_id . "'";
# Execute the query
$results = mysqli_query($dbc, $query);
check_results($results);
# If we get no rows, the login failed
if (mysqli_num_rows($results) == 0) {
return -1;
}
# We have at least one row, so get the first one and return it
while ($row = mysqli_fetch_array($results, MYSQLI_ASSOC)) {
if ($row['pass'] == $hash) {
$pid = $row['id'];
return intval($pid);
} else {
return -4;
}
}
}
function insert_lost_item($dbc, $item, $owner, $location_name, $room, $description)
{
$location_id = $location_name;
#$valueString = '("' . $item . '","' . $owner . '",' . $location_id . ',"' . $room . '","' . $description . '", NOW(), NOW(), \'lost\')';
$valueString = "('{$item}', '{$owner}', {$location_id}, '{$room}', '{$description}', NOW(), NOW(), 'lost')";
$query = 'INSERT INTO stuff(item, owner, location_id, room, description, create_date, update_date, status) VALUES ' . $valueString;
#show_query($query);
$results = mysqli_query($dbc, $query);
check_results($results);
#mysqli_free_result($results)
return $results;
}
function validate($username = '', $password = '')
{
global $dbc;
if (empty($username) or empty($password)) {
return -1;
}
# Make the query
$query = "SELECT id, username, password FROM users WHERE username = '******' and password = '******' ";
# Execute the query
$results = mysqli_query($dbc, $query);
check_results($results);
# If we get no rows, the login failed
if (mysqli_num_rows($results) == 0) {
return -1;
}
# We have at least one row, so get the frist one and return it
$row = mysqli_fetch_array($results, MYSQLI_ASSOC);
$pid = $row['id'];
return intval($pid);
}
function validate($dbc, $username, $password)
{
//global $dbc;
if (empty($username) || empty($password)) {
return -1;
}
# Make the query
$query = "SELECT user_id FROM users WHERE username='******' AND pass='******'";
# Execute the query
$results = mysqli_query($dbc, $query);
check_results($results);
# If we get no rows, the login failed
if (mysqli_num_rows($results) == 0) {
return -1;
}
# We have at least one row, so get the first one and return it
$row = mysqli_fetch_array($results, MYSQLI_ASSOC);
$pid = $row['user_id'];
mysqli_free_result($results);
return intval($pid);
}
function validate($lname = '')
{
global $dbc;
if (empty($lname)) {
return -1;
}
# Make the query
$query = "SELECT id, lname FROM presidents WHERE lname='" . $lname . "'";
show_query($query);
# Execute the query
$results = mysqli_query($dbc, $query);
check_results($results);
# If we get no rows, the login failed
if (mysqli_num_rows($results) == 0) {
return -1;
}
# We have at least one row, so get the frist one and return it
$row = mysqli_fetch_array($results, MYSQLI_ASSOC);
$pid = $row['id'];
return intval($pid);
}
echo '<li>' . '<a href="claimed.php?location=' . $row['id'] . '">' . $row['short_name'] . '</a>' . '</li>';
}
}
?>
</ul>
</div>
<div class="input-field col s3">
<a class='dropdown-button btn' href='#' data-activates='category_drop'><i class="material-icons right">keyboard_arrow_down</i>Category</a>
<ul id='category_drop' class='dropdown-content'>
<?php
#Query database for item categories
$query = 'SELECT * FROM categories ORDER BY name ASC';
#Execute query
$results = mysqli_query($dbc, $query);
#Output SQL errors, if any
check_results($results);
#Populate drop-down list, if we got results from the query
if ($results) {
while ($row = mysqli_fetch_array($results, MYSQLI_ASSOC)) {
echo '<li>' . '<a href="claimed.php?category=' . $row['id'] . '">' . $row['name'] . '</a>' . '</li>';
}
}
?>
</ul>
</div>
<div class="input-field col s2">
<a class="waves-effect waves-light btn" href='claimed.php'>Show All</a>
</div>
</div>
<?php
$random = mt_rand(0, 999999);
function perform_action($dbc, $id, $action)
{
if ($action == 'delete') {
$query = "DELETE FROM stuff WHERE id = {$id}";
} elseif ($action == 'found') {
$query = "UPDATE stuff SET status = 'found' WHERE id = {$id}";
} elseif ($action == 'lost') {
$query = "UPDATE stuff SET status = 'lost' WHERE id = {$id}";
} elseif ($action == 'claimed') {
$query = "UPDATE stuff SET status = 'claimed' WHERE id = {$id}";
} elseif ($action == 'update') {
Header("Location: /searchreport.php?status=update&id={$id}");
} else {
return false;
}
$result = mysqli_query($dbc, $query);
check_results($result);
return $result;
}
/**
* @desc Creates a new admin based on user input
* @param $dbc - the database connection object
**/
function make_new_admin($dbc)
{
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
# Validates the user input
if (isset($_POST['new_admin_submit']) && !empty($_POST['username']) && !empty($_POST['first_name']) && !empty($_POST['last_name']) && !empty($_POST['email']) && !empty($_POST['password']) && !empty($_POST['password-repeat'])) {
if (strcmp($_POST['password'], $_POST['password-repeat']) == 0) {
$username = $_POST['username'];
$firstName = $_POST['first_name'];
$lastName = $_POST['last_name'];
$email = $_POST['email'];
$password = $_POST['password'];
# Create query to insert new admin into database
$query = 'INSERT INTO users(username, first_name, last_name, email, pass, reg_date) VALUES("' . $username . '", "' . $firstName . '", "' . $lastName . '", "' . $email . '", "' . $password . '", Now())';
# Execute the query
$results = mysqli_query($dbc, $query);
check_results($results);
header("Location: manage_users.php");
exit("Redirecting to user panel");
} else {
echo '<p> Please make sure passwords match </p>';
}
} else {
echo '<p> Please make sure all fields are filled out </p>';
}
}
}
function show_form($dbc, $type)
{
$query = 'SELECT locations.id, locations.name
FROM locations
ORDER BY locations.name';
$results = mysqli_query($dbc, $query);
check_results($results);
if ($results) {
switch ($type) {
case 'found_search':
echo '<form action="search.php" method="POST">';
echo '<input type="hidden" name="type" value="lost" placeholder="Describe your item here" />';
echo '<p>Description: <input type="text" name="desc" placeholder="Describe your item here" /></p>';
echo '<p>Location:';
echo '<select name="location" id="where">';
while ($row = mysqli_fetch_array($results, MYSQLI_ASSOC)) {
echo '<option value="' . $row['id'] . '">' . $row['name'] . '</option>';
}
echo '</select></p>';
echo '<p><input value="Search" type="submit"></p>';
break;
case 'lost_search':
echo '<form action="search.php" method="POST">';
echo '<input type="hidden" name="type" value="found" placeholder="Describe your item here" />';
echo '<p>Description: <input type="text" name="desc" placeholder="Describe your item here" /></p>';
echo '<p>Location:';
echo '<select name="location" id="where">';
while ($row = mysqli_fetch_array($results, MYSQLI_ASSOC)) {
echo '<option value="' . $row['id'] . '">' . $row['name'] . '</option>';
}
echo '</select></p>';
echo '<p><input value="Search" type="submit"></p>';
break;
}
}
}
function delete_admin($dbc, $username, $target_admin)
{
$username = mysqli_real_escape_string($dbc, $username);
$target_admin = mysqli_real_escape_string($dbc, $target_admin);
# Only super admins may delete users
if (!is_super($dbc, $username)) {
return "Current user is not a super administrator";
}
$query = "DELETE FROM users WHERE username = '******'";
$result = mysqli_query($dbc, $query);
check_results($result);
if ($result !== true) {
return "Administrator deletion failed";
}
return "Administrator: '{$target_admin}' successfully deleted";
}
function insert_record($dbc, $location_id, $description, $room, $owner, $finder, $status)
{
$query = 'INSERT INTO stuff(location_id, description, create_date, update_date, room, owner, finder, status)
VALUES ("' . $location_id . '" , "' . $description . '" , NOW() , NOW() , "' . $room . '" , "NA" , "' . $finder . '" , "' . $status . '")';
show_query($query);
$results = mysqli_query($dbc, $query);
check_results($results);
return $results;
}
function insert_item($status, $date)
{
global $dbc;
#Assign variabled to insert into database from user input in $_POST
$loc = $_POST['location'];
$title = $_POST['title'];
$descr = $_POST['description'];
$category = $_POST['category'];
$create_date = $date;
$update_date = $date;
if ($status == 'Lost') {
$lost_date = $_POST['date'];
} else {
$lost_date = '';
}
if ($status == 'Found') {
$found_date = $_POST['date'];
} else {
$found_date = '';
}
$room = trim($_POST['room']);
if (!empty($_POST['owner_email'])) {
$owner_email = strtolower(trim($_POST['owner_email']));
} else {
$owner_email = '';
}
if (!empty($_POST['owner_phone'])) {
$owner_phone = trim($_POST['owner_phone']);
} else {
$owner_phone = '';
}
if (!empty($_POST['finder_email'])) {
$finder_email = strtolower(trim($_POST['finder_email']));
} else {
$finder_email = '';
}
if (!empty($_POST['finder_phone'])) {
$finder_phone = trim($_POST['finder_phone']);
} else {
$finder_phone = '';
}
//$photo = $_POST['filepath'];
if ($status == 'Lost') {
$owner = $_POST['full_name'];
} else {
$owner = '';
}
if ($status == 'Found') {
$finder = $_POST['full_name'];
} else {
$finder = '';
}
#TODO: add database insert functionality here
$query = "INSERT INTO stuff (location_id, title, description, category, create_date, update_date, lost_date, found_date, room, owner_email, owner_phone, finder_email, finder_phone, owner, finder, status) VALUES({$loc}, \"{$title}\", \"{$descr}\", {$category}, \"{$create_date}\", \"{$update_date}\", \"{$lost_date}\", \"{$found_date}\", \"{$room}\", \"{$owner_email}\", \"{$owner_phone}\", \"{$finder_email}\", \"{$finder_phone}\", \"{$owner}\", \"{$finder}\", \"{$status}\")";
#Show query if debugging is enabled (at the top of this file)
show_query($query);
#Get results of SQL query
$results = mysqli_query($dbc, $query);
#Output SQL errors, if any
check_results($results);
return $results;
}
</form>
<h2>Output</h2>
<?php
require_once '../includes/helpers.php';
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
$query = trim($_POST['query'], ';');
$result = mysqli_query($dbc, $query);
# DDL commands and most DML (UPDATE, DELETE, ALTER)
# return true if the query succeds
if ($result === true) {
echo 'Query executed successfully.';
# If the query failed, it will have returned false
} elseif ($result === false) {
echo 'Query failed with the follwing message<br>';
check_results($result);
# If it is neither of these, we have the iterator
# for the results of SELECT query
} else {
echo '<table id="queryTable">';
$row = mysqli_fetch_array($result, MYSQLI_ASSOC);
if ($row != false) {
echo '<tr>';
# Print the column names by using the associative
# array keys from the first row
foreach ($row as $key => $item) {
echo "<th>{$key}</th>";
}
echo '</tr>';
# Reset the iterator back to the first result
mysqli_data_seek($result, 0);
/**
* @desc Inserts a record into the stuff table
* @param $dbc - the database connection object
* @param $item - the name of the item
* @param $location - location the item was lost/found as selected from the dropdown
* @param $category - the type of item
* @param $color - the color of the item
* @param $descr - the description added by the listing poster
* @param $date - the date the item was lost/found
* @param $status - the file path to an image of the item
* @return bool|mysqli_result - the result of the query
*/
function insert_item($dbc, $item, $location, $category, $color, $descr, $date, $email, $status, $image)
{
$query = 'INSERT INTO stuff(item, location_id, category, color, description, item_date, create_date, update_date, uploaderEmail, status, image)
VALUES ("' . $item . '" , ' . $location . ' , "' . $category . '" , "' . $color . '" , "' . $descr . '" , STR_TO_DATE("' . $date . '","%Y-%m-%d"), Now(), Now(),"' . $email . '", "' . $status . '", "' . $image . '" )';
$results = mysqli_query($dbc, $query);
check_results($results);
// echo $query;
return $results;
}
function index_queries($dbc)
{
#Make the query I want to execute
$limit_stopper = 0;
$query = "SELECT *, stuff.id AS item_id FROM stuff JOIN locations ON (locations.id = stuff.location_id) ORDER BY stuff.update_date DESC";
#Executes the query I requested
$results = mysqli_query($dbc, $query);
check_results($results);
#Show the results of the execution
if ($results) {
#Generating the table information
echo '<H1>Recently updated in Limbo</H1>';
echo '<TABLE id="indexTable" style="margin-left:80px; border: solid;">';
echo '<TR>';
echo '<TH>Name</TH>';
echo '<TH>Status</TH>';
echo '<TH>Location</TH>';
echo '</TR>';
#Generate the table row
while ($limit_stopper < 5 && ($row = mysqli_fetch_array($results, MYSQLI_ASSOC))) {
echo '<TR>';
echo "<td> <a href='item.php?id={$row['item_id']}'>{$row['item']}</a> </td>";
echo '<TD>' . ucwords($row['status']) . '</TD>';
echo '<TD>' . $row['name'] . '</TD>';
echo '</TR>';
$limit_stopper++;
}
#Thus concludes the table
echo '</TABLE>';
# Free memory
mysqli_free_result($results);
}
}
function getName()
{
global $dbc;
session_start();
$user_id = isset($_SESSION['user_id']) ? $_SESSION['user_id'] : null;
$query = "SELECT first_name FROM users WHERE user_id = {$user_id};";
$results = pg_query($dbc, $query);
check_results($results);
while ($row = pg_fetch_array($results, NULL, PGSQL_ASSOC)) {
$fname = isset($row['first_name']) ? $row['first_name'] : null;
}
$_SESSION['first_name'] = $fname;
}
