function insert_transaction($date, $recipient, $category, $amount, $description)
{
$link = open_database_connection();
$date = strtotime($date);
$date = mysql_real_escape_string(date('Y-m-d', $date));
$recipientName = mysql_real_escape_string(trim($recipient));
$categoryName = $category;
$categoryName = mysql_real_escape_string(trim($category));
$description = mysql_real_escape_string(trim($description));
$amount = mysql_real_escape_string(trim($amount));
$checkRecipientQuery = "select id from Recipients where upper(name) like upper('{$recipientName}');";
$result = mysql_query($checkRecipientQuery, $link);
if (mysql_num_rows($result) == 0) {
$createRecipientQuery = "insert into Recipients (name) values ('{$recipientName}');";
mysql_query($createRecipientQuery, $link);
$result = mysql_query($checkRecipientQuery, $link);
}
$row = mysql_fetch_assoc($result);
$recipientID = $row['id'];
$checkCategoryQuery = "select id from Categories where upper(name) like upper('{$categoryName}');";
$result = mysql_query($checkCategoryQuery, $link);
if (mysql_num_rows($result) == 0) {
$createCategoryQuery = "insert into Categories (name) values ('{$categoryName}');";
mysql_query($createCategoryQuery, $link);
$result = mysql_query($checkCategoryQuery, $link);
}
$row = mysql_fetch_assoc($result);
$categoryID = $row['id'];
$createTransactionQuery = "insert into Transactions (date, amount, description, recipientID, categoryID) " . "values ('{$date}', '{$amount}', '{$description}', '{$recipientID}', '{$categoryID}');";
mysql_query($createTransactionQuery, $link);
close_database_connection($link);
}
function remove_post($id)
{
$link = open_database_connection();
$sql = "DELETE FROM Post WHERE id = {$id}";
$result = mysql_query($sql, $link);
close_database_connection($link);
return $post;
}
function delete_post($id)
{
$sql = "DELETE FROM post WHERE id='{$id}'";
$link = open_database_connection();
mysql_query($sql, $link);
close_database_connection($link);
return;
}
function delete_post($id)
{
$link = open_database_connection();
$sql = "DELETE FROM `post` WHERE id='{$id}' ";
mysql_query($sql, $link) or die("Запрос не выполненн" . mysql_error());
close_database_connection($link);
return;
}
function get_thing_by_key($thingKey)
{
$dbconn = open_database_connection();
$thingKey = intval($thingKey);
$result = $dbconn->query("SELECT * FROM things WHERE thingKey = " . $thingKey);
$row = $result->fetch_assoc();
close_database_connection($dbconn);
return $row;
}
function insertNewRecord($playerName, $Score)
{
$connection = open_database_connection();
$query = "INSERT INTO highscores(Name, Score) VALUES ('{$playerName}', {$Score})";
print '<br>';
print $query;
mysqli_query($connection, $query);
close_database_connection($connection);
}
function get_post($id)
{
$link = open_database_connection();
$sql = "SELECT * FROM post WHERE id='{$id}'";
$result = mysql_query($sql, $link);
$post = mysql_fetch_assoc($result);
close_database_connection($link);
return $post;
}
/**
* @param $id
*
* @return array|null
*/
function get_post_by_id($id)
{
$link = open_database_connection();
$id = intval($id);
$query = 'SELECT id, title FROM post WHERE id = ' . $id;
$result = mysqli_query($link, $query);
$row = mysqli_fetch_assoc($result);
close_database_connection($link);
return $row;
}
function get_post_by_id($id)
{
$link = open_database_connection();
$id = mysql_real_escape_string($id);
$query = 'SELECT date, title, body FROM post WHERE id = ' . $id;
$result = mysql_query($query);
$row = mysql_fetch_assoc($result);
close_database_connection($link);
return $row;
}
function edit_data($id)
{
$autor = $_POST['autor'];
$date = date("Y-m-d H:i:s");
$title = $_POST['title'];
$edit_content = $_POST['content'];
$link = open_database_connection();
$sql = "UPDATE `post` SET `autor` = '{$autor}', `date` = '{$date}', `title` = '{$title}', `content` = '{$edit_content}' WHERE `id` = '{$id}';";
$add = mysql_query($sql, $link);
close_database_connection($link);
}
function update_row()
{
if (!($request = getRequest())) {
return false;
}
$sql = "UPDATE `pages` SET `date`='" . $request['date'] . "', \n\t\t\t\t\t\t`title`='" . $request['title'] . "', \n\t\t\t\t\t\t`author`='" . $request['author'] . "', \n\t\t\t\t\t\t`content`='" . $request['content'] . "' \n\t\t\t\t\t\t WHERE id=" . $request['id'];
$link = open_database_connection();
$result = mysql_query($sql, $link) or die("error! " . $sql . " == " . mysql_error());
close_database_connection($link);
return $result;
}
function get_all_rows()
{
$link = open_database_connection();
$result = mysql_query('SELECT * FROM pages', $link);
$rows = array();
while ($row = mysql_fetch_assoc($result)) {
$rows[] = $row;
}
close_database_connection($link);
return $rows;
}
function get_all_posts()
{
$link = open_database_connection();
$result = mysql_query('SELECT id, title FROM post', $link);
$posts = array();
while ($row = mysql_fetch_assoc($result)) {
$posts[] = $row;
}
close_database_connection($link);
return $posts;
}
public function testDeleteCategory()
{
// Arrange
$changeCategory = new ChangeCategory();
$connection = open_database_connection();
$expectedResult = $changeCategory->addCategory($connection, "PHP Unit Test Category", "", "", "PHP Unit Test Summary");
// Act
$result = $changeCategory->deleteCategory($connection, "PHP Unit Test Category");
// Assert
$this->assertEquals($result, $expectedResult);
}
function edit_data($id)
{
$autor = $_POST['autor'];
$content = $_POST['content'];
$title = $_POST['title'];
$link = open_database_connection();
$sql = "UPDATE `post` SET `autor` = '{$autor}', `title` = '{$title}', `content` = '{$content}' WHERE `id` = '{$id}';";
$edit = mysql_query($sql, $link);
close_database_connection($link);
header('location:admin');
}
function getAllFromById($tableName, $id)
{
$link = open_database_connection();
$result = mysql_query('SELECT * FROM $tableName WHERE id = $id', $link);
$posts = array();
while ($row = mysql_fetch_assoc($result)) {
$posts[] = $row;
}
close_database_connection($link);
return $posts;
}
function add_post()
{
$author = $_POST['add_author'];
$time = $_POST['add_time'];
$title = $_POST['add_title'];
$content = $_POST['add_content'];
$link = open_database_connection();
$sql = "INSERT INTO `post` (`id`, `author`, `time`, `title`, `content`) \n\t\t\tVALUES (NULL, '{$author}', '{$time}', '{$title}', '{$content}');";
$result = mysql_query($sql, $link);
close_database_connection($link);
}
function get_row($id)
{
$link = open_database_connection();
$sql = "SELECT * FROM `idpages` WHERE `id`={$id}";
$result = mysql_query($sql, $link);
$rows = array();
while ($row = mysql_fetch_array($result)) {
$rows[] = $row;
}
close_database_connection($link);
return $rows;
}
function get_all_posts()
{
$link = open_database_connection();
$sql = 'SELECT * FROM posts';
$result = mysqli_query($link, $sql);
$posts = array();
while ($row = mysqli_fetch_array($result, MYSQL_ASSOC)) {
$posts[] = $row;
}
close_db_connection($link);
return $posts;
}
/**
* @return array
*/
public function getImageUrlArray()
{
// make a new connection to the database
$connection = open_database_connection();
// make a new query for the CategoryID and ProductImageURL from all rows from `products`
$query = "SELECT CategoryID, ProductImageURL FROM `products` ORDER BY CategoryID ASC";
// get the results from the query
$results = mysqli_query($connection, $query);
// declare imageData to be an empty array
// this variable will hold all the data in php array format before we json_encode() it
$imageData = array();
// declaring and defining a variable to represent which category(the CategoryID)
// we're processing in the loop below
$categoryToProcess = 0;
// declaring and defining a variable to represent which index in its respective
// category array in imageData we're processing
$categoryIndexToProcess = 0;
// check to make sure there are results from the mysql query we made earlier
if (mysqli_num_rows($results) > 0) {
// as long as we can retrieve another row from the database, add the row's ProductImageURL to imageData
while ($rows = $results->fetch_assoc()) {
// set the category we're processing to be the CategoryID of the row we retrieved
$categoryToProcess = $rows['CategoryID'];
if (array_key_exists($categoryToProcess, $imageData)) {
// If an array key exists in the object for the category we're processing,
// then there is an array dedicated to storing data for that category, so
// insert the image url in the category's array, in the next index
$imageData[$categoryToProcess][$categoryIndexToProcess] = "/images/" . $rows['ProductImageURL'];
} else {
// If there isn't an array key for the category we're processing,
// then there isn't an array for this category, so create a new array,
$imageData[$categoryToProcess] = array();
// reset the index of the item we're processing to 1,
$categoryIndexToProcess = 1;
// and insert the image's url into that index
$imageData[$categoryToProcess][$categoryIndexToProcess] = "/images/" . $rows['ProductImageURL'];
}
// update the amount of items in the array of the category we're processing
// to be the last index we processed
$imageData[$categoryToProcess][0] = $categoryIndexToProcess;
// update the amount of categories in the object to be the category we're processing
// for the same reason as above
$imageData[0] = $rows['CategoryID'];
// process the next index
$categoryIndexToProcess++;
}
}
// close the database connection
close_database_connection($connection);
// return the imageData
return $imageData;
}
function add_row()
{
if (empty($_REQUEST['add_title']) && !empty($_REQUEST['add_content']) && !empty($_REQUEST['add_autor'])) {
return;
//'koik otsast peale'
}
$title = $_REQUEST['add_title'];
$autor = $_REQUEST['add_title'];
$content = $_REQUEST['add_title'];
$date = new date();
$link = $_REQUEST['add_title'];
$link = open_database_connection();
$sql = "INSERT INTO pages ('date',title,content,autor) \n\t\tVALUES('{$date}','{$title}','{$content}','{$autor}')";
close_database_connection($link);
}
function get_user_by_name($name)
{
global $db_conn;
global $user_select;
global $FETCH_SQL_SERVER;
global $FETCH_SQL_PORT;
global $FETCH_SQL_DATABASE;
if (!isset($user_select)) {
$user_select = open_database_connection()->prepare('SELECT `UserId`,`Username`,`ScratchUser`,`ImageHash`,`PasswordHash`,`EMailAddress`,`VerifiedEMail`,`VerifiedScratch` FROM `users` WHERE Username=?');
}
$user_select->execute(array($name));
$data = $user_select->fetch();
$user_select->closeCursor();
// Ensure we can query again
return $data;
}
/**
* return an array containing all menu categories after connecting to the database
*
* @return array
*/
public function getAllMenuCategories()
{
$connection = open_database_connection();
$query = "SELECT * FROM `categories`";
$results = mysqli_query($connection, $query);
$this->categories = array();
if (mysqli_num_rows($results) > 0) {
while ($rows = $results->fetch_assoc()) {
$this->categories[$rows['CategoryID']] = new MenuCategory($rows['CategoryID']);
$this->categories[$rows['CategoryID']]->setMenuCategoryTitle($rows['CategoryName']);
$this->categories[$rows['CategoryID']]->setMenuCategoryImage($rows['CategoryImage']);
$this->categories[$rows['CategoryID']]->setMenuCategorySummary($rows['CategorySummary']);
$this->addMenuCategory($this->categories[$rows['CategoryID']]);
}
}
close_database_connection($connection);
return $this->categories;
}
function toXML()
{
$connection = open_database_connection();
$query = "SELECT * FROM highscores order by Score desc";
$xml_output = "<?xml><playerScoreList>";
if ($result = mysqli_query($connection, $query)) {
while ($row = mysqli_fetch_assoc($result)) {
$player = $row['Name'];
$Score = $row['Score'];
$xml_output .= "<Name>";
$xml_output .= "<playerName>{$player}</playerName>";
$xml_output .= "<Score>{$Score}</Score>";
$xml_output .= "</Name>";
}
}
$xml_output .= "</playerScoreList>";
print $xml_output;
}
function get_position_id_query($username)
{
$link = open_database_connection();
$position_id_query = sprintf("SELECT positionID FROM staff WHERE username = '******' AND start_date < NOW() AND end_date > NOW()", $username);
$position_id_result = mysql_query($position_id_query) or die(mysql_error($link));
close_database_connection($link);
return $position_id_result;
}
<html>
<head>
<title>New Transaction</title>
<script type="text/javascript" src="jquery-2.1.4.min.js"></script>
<script type="text/javascript" src="jquery-ui.min.js"></script>
</head>
<?php
require_once 'connection.php';
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
$link = open_database_connection();
$date = strtotime($_POST['date']);
$date = date('Y-m-d', $date);
$amount = $_POST['amount'];
$recipientName = $_POST['recipient'];
$recipientName = trim($recipientName);
$categoryName = $_POST['category'];
$categoryName = trim($categoryName);
$description = $_POST['description'];
$description = trim($description);
$checkRecipientQuery = "select id from Recipients where upper(name) like upper('{$recipientName}');";
$result = mysql_query($checkRecipientQuery, $link);
if (mysql_num_rows($result) == 0) {
$createRecipientQuery = "insert into Recipients (name) values ('{$recipientName}');";
mysql_query($createRecipientQuery, $link);
$result = mysql_query($checkRecipientQuery, $link);
}
$row = mysql_fetch_assoc($result);
$recipientID = $row['id'];
$checkCategoryQuery = "select id from Categories where upper(name) like upper('{$categoryName}');";
$result = mysql_query($checkCategoryQuery, $link);
if (mysql_num_rows($result) == 0) {
/**
* test for sorting products from the search
* NOTE: By changing the the products "Cappuccino", "Lemon Tea", or
* "Mineral Water" in the database or adding any products with
* similar names this test will no longer work unless $expectedResult
* is changed based on AdminSearch::sortProductBySearch's new output
*/
public function testSortProductBySearch()
{
// Arrange
$adminSearch = new AdminSearch();
$connection = open_database_connection();
$expectedResult = '<h3>Search results for Cappuccino</h3><form id="productResult13" action= "/adminEditProduct" method="get">
<p><span class="search" onclick="document.getElementById(\'productResult13\').submit();">Cappuccino</span> - This creamy, delicious, rich flavoured coffee specialty is made from 1/3 Espresso, 1/3 steamed milk and 1/3 foamed milk and garnished with your choice of Cinnamon or powder chocolate. We use high altitude(above 3000 ft) grown Arabica coffee beans.</p><p>Calories: 115</p><p>Price: 3.45</p><input type="hidden" name="product_ID" value="13" /><input type="hidden" name="product_name" value="Cappuccino" /><input type="hidden" name="product_image_URL" value="cappuccino.png" /><input type="hidden" name="product_description" value="This creamy, delicious, rich flavoured coffee specialty is made from 1/3 Espresso, 1/3 steamed milk and 1/3 foamed milk and garnished with your choice of Cinnamon or powder chocolate. We use high altitude(above 3000 ft) grown Arabica coffee beans." /><input type="hidden" name="product_calories" value="115" /><input type="hidden" name="product_allergy_info" value="no allergens are present on this drink product but consideration for those with low toleration to milk products should be given." /><input type="hidden" name="product_price" value="3.45" /><input type="hidden" name="category_ID" value="3" /><input type="hidden" name="category_name" value="Drinks" /><input type="hidden" name="category_summary" value="Quench your thirst with our selection of freshly made drinks or warm yourself up with a selection of hot drinks that we offer!" /><input type="hidden" name="productImageURL1" value="" /><input type="hidden" name="productImageURL2" value="cappuccino.png" /></form><h3>Search results for Water</h3><form id="productResult10" action= "/adminEditProduct" method="get">
<p><span class="search" onclick="document.getElementById(\'productResult10\').submit();">Lemon Tea</span> - KBK lemon tea is inspired from the Indian cuisine and it is a very refreshing drink which could be served at any time of the day indulging yourself or when meeting with your next door neighbor. It is made of aromatic Indian black tea, lemon, and spring water. Best served with a slice of lemon and some ice!</p><p>Calories: 40</p><p>Price: 2.95</p><input type="hidden" name="product_ID" value="10" /><input type="hidden" name="product_name" value="Lemon Tea" /><input type="hidden" name="product_image_URL" value="lemon_tea.png" /><input type="hidden" name="product_description" value="KBK lemon tea is inspired from the Indian cuisine and it is a very refreshing drink which could be served at any time of the day indulging yourself or when meeting with your next door neighbor. It is made of aromatic Indian black tea, lemon, and spring water. Best served with a slice of lemon and some ice!" /><input type="hidden" name="product_calories" value="40" /><input type="hidden" name="product_allergy_info" value="no allergens are present on this product." /><input type="hidden" name="product_price" value="2.95" /><input type="hidden" name="category_ID" value="3" /><input type="hidden" name="category_name" value="Drinks" /><input type="hidden" name="category_summary" value="Quench your thirst with our selection of freshly made drinks or warm yourself up with a selection of hot drinks that we offer!" /><input type="hidden" name="productImageURL1" value="" /><input type="hidden" name="productImageURL2" value="lemon_tea.png" /></form><form id="productResult14" action= "/adminEditProduct" method="get">
<p><span class="search" onclick="document.getElementById(\'productResult14\').submit();">Mineral Water</span> - This mineral water is bottled fresh at its source in the French Alps. You are what you drink, so hydration is very important, so do not leave out this vital supplement in any meal.</p><p>Calories: 10</p><p>Price: 1.49</p><input type="hidden" name="product_ID" value="14" /><input type="hidden" name="product_name" value="Mineral Water" /><input type="hidden" name="product_image_URL" value="mineral_water.png" /><input type="hidden" name="product_description" value="This mineral water is bottled fresh at its source in the French Alps. You are what you drink, so hydration is very important, so do not leave out this vital supplement in any meal." /><input type="hidden" name="product_calories" value="10" /><input type="hidden" name="product_allergy_info" value="None" /><input type="hidden" name="product_price" value="1.49" /><input type="hidden" name="category_ID" value="3" /><input type="hidden" name="category_name" value="Drinks" /><input type="hidden" name="category_summary" value="Quench your thirst with our selection of freshly made drinks or warm yourself up with a selection of hot drinks that we offer!" /><input type="hidden" name="productImageURL1" value="" /><input type="hidden" name="productImageURL2" value="mineral_water.png" /></form>';
// Assert
$result = $adminSearch->sortProductBySearch($connection, "ASC", "ProductName", "Cappuccino Water");
close_database_connection($connection);
// Act
$this->assertEquals($result, $expectedResult);
}
/**
* Action for displaying all the information about a product to the user
*
* Action for route: /product
*
* @param Request $request
* @param Application $app
* @return mixed
*/
public function productAction(Request $request, Application $app)
{
//check if username is stored in session
$username = getAuthenticatedUsername($app);
$params = $request->query->all();
$productName = $_GET['product_name'];
$error = "";
$connection = open_database_connection();
if (!$connection) {
$_SESSION['errorCategory'] = 'Database';
$_SESSION['errorMessage'] = 'DB connection failed: ' . mysqli_connect_error();
header('Location: /error');
}
$query = "SELECT * FROM `products` WHERE ProductName = '" . $productName . "'";
$resultSet = mysqli_query($connection, $query);
if (mysqli_num_rows($resultSet) > 0) {
$rows = mysqli_fetch_assoc($resultSet);
$productName = $rows['ProductName'];
$productImageURL = $rows['ProductImageURL'];
$productDescription = $rows['ProductDescription'];
$productCalories = $rows['ProductCalories'];
$productAllergyInfo = $rows['ProductAllergyInfo'];
$productPrice = sprintf("%01.2f", $rows['ProductPrice']);
} else {
$error = "An internal server error occurred. Please try again later.";
}
close_database_connection($connection);
// build args array
// -------------
$argsArray = array('username' => $username, 'title' => $productName, 'productName' => $productName, 'productImageURL' => $productImageURL, 'productDescription' => $productDescription, 'productCalories' => $productCalories, 'productAllergyInfo' => $productAllergyInfo, 'productPrice' => $productPrice, 'errorMessage' => $error);
// render template
// --------------
$templateName = 'product';
return $app['twig']->render($templateName . '.html.twig', $argsArray);
}
function update_post($id)
{
if (empty($_REQUEST['update_autor']) || empty($_REQUEST['update_date']) || empty($_REQUEST['update_title']) || empty($_REQUEST['update_content'])) {
echo "Пропущена запись!";
return false;
}
$id = $_REQUEST['update_id'];
$update_autor = $_REQUEST['update_autor'];
$update_date = date("Y-m-d H:i:s");
$update_title = $_REQUEST['update_title'];
$update_content = $_REQUEST['update_content'];
$sql = "UPDATE post SET `date`='{$update_date}', autor='{$update_autor}',title='{$update_title}',\n\tcontent='{$update_content}' WHERE id='{$id}'";
echo "sql=" . $sql;
/*$sql="UPDATE post SET(`date`,`autor`,`title`,`content`)
VALUES('$add_date','$add_autor','$add_title','$add_content') WHERE id='$id'";*/
$link = open_database_connection();
mysql_query($sql, $link) or die("Запрос не выполнен" . mysql_error());
close_database_connection($link);
echo "Обновлено!";
return true;
}
function update_userPreferences($uid, $buttonId, $snackId)
{
$link = open_database_connection();
$query = "INSERT INTO usersPreferences VALUES ('" . mysqli_real_escape_string($link, $uid) . "', " . mysqli_real_escape_string($link, $buttonId) . ", " . mysqli_real_escape_string($link, $snackId) . ") ON DUPLICATE KEY UPDATE snackId=" . mysqli_real_escape_string($link, $snackId);
$result = mysqli_query($link, $query);
mysqli_free_result($result);
mysqli_close($link);
}
