PHP mysqli_stmt_bind_result函数代码示例

function createExtFile($type)
{
    $pathOfExt = "C:/data/ext/";
    $pathOfDatabase = "C:/data/database/";
    $t = time();
    $temp_id = array();
    $con = mysqli_connect("localhost", "root", "1212312121", "proj4d");
    mysqli_set_charset($con, "utf8");
    $query = "SELECT id FROM " . $type . "_detail WHERE isValid = 1";
    $statement = mysqli_prepare($con, $query);
    $success = mysqli_stmt_execute($statement);
    mysqli_stmt_store_result($statement);
    mysqli_stmt_bind_result($statement, $id);
    $path = $pathOfExt . $type . $t . ".ext";
    $myfile = fopen($path, "w") or die("Unable to open file!");
    while (mysqli_stmt_fetch($statement)) {
        array_push($temp_id, $id);
    }
    $i = 0;
    for ($i; $i < sizeof($temp_id) - 1; $i++) {
        $id = $temp_id[$i];
        $txt = $pathOfDatabase . $type . "/" . $id . "/1.png;" . $id . PHP_EOL;
        fwrite($myfile, $txt);
        $txt = $pathOfDatabase . $type . "/" . $id . "/2.png;" . $id . PHP_EOL;
        fwrite($myfile, $txt);
    }
    $id = $temp_id[$i];
    $txt = $pathOfDatabase . $type . "/" . $id . "/1.png;" . $id . PHP_EOL;
    fwrite($myfile, $txt);
    $txt = $pathOfDatabase . $type . "/" . $id . "/2.png;" . $id;
    fwrite($myfile, $txt);
    fclose($myfile);
    return $type . $t . ".ext";
}

function update_vote($image_id)
{
    //get number of votes and update
    global $link;
    $data = array();
    $stmt = mysqli_prepare($link, "SELECT `amount` FROM `votes_amount` WHERE `imageID`=?;");
    mysqli_stmt_bind_param($stmt, 'i', $image_id);
    mysqli_stmt_execute($stmt);
    mysqli_stmt_bind_result($stmt, $num);
    while (mysqli_stmt_fetch($stmt)) {
        $amount['amount'] = $num;
    }
    mysqli_stmt_close($stmt);
    $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;
}

function isInQueue()
{
    // Reference Global Variables
    global $globalHostName;
    global $globalUserName;
    global $globalPassword;
    global $globalDatabase;
    // MySQL Connection
    $connection = mysqli_connect($globalHostName, $globalUserName, $globalPassword, $globalDatabase);
    // Connection Error Handling
    if ($connection->connect_error) {
        // Kill the Connection
        die("Could Not Connect to the Database");
    }
    // MySQL Injection Neutralized Email Variable
    $safeEmail = mysqli_real_escape_string($connection, $_REQUEST['inputEmail']);
    // Query Preparation
    $query = mysqli_prepare($connection, 'SELECT COUNT(*) as total FROM users WHERE email = ?');
    $query->bind_param('s', $safeEmail);
    // Query Execution
    mysqli_stmt_execute($query);
    // Query Result Analysis
    mysqli_stmt_bind_result($query, $total);
    $data = mysqli_stmt_fetch($query);
    //-----
    $connection->close();
    // If That Email is Already Registered...
    if ($total > 0) {
        echo "true";
        return true;
    } else {
        echo "false";
        return false;
    }
}

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 registrator($link)
{
    //Функция регистрации пользователя (Взято из интернета "редактированно")
    if (!empty($_POST["submit"])) {
        if (!preg_match("/^[a-zA-Z0-9]+\$/", $_POST['login'])) {
            $err[] = "Логин может состоять только из букв английского алфавита и цифр<br>";
        }
        if (strlen($_POST['login']) < 3 or strlen($_POST['login']) > 30) {
            $err[] = "Логин должен быть не меньше 3-х символов и не больше 30<br>";
        }
        $query = "SELECT COUNT(user_id) FROM users WHERE user_login='" . mysqli_real_escape_string($link, $_POST['login']) . "'";
        if ($stmt = mysqli_prepare($link, $query)) {
            mysqli_stmt_execute($stmt);
            mysqli_stmt_bind_result($stmt, $user_id);
            mysqli_stmt_store_result($stmt);
            mysqli_stmt_fetch($stmt);
            mysqli_stmt_close($stmt);
        }
        if (!$user_id == 0) {
            $err[] = "Пользователь с таким логином уже существует в базе данных<br>";
        }
        if (count($err) == 0) {
            $login = $_POST['login'];
            $password = md5(md5(trim($_POST['password'])));
            mysqli_query($link, "INSERT INTO users SET user_login='" . $login . "', user_password='" . $password . "'");
            header("Location: login.php");
            exit;
        } else {
            print "<b>При регистрации произошли следующие ошибки:</b><br>";
            foreach ($err as $error) {
                print $error . "<br>";
            }
        }
    }
}

 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;
     }
 }

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 mysqli_fetch_array_large($offset, $link, $package_size)
{
    /* we are aiming for maximum compression to test MYSQLI_CLIENT_COMPRESS */
    $random_char = str_repeat('a', 255);
    $sql = "INSERT INTO test(label) VALUES ";
    while (strlen($sql) < $package_size - 259) {
        $sql .= sprintf("('%s'), ", $random_char);
    }
    $sql = substr($sql, 0, -2);
    $len = strlen($sql);
    assert($len < $package_size);
    if (!@mysqli_query($link, $sql)) {
        if (1153 == mysqli_errno($link) || 2006 == mysqli_errno($link) || stristr(mysqli_error($link), 'max_allowed_packet')) {
            /*
            	myslqnd - [1153] Got a packet bigger than 'max_allowed_packet' bytes
            	libmysql -[2006] MySQL server has gone away
            */
            return false;
        }
        printf("[%03d + 1] len = %d, [%d] %s\n", $offset, $len, mysqli_errno($link), mysqli_error($link));
        return false;
    }
    /* buffered result set - let's hope we do not run into PHP memory limit... */
    if (!($res = mysqli_query($link, "SELECT id, label FROM test"))) {
        printf("[%03d + 2] len = %d, [%d] %s\n", $offset, $len, mysqli_errno($link), mysqli_error($link));
        return false;
    }
    while ($row = mysqli_fetch_assoc($res)) {
        if ($row['label'] != $random_char) {
            printf("[%03d + 3] Wrong results - expecting '%s' got '%s', len = %d, [%d] %s\n", $offset, $random_char, $row['label'], $len, mysqli_errno($link), mysqli_error($link));
            return false;
        }
    }
    mysqli_free_result($res);
    if (!($stmt = mysqli_prepare($link, "SELECT id, label FROM test"))) {
        printf("[%03d + 4] len = %d, [%d] %s\n", $offset, $len, mysqli_errno($link), mysqli_error($link));
        return false;
    }
    /* unbuffered result set */
    if (!mysqli_stmt_execute($stmt)) {
        printf("[%03d + 5] len = %d, [%d] %s, [%d] %s\n", $offset, $len, mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt), mysqli_errno($link), mysqli_error($link));
        return false;
    }
    $id = $label = NULL;
    if (!mysqli_stmt_bind_result($stmt, $id, $label)) {
        printf("[%03d + 6] len = %d, [%d] %s, [%d] %s\n", $offset, $len, mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt), mysqli_errno($link), mysqli_error($link));
        return false;
    }
    while (mysqli_stmt_fetch($stmt)) {
        if ($label != $random_char) {
            printf("[%03d + 7] Wrong results - expecting '%s' got '%s', len = %d, [%d] %s\n", $offset, $random_char, $label, $len, mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt));
            return false;
        }
    }
    mysqli_stmt_free_result($stmt);
    mysqli_stmt_close($stmt);
    return true;
}

 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;
     }
 }

function isValid($inputEmail)
{
    // Reference Global Variables
    global $globalHostName;
    global $globalUserName;
    global $globalPassword;
    global $globalDatabase;
    if (empty($inputEmail) || !isset($inputEmail) || is_null($inputEmail) || str_replace(" ", "", $inputEmail) == "") {
        echo "false";
        return false;
    }
    if (stristr(strtolower($inputEmail), "@sharklasers") || stristr(strtolower($inputEmail), "@guerrillamail") || stristr(strtolower($inputEmail), "@grr") || stristr(strtolower($inputEmail), "@spam4") || stristr(strtolower($inputEmail), "@trbvm.") || stristr(strtolower($inputEmail), "@mailinator") || stristr(strtolower($inputEmail), "@throam")) {
        echo "false";
        return false;
    }
    // Gmail Additional Validation
    $emailPieces = explode("@", $inputEmail);
    // Check if the Address is a Gmail Address
    if (stristr(strtolower($emailPieces[1]), "gmail")) {
        // If There are Periods or Plus Signs in the First Part of the Email, Notify the User to Remove Them
        if (stristr(strtolower($emailPieces[0]), "+")) {
            echo "false";
            return false;
        }
        if (stristr(strtolower($emailPieces[0]), ".")) {
            echo "false";
            return false;
        }
    }
    // MySQL Connection
    $connection = mysqli_connect($globalHostName, $globalUserName, $globalPassword, $globalDatabase);
    // Connection Error Handling
    if ($connection->connect_error) {
        // Kill the Connection
        die("Could Not Connect to the Database");
    }
    // MySQL Injection Neutralized Email Variable
    $safeEmail = mysqli_real_escape_string($connection, $inputEmail);
    // Query Preparation
    $query = mysqli_prepare($connection, 'SELECT COUNT(*) as total FROM users WHERE email = ?');
    $query->bind_param('s', $safeEmail);
    // Query Execution
    mysqli_stmt_execute($query);
    // Query Result Analysis
    mysqli_stmt_bind_result($query, $total);
    $data = mysqli_stmt_fetch($query);
    //-----
    $connection->close();
    // If That Email is Already Registered...
    if ($total < 1) {
        echo "true";
        return true;
    } else {
        echo "false";
        return false;
    }
}

 public function getBook()
 {
     $books = [];
     $stmt = mysqli_prepare($this->connection, 'SELECT book_id,book_title FROM books');
     mysqli_stmt_bind_result($stmt, $bookId, $bookName);
     mysqli_stmt_execute($stmt);
     while (mysqli_stmt_fetch($stmt)) {
         $books[$bookId] = $bookName;
     }
     return $books;
 }

 /**
  * @return array
  */
 public function selectAllAuthors()
 {
     $authors = [];
     $stmt = mysqli_prepare($this->connection, 'SELECT author_id, author_name FROM authors');
     mysqli_stmt_execute($stmt);
     mysqli_stmt_bind_result($stmt, $authorID, $author);
     while (mysqli_stmt_fetch($stmt)) {
         $authors[$authorID] = $author;
     }
     return $authors;
 }

function test_format($link, $format, $from, $order_by, $expected, $offset)
{
    if (!($stmt = mysqli_stmt_init($link))) {
        printf("[%03d] Cannot create PS, [%d] %s\n", $offset, mysqli_errno($link), mysqli_error($link));
        return false;
    }
    if ($order_by) {
        $sql = sprintf('SELECT %s AS _format FROM %s ORDER BY %s', $format, $from, $order_by);
    } else {
        $sql = sprintf('SELECT %s AS _format FROM %s', $format, $from);
    }
    if (!mysqli_stmt_prepare($stmt, $sql)) {
        printf("[%03d] Cannot prepare PS, [%d] %s\n", $offset + 1, mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt));
        return false;
    }
    if (!mysqli_stmt_execute($stmt)) {
        printf("[%03d] Cannot execute PS, [%d] %s\n", $offset + 2, mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt));
        return false;
    }
    if (!mysqli_stmt_store_result($stmt)) {
        printf("[%03d] Cannot store result set, [%d] %s\n", $offset + 3, mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt));
        return false;
    }
    if (!is_array($expected)) {
        $result = null;
        if (!mysqli_stmt_bind_result($stmt, $result)) {
            printf("[%03d] Cannot bind result, [%d] %s\n", $offset + 4, mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt));
            return false;
        }
        if (!mysqli_stmt_fetch($stmt)) {
            printf("[%03d] Cannot fetch result,, [%d] %s\n", $offset + 5, mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt));
            return false;
        }
        if ($result !== $expected) {
            printf("[%03d] Expecting %s/%s got %s/%s with %s - %s.\n", $offset + 6, gettype($expected), $expected, gettype($result), $result, $format, $sql);
        }
    } else {
        $order_by_col = $result = null;
        if (!mysqli_stmt_bind_result($stmt, $order_by_col, $result)) {
            printf("[%03d] Cannot bind result, [%d] %s\n", $offset + 7, mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt));
            return false;
        }
        reset($expected);
        while ((list($k, $v) = each($expected)) && mysqli_stmt_fetch($stmt)) {
            if ($result !== $v) {
                printf("[%03d] Row %d - expecting %s/%s got %s/%s [%s] with %s - %s.\n", $offset + 8, $k, gettype($v), $v, gettype($result), $result, $order_by_col, $format, $sql);
            }
        }
    }
    mysqli_stmt_free_result($stmt);
    mysqli_stmt_close($stmt);
    return true;
}

function getGenres()
{
    global $db;
    $stmt = mysqli_prepare($db, 'SELECT id, name FROM genres');
    mysqli_stmt_execute($stmt);
    mysqli_store_result($db);
    mysqli_stmt_bind_result($stmt, $id, $genre);
    while (mysqli_stmt_fetch($stmt)) {
        $genres[$id] = $genre;
    }
    return $genres;
}

function authenticate($db, $xUser, $xPassword)
{
    #Blocks for more than 5 logins in an hour
    #if ($stmt = mysqli_prepare($db, "SELECT user AND action='failed' AND loginid > 5 WHERE date > (DATE_SUB(NOW(), INTERVAL 1 HOUR)")) {
    #	mysqli_stmt_bind_param($stmt);
    #	mysqli_stmt_execute($stmt);
    #	mysqli_stmt_bind_result($stmt);
    #	echo "Too many login attempts";
    #        header("Location: /hw10/login.php");
    #        exit;
    #	}
    $xUser = mysqli_real_escape_string($db, $xUser);
    if ($stmt = mysqli_prepare($db, "SELECT password, salt, userid from users WHERE username=?")) {
        mysqli_stmt_bind_param($stmt, "s", $xUser);
        mysqli_stmt_execute($stmt);
        mysqli_stmt_bind_result($stmt, $password, $salt, $userid);
        while (mysqli_stmt_fetch($stmt)) {
            $userid = $userid;
            $password = $password;
            $salt = $salt;
        }
        mysqli_stmt_close($stmt);
        $epass = hash('sha256', $xPassword . $salt);
        if ($epass == $password) {
            $_SESSION['userid'] = $userid;
            #$_SESSION['email']=$email;
            $_SESSION['authenticated'] = "yes";
            $_SESSION['ip'] = $_SERVER['REMOTE_ADDR'];
            $_SESSION['HTTP_USER_AGENT'] = md5($_SERVER['HTTP_USER_AGENT']);
            $_SESSION['created'] = time();
            $ip = mysqli_real_escape_string($db, $_SERVER['REMOTE_ADDR']);
            if ($stmt = mysqli_prepare($db, "INSERT INTO login set loginid='', action='accepted', ip=?, user=?, date=now()")) {
                mysqli_stmt_bind_param($stmt, "ss", $ip, $xUser);
                mysqli_stmt_execute($stmt);
                mysqli_stmt_close($stmt);
            }
        } else {
            $ip = mysqli_real_escape_string($db, $_SERVER['REMOTE_ADDR']);
            if ($stmt = mysqli_prepare($db, "INSERT INTO login set loginid='', action='failed', ip=?, user=?, date=now()")) {
                mysqli_stmt_bind_param($stmt, "ss", $ip, $xUser);
                mysqli_stmt_execute($stmt);
                mysqli_stmt_close($stmt);
                echo "Failed to Login";
                header("Location: /hw10/login.php");
                exit;
            }
        }
    } else {
        echo "Failed to Login";
        header("Location: /hw10/login.php");
        exit;
    }
}