public function EliminarMarca($marca)
 {
     $mysqli = $this->mysqli;
     $stmt = \mysqli_prepare($mysqli, "CALL ELIMINAR_MARCA(?)");
     \mysqli_stmt_bind_param($stmt, 'i', $marca);
     \mysqli_stmt_execute($stmt);
 }

 public function insertItems(Items $items)
 {
     $con = self::openConnection();
     $affected = 0;
     mysqli_begin_transaction($con);
     $stm = mysqli_stmt_init($con);
     $sql = "INSERT INTO product VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
     mysqli_stmt_prepare($stm, $sql);
     foreach ($items->getItems() as $item) {
         $code = $item->getCode();
         $articul = $item->getArticul();
         $name = $item->getName();
         $bmuID = $item->getBasicMeasurementUnit() == null ? null : $item->getBasicMeasurementUnit()->getId();
         $price = $item->getPrice();
         $curID = $item->getCurrency() == null ? null : $item->getCurrency()->getId();
         $muID = $item->getMeasurementUnit() == null ? null : $item->getMeasurementUnit()->getId();
         $parent = $item->getParent() == null ? null : $item->getParent()->getCode();
         mysqli_stmt_bind_param($stm, 'sssdddds', $code, $articul, $name, $bmuID, $price, $curID, $muID, $parent);
         mysqli_stmt_execute($stm);
         if (mysqli_affected_rows($con) == 1) {
             $affected++;
         }
     }
     if ($affected > 0) {
         mysqli_commit($con);
     } else {
         mysqli_rollback($con);
     }
     return $affected;
 }

function login()
{
    include_once 'database_conn.php';
    // check is form filled
    if (isFormFilled()) {
        // if not filled, stop
        return;
    }
    $uid = sanitizeData($_POST['username']);
    $pswd = sanitizeData($_POST['password']);
    $columnLengthSql = "\n\t\t\tSELECT COLUMN_NAME, CHARACTER_MAXIMUM_LENGTH\n\t\t\tFROM INFORMATION_SCHEMA.COLUMNS\n\t\t\tWHERE TABLE_NAME =  'te_users'\n\t\t\tAND (column_name =  'username'\n\t\t\tOR column_name =  'passwd')";
    $COLUMN_LENGTH = getColumnLength($conn, $columnLengthSql);
    $isError = false;
    $errMsg[] = validateStringLength($uid, $COLUMN_LENGTH['username']);
    //uid
    $errMsg[] = validateStringLength($pswd, $COLUMN_LENGTH['passwd']);
    //pswd
    for ($i = 0; $i < count($errMsg); $i++) {
        if (!($errMsg[$i] === true)) {
            echo "{$errMsg[$i]}";
            $isError = true;
        }
    }
    //if contain error, halt continue executing the code
    if ($isError) {
        return;
    }
    // check is uid exist
    $checkUIDSql = "SELECT passwd, salt FROM te_users WHERE username = ?";
    $stmt = mysqli_prepare($conn, $checkUIDSql);
    mysqli_stmt_bind_param($stmt, "s", $uid);
    mysqli_stmt_execute($stmt);
    mysqli_stmt_store_result($stmt);
    if (mysqli_stmt_num_rows($stmt) <= 0) {
        echo "Sorry we don't seem to have that username.";
        return;
    }
    mysqli_stmt_bind_result($stmt, $getHashpswd, $getSalt);
    while (mysqli_stmt_fetch($stmt)) {
        $hashPswd = $getHashpswd;
        $salt = $getSalt;
    }
    // if exist, then get salt and db hashed password
    // create hash based on password
    // hash pswd using sha256 algorithm
    // concat salt in db by uid
    // hash using sha256 algorithm
    $pswd = hash("sha256", $salt . hash("sha256", $pswd));
    // check does it match with hased password from db
    if (strcmp($pswd, $hashPswd) === 0) {
        echo "Success login<br/>";
        // add session
        $_SESSION['logged-in'] = $uid;
        // go to url
        $url = $_SERVER['REQUEST_URI'];
        header("Location: {$url}");
    } else {
        echo "Fail login<br/>";
    }
}

function update_last_try($dbh, $config, $key)
{
    $stmt = mysqli_prepare($dbh, "UPDATE " . $config['table_prefix'] . "keys SET last_try = NOW() WHERE `key` = ?");
    mysqli_stmt_bind_param($stmt, "s", $key);
    mysqli_stmt_execute($stmt);
    mysqli_stmt_close($stmt);
}

 public function insertItems(Items $items)
 {
     $con = self::openConnection();
     $affected = 0;
     mysqli_begin_transaction($con);
     $stm = mysqli_stmt_init($con);
     $sql = "INSERT INTO category VALUES (?, ?, ?)";
     mysqli_stmt_prepare($stm, $sql);
     foreach ($items->getItems() as $item) {
         $code = $item->getCode();
         $name = $item->getName();
         $parent = $item->getParent() == null ? null : $item->getParent()->getCode();
         mysqli_stmt_bind_param($stm, 'sss', $code, $name, $parent);
         mysqli_stmt_execute($stm);
         if (mysqli_affected_rows($con) == 1) {
             $affected++;
         }
     }
     if ($affected > 0) {
         mysqli_commit($con);
     } else {
         mysqli_rollback($con);
     }
     return $affected;
 }

function insertAgent($agtdata)
{
    //SQL connection variables
    $servername = "localhost";
    $username = "root";
    $password = "";
    $dbname = "travelexperts";
    //myslqi connection and prepared statement
    $dbh = @mysqli_connect($servername, $username, $password) or die("Connect Error: " . mysqli_connect_error());
    mysqli_select_db($dbh, $dbname);
    $colnames = array_keys($agtdata);
    $colnamestring = implode(", ", $colnames);
    $sql = "insert into agents ({$colnamestring}) values (?, ?, ?, ?, ?, ?, ?, ?, ?)";
    //number of ? needs to match the number of fields
    $stmt = mysqli_prepare($dbh, $sql);
    $values = array_values($agtdata);
    mysqli_stmt_bind_param($stmt, "ssssssiss", $values[0], $values[1], $values[2], $values[3], $values[4], $values[5], $values[6], $values[7], $values[8]);
    // the number of s or i (string or int or other) needs to match the number and type of fields
    $result = mysqli_stmt_execute($stmt);
    //print(mysqli_error($dbh));
    //print("result=$result");
    //print($sql);
    mysqli_close($dbh);
    //Return messages if successful or unsuccessful
    if ($result) {
        return "A new agent account was created successfully<br />";
    } else {
        return "Failed to create new agent account<br />";
    }
}

function changeItem($elemID, $uniqueID, $changeTo)
{
    // Connect to the MySQL database
    $host = "fall-2015.cs.utexas.edu";
    $user = "pjobrien";
    $file = fopen("/u/pjobrien/password.txt", "r");
    $line = fgets($file);
    $pwd = trim($line);
    fclose($file);
    $dbs = "cs329e_pjobrien";
    $port = "3306";
    $connect = mysqli_connect($host, $user, $pwd, $dbs, $port);
    if (empty($connect)) {
        die("mysql_connect failed " . mysqli_connect_error());
    }
    $elemID = trim($elemID);
    $uniqueID = trim($uniqueID);
    $changeTo = trim($changeTo);
    // get the item we want to change from the front end
    $stmt = mysqli_prepare($connect, "UPDATE userInfo SET {$elemID} = ? WHERE username= ?");
    mysqli_stmt_bind_param($stmt, 'ss', $changeTo, $uniqueID) or die("Failed: " . mysqli_error($connect));
    mysqli_stmt_execute($stmt) or die("Failed: " . mysqli_error($connect));
    mysqli_stmt_close($stmt);
    // Close connection to the database
    mysqli_close($connect);
    return true;
}

function email()
{
    global $link, $stmt;
    if (defined("CRYPT_BLOWFISH") && CRYPT_BLOWFISH) {
        $salt = '$2y$11$' . substr(md5(uniqid(rand(), true)), 0, 22);
        $password = crypt($_POST['password'], $salt);
    }
    mysqli_stmt_bind_param($stmt, 'ss', $_POST['email'], $password);
    mysqli_stmt_execute($stmt);
    mysqli_stmt_close($stmt);
    //to verify email
    $hash = hash('md5', $_POST["email"]);
    $stmt = mysqli_prepare($link, "INSERT INTO `verify_email`(`email`, `hash`) VALUES(?,'" . $hash . "')") or die(mysqli_error($link));
    mysqli_stmt_bind_param($stmt, 's', $_POST['email']);
    mysqli_stmt_execute($stmt);
    mysqli_stmt_close($stmt);
    mysqli_close($link);
    $to = $_POST['email'];
    $subject = 'Email varification';
    $message = 'Please click this link to activate your account: http://woofwarrior.com//gallery/verify.php?email=' . $_POST['email'] . '&hash=' . $hash . ' ';
    $headers = "From: [email protected]\r\n";
    mail($to, $subject, $message, $headers);
    //echo json_encode('signed up, verify email. Please click this link to activate your account: http://woofwarrior.com//gallery/htdocs/verify.php?email='.$_POST['email'].'&hash='.$hash);
    echo json_encode('verify email');
}

function checkCredentials($username, $password)
{
    $link = retrieve_mysqli();
    //Test to see if their credentials are valid
    $queryString = 'SELECT salt, hashed_password FROM user WHERE username = ?';
    if ($stmt = mysqli_prepare($link, $queryString)) {
        //Get the stored salt and hash as $dbSalt and $dbHash
        mysqli_stmt_bind_param($stmt, "s", $username);
        mysqli_stmt_execute($stmt);
        mysqli_stmt_bind_result($stmt, $dbSalt, $dbHash);
        mysqli_stmt_fetch($stmt);
        mysqli_stmt_close($stmt);
        // close prepared statement
        mysqli_close($link);
        /* close connection */
        //Generate the local hash to compare against $dbHash
        $localhash = generateHash($dbSalt . $password);
        //Compare the local hash and the database hash to see if they're equal
        if ($localhash == $dbHash) {
            return true;
        }
        // password hashes matched, this is a valid user
    }
    return false;
    // password hashes did not match or username didn't exist
}

function update_vote($image_id)
{
    //get number of votes and update
    global $link;
    /*$result = mysqli_query($link, "SELECT `amount` FROM `votes_amount` WHERE `imageID`=".$image_id.";") or die(mysqli_error($link));
    	$amount = mysqli_fetch_assoc($result);
    	$new_amount = $amount['amount']+1;
    	mysqli_query($link, "UPDATE `votes_amount` SET `amount`=".$new_amount." WHERE `imageID`=".$image_id.";") or die(mysqli_error($link));*/
    $stmt = mysqli_stmt_init($link);
    mysqli_stmt_prepare($stmt, "SELECT `amount` FROM `votes_amount` WHERE `imageID`=?;") or die(mysqli_error($link));
    mysqli_stmt_bind_param($stmt, 'i', $image_id);
    mysqli_stmt_execute($stmt);
    $result = mysqli_stmt_get_result($stmt);
    mysqli_stmt_close($stmt);
    $amount = mysqli_fetch_assoc($result);
    $new_amount = $amount['amount'] + 1;
    $stmt = mysqli_prepare($link, "UPDATE `votes_amount` SET `amount`=" . $new_amount . " WHERE `imageID`=?;") or die(mysqli_error($link));
    mysqli_stmt_bind_param($stmt, 'i', $image_id);
    mysqli_stmt_execute($stmt);
    mysqli_stmt_close($stmt);
    //return ajax data
    if (isset($_SESSION['id']) && !isset($_POST['action']) && !isset($_POST['votePic'])) {
        $data = array('new_amount' => $new_amount, 'imageID' => $image_id);
    } elseif (isset($_POST['action']) && $_POST['action'] == 'anonymous_voting') {
        //get another two images
        $result = mysqli_query($link, "SELECT * FROM `image` ORDER BY RAND() LIMIT 2;") or die(mysqli_error($link));
        $data = array();
        while ($row = mysqli_fetch_assoc($result)) {
            $data[] = $row;
        }
    }
    mysqli_close($link);
    return $data;
}

 public function insertBook($bookName, $bookAuthor)
 {
     $stmt = mysqli_prepare($this->connection, 'INSERT INTO books(book_title) VALUES (?)');
     mysqli_stmt_bind_param($stmt, 's', $bookName);
     mysqli_stmt_execute($stmt);
     $authorId = [];
     $author = new Author();
     $allAuthors = $author->selectAllAuthors();
     foreach ($bookAuthor as $au) {
         foreach ($allAuthors as $key => $value) {
             if ($au == $value) {
                 $authorId[] = $key;
             }
         }
     }
     $books = $this->getBook();
     $keyID = 0;
     foreach ($books as $key => $title) {
         if ($title == $bookName) {
             $keyID = $key;
         }
     }
     $stmt2 = mysqli_prepare($this->connection, 'INSERT INTO books_authors(book_id,author_id) VALUES (?,?)');
     for ($i = 0; $i < count($authorId); $i++) {
         mysqli_stmt_bind_param($stmt2, 'ii', $keyID, $authorId[$i]);
         mysqli_stmt_execute($stmt2);
     }
 }

 public function Get_Safe_Item($table, $field, $var_type, $field_like, $like = FALSE)
 {
     // Подготавливаем sql-строку и предварительный запрос
     $sign = $like ? "LIKE" : "=";
     $sql = "SELECT `{$field}` FROM `{$table}` WHERE `{$field}` {$sign} ?";
     $statement = mysqli_prepare($this->db_connector, $sql);
     // Связываем параметр с меткой и выполняем запрос
     switch ($var_type) {
         case "string":
             $var = "s";
             break;
         case "integer":
             $var = "i";
             break;
         case "double":
             $var = "d";
             break;
         default:
             $var = "b";
             break;
     }
     $field_value = $like ? $field_like . "%" : $field_like;
     mysqli_stmt_bind_param($statement, $var, $field_value);
     mysqli_stmt_execute($statement);
     // Связываем переменную со значением результата запроса и получаем значение результата
     mysqli_stmt_bind_result($statement, $safe_value);
     if (mysqli_stmt_fetch($statement)) {
         return $safe_value;
     } else {
         return NULL;
     }
 }

 public function save()
 {
     $Autor = $this->getAlgo('autor');
     $blog_id = $this->getAlgo('blog_id');
     $Texto = $this->getAlgo('texto');
     $Fecha = $this->getAlgo('fecha');
     $conn = conexion();
     if ($conn->connect_error) {
         echo "<h2>";
         die("Connection failed: " . $conn->connect_error);
         echo "</h2>";
     } else {
         $sentencia = $conn->stmt_init();
         if (!$sentencia->prepare("INSERT INTO comentarios (autor, blog_id, texto, fecha) VALUES (?, ?, ?, ?)")) {
             echo "Falló la preparación: (" . $conn->errno . ") " . $conn->error;
         } else {
             mysqli_stmt_bind_param($sentencia, "siss", $Autor, $blog_id, $Texto, $Fecha);
             if (!$sentencia->execute()) {
                 $conn->close();
             } else {
                 $conn->close();
             }
         }
     }
 }

 public function EliminarTienda($tienda)
 {
     $mysqli = $this->mysqli;
     $stmt = \mysqli_prepare($mysqli, "CALL ELIMINAR_TIENDA(?);");
     \mysqli_stmt_bind_param($stmt, 'i', $tienda);
     \mysqli_stmt_execute($stmt);
 }

function getPageInfo($con, $city_page_id)
{
    $result_array = array();
    $query_case_list = "SELECT r.region_name_latin, cp.city_page_key, c.city_name_latin FROM `city` c, `city_page` cp, `region` r WHERE 1 AND cp.city_page_id = ? AND c.city_id = cp.city_id AND c.region_id = r.region_id";
    if (!($stmt = mysqli_prepare($con, $query_case_list))) {
        #echo "Prepare failed: (" . mysqli_connect_errno() . ") " . mysqli_connect_error()."<br>";
    }
    //set values
    #echo "set value...";
    $id = 1;
    if (!mysqli_stmt_bind_param($stmt, "s", $city_page_id)) {
        #echo "Binding parameters failed: (" . mysqli_connect_errno() . ") " . mysqli_connect_error()."<br>";
    }
    #echo "execute...";
    if (!mysqli_stmt_execute($stmt)) {
        #echo "Execution failed: (" . mysqli_connect_errno() . ") " . mysqli_connect_error()."<br>";
    }
    /* instead of bind_result: */
    #echo "get result...";
    if (!mysqli_stmt_bind_result($stmt, $region_name_latin, $city_page_key, $city_name_latin)) {
        #echo "Getting results failed: (" . mysqli_connect_errno() . ") " . mysqli_connect_error()."<br>";
    }
    if (mysqli_stmt_fetch($stmt)) {
        $result_array = array("region_name_latin" => $region_name_latin, "city_page_key" => $city_page_key, "city_name_latin" => $city_name_latin);
    } else {
        #echo "Fetching results failed: (" . mysqli_connect_errno() . ") " . mysqli_connect_error()."<br>";
        print_r(error_get_last());
    }
    mysqli_stmt_close($stmt);
    return $result_array;
}

 public function EliminarRedSocial($id)
 {
     $mysqli = $this->mysqli;
     $stmt = \mysqli_prepare($mysqli, "CALL ELIMINAR_RS(?)");
     \mysqli_stmt_bind_param($stmt, 'i', $id);
     \mysqli_stmt_execute($stmt);
     \mysqli_stmt_close($stmt);
 }

 public function EliminarImagen($imagen)
 {
     $mysqli = $this->mysqli;
     $stmt = \mysqli_prepare($mysqli, "CALL ELIMINAR_IMG(?);");
     \mysqli_stmt_bind_param($stmt, 'i', $imagen);
     \mysqli_stmt_execute($stmt);
     \mysqli_stmt_close($stmt);
 }

 public function Get_Safe_Rows($table, $field, $var_type, $field_like, $like = FALSE, $sql_end = "")
 {
     // Подготавливаем безопасный запрос в базу данных MyISAM и старых версий MySQL
     /*
     		$field_value = mysqli_real_escape_string($this->db_connector, $field_like);
     		
     		if ($field_value != $field_like) { return FALSE; }
     		$sign = ($like) ? "LIKE" : "=";
     		$field_value = ($like) ? $field_value."%" : $field_value;
     		$sql = "SELECT `id` FROM `$table` WHERE `$field` $sign '$field_value'";
     		if ($sql_end != "") {
     			$sql .= " AND ".$sql_end;
     		}
     		$temp_arr = $this->GetMultiItemsBySql($sql, array("id"));
     		$temp_num = count($temp_arr);
     		for ($i=0; $i<$temp_num; $i++) {
     			$arr_of_ids[$i] = $temp_arr[$i]["id"];
     		}
     		
     		return $arr_of_ids;
     */
     // Подготавливаем sql-строку и предварительный запрос в базу данных InnoDB и современных версий MySQL
     $sign = $like ? "LIKE" : "=";
     $sql = "SELECT `id` FROM `{$table}` WHERE `{$field}` {$sign} ?";
     if ($sql_end != "") {
         $sql .= " AND " . $sql_end;
     }
     $statement = mysqli_prepare($this->db_connector, $sql);
     // Связываем параметр с меткой и выполняем запрос
     switch ($var_type) {
         case $var_type == "string" || $var_type == "str" || $var_type == "s":
             $var = "s";
             break;
         case $var_type == "integer" || $var_type == "int" || $var_type == "i":
             $var = "i";
             break;
         case $var_type == "double" || $var_type == "float" || $var_type == "d" || $var_type == "f":
             $var = "d";
             break;
         default:
             $var = "b";
             break;
     }
     $field_value = $like ? "%" . $field_like . "%" : $field_like;
     mysqli_stmt_bind_param($statement, $var, $field_value);
     mysqli_stmt_execute($statement);
     // Связываем переменную со значением результата запроса и получаем значение результата
     mysqli_stmt_bind_result($statement, $id);
     $arr_of_ids = array();
     if (mysqli_stmt_fetch($statement)) {
         $arr_of_ids[] = $id;
     }
     if (!empty($arr_of_ids)) {
         return $arr_of_ids;
     } else {
         return NULL;
     }
 }

 /**
  * @param $name
  * @return mixed
  */
 public function checkRepeatName($name)
 {
     $stmt = mysqli_prepare($this->connection, 'SELECT author_name FROM authors WHERE author_name =?');
     mysqli_stmt_bind_param($stmt, 's', $name);
     mysqli_stmt_execute($stmt);
     mysqli_stmt_bind_result($stmt, $hasAuthor);
     mysqli_stmt_fetch($stmt);
     return $hasAuthor;
 }

function bind_twice($link, $engine, $sql_type1, $sql_type2, $bind_type1, $bind_type2, $bind_value1, $bind_value2, $offset)
{
    if (!mysqli_query($link, "DROP TABLE IF EXISTS test_mysqli_stmt_bind_param_type_juggling_table_1")) {
        printf("[%03d + 1] [%d] %s\n", $offset, mysqli_errno($link), mysqli_error($link));
        return false;
    }
    mysqli_autocommit($link, true);
    $sql = sprintf("CREATE TABLE test_mysqli_stmt_bind_param_type_juggling_table_1(col1 %s, col2 %s) ENGINE=%s", $sql_type1, $sql_type2, $engine);
    if (!mysqli_query($link, $sql)) {
        printf("[%03d + 2] [%d] %s\n", $offset, mysqli_errno($link), mysqli_error($link));
        return false;
    }
    if (!($stmt = mysqli_stmt_init($link))) {
        printf("[%03d + 3] [%d] %s\n", $offset, mysqli_errno($link), mysqli_error($link));
        return false;
    }
    if (!mysqli_stmt_prepare($stmt, "INSERT INTO test_mysqli_stmt_bind_param_type_juggling_table_1(col1, col2) VALUES (?, ?)")) {
        printf("[%03d + 4] [%d] %s\n", $offset, mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt));
        return false;
    }
    if (!mysqli_stmt_bind_param($stmt, $bind_type1 . $bind_type2, $bind_value1, $bind_value1)) {
        printf("[%03d + 5] [%d] %s\n", $offset, mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt));
        return false;
    }
    if (!mysqli_stmt_execute($stmt)) {
        printf("[%03d + 6] [%d] %s\n", $offset, mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt));
        return false;
    }
    if (!mysqli_stmt_bind_param($stmt, $bind_type1 . $bind_type2, $bind_value1, $bind_value2)) {
        printf("[%03d + 7] [%d] %s\n", $offset, mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt));
        return false;
    }
    if (!mysqli_stmt_execute($stmt)) {
        printf("[%03d + 8] [%d] %s\n", $offset, mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt));
        return false;
    }
    mysqli_stmt_close($stmt);
    if (!($res = mysqli_query($link, "SELECT col1, col2 FROM test_mysqli_stmt_bind_param_type_juggling_table_1"))) {
        printf("[%03d + 9] [%d] %s\n", $offset, mysqli_errno($link), mysqli_error($link));
        return false;
    }
    if (2 !== ($tmp = mysqli_num_rows($res))) {
        printf("[%03d + 10] Expecting 2 rows, got %d rows [%d] %s\n", $offset, $tmp, mysqli_errno($link), mysqli_error($link));
    }
    $row = mysqli_fetch_assoc($res);
    if ($row['col1'] != $bind_value1 || $row['col2'] != $bind_value1) {
        printf("[%03d + 11] Expecting col1 = %s, col2 = %s got col1 = %s, col2 = %s - [%d] %s\n", $offset, $bind_value1, $bind_value1, $row['col1'], $row['col2'], mysqli_errno($link), mysqli_error($link));
        return false;
    }
    $row = mysqli_fetch_assoc($res);
    if ($row['col1'] != $bind_value1 || $row['col2'] != $bind_value2) {
        printf("[%03d + 12] Expecting col1 = %s, col2 = %s got col1 = %s, col2 = %s - [%d] %s\n", $offset, $bind_value1, $bind_value2, $row['col1'], $row['col2'], mysqli_errno($link), mysqli_error($link));
        return false;
    }
    mysqli_free_result($res);
    return true;
}