PHP mysqli_prepare函数代码示例

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

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

function block_temporary($ipUser)
{
    $config_file_parsed = $GLOBALS['v'];
    include_once 'database.php';
    $connection = connect($config_file_parsed["database"]["host"], $config_file_parsed["database"]["user"], $config_file_parsed["database"]["pass"], $config_file_parsed["database"]["database"]);
    if ($stmt = mysqli_prepare($connection, "SELECT attempt FROM blocked_ip WHERE ip=?")) {
        $k = 0;
        $stmt->bind_param("s", $ipUser);
        $stmt->execute();
        $stmt->bind_result($col1);
        echo 'salam: ' . $col1;
        while ($stmt->fetch()) {
            $k++;
            // printf("%i %i", $col1,$k);
        }
        echo $col1;
        $sql = '';
        if ($col1 == 0) {
            $temp = "'" . $ipUser . "'";
            $sql = "INSERT INTO `blocked_ip` VALUES (NULL,{$temp},NOW(),NOW(),1)";
        }
        if ($col1 == 1) {
            $sql = "UPDATE `blocked_ip` SET `start_date`=NOW(),`end_date`=NOW() + INTERVAL 1 HOUR,`attempt`=2 WHERE ip=" . "'" . $ipUser . "'";
        }
        if ($col1 == 2) {
            $sql = "UPDATE `blocked_ip` SET `start_date`=NOW(),`end_date`=NOW() + INTERVAL 1000 HOUR,`attempt`=3 WHERE ip=" . "'" . $ipUser . "'";
        }
        echo $sql;
        if ($col1 >= 0 && $col1 < 3) {
            mysqli_query($connection, $sql);
            //echo "<h1>afectate:</h1>".mysqli_affected_rows($connection);
        }
        $stmt->close();
    }
}

/**
 * Save data
 * @param   array   Request data (unfiltered)
 */
function saveAction($request)
{
    require_once 'config.php';
    //connection:
    $link = mysqli_connect($servidor, $user, $pass, $database) or die("Error " . mysqli_error($link));
    $flag = 'false';
    $param = $request;
    $idUrl = mysqli_real_escape_string($link, $param['idUrl']);
    $dataPost = isset($param['data']) ? $param['data'] : false;
    $idPage = _checkIdUrl($link, $idUrl);
    if ($idPage > 0 && is_array($dataPost) && count($dataPost) > 0) {
        $reg = formarDataToSerial($idPage, $dataPost);
        $reg['page_id'] = intval($reg['page_id']);
        $reg['browser_id'] = $reg['browser_id'];
        $reg['view_port'] = mysqli_real_escape_string($link, $reg['view_port']);
        $reg['window_browser'] = mysqli_real_escape_string($link, $reg['window_browser']);
        $reg['screen'] = mysqli_real_escape_string($link, $reg['screen']);
        $query = "INSERT INTO heatmap (page_id, browser_id, view_port, window_browser, screen, data_serial, created_at) " . "VALUES ('" . $reg['page_id'] . "', '" . $reg['browser_id'] . "','" . $reg['view_port'] . "','" . $reg['window_browser'] . "','" . $reg['screen'] . "', '" . $reg['data_serial'] . "', '" . date('Y-m-d H:i:s') . "')";
        $stmt = mysqli_prepare($link, $query);
        mysqli_stmt_execute($stmt);
        mysqli_stmt_close($stmt);
        $flag = 'true';
    }
    mysqli_close($link);
    echo $flag;
}

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: activation@woofwarrior.com\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 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;
    }
}

 public function query($sql, $values = array())
 {
     $sql = new Expressions($sql);
     $sql->set_connection($this);
     $sql->bind_values($values);
     $sql = trim($sql->to_s());
     $values = $values ? array_flatten($values) : array();
     if (!($sth = mysqli_prepare($this->connection, $sql))) {
         throw new DatabaseException(mysqli_error($this->connection), mysqli_errno($this->connection));
     }
     if (count($values) > 0) {
         $params = array($sth, '');
         foreach ($values as &$value) {
             if (is_int($value)) {
                 $params[1] .= 'i';
             } elseif (is_float($value)) {
                 $params[1] .= 'd';
             } else {
                 $params[1] .= 's';
             }
             $params[] =& $value;
         }
         if ($params[1]) {
             call_user_func_array('mysqli_stmt_bind_param', $params);
         }
     }
     if (is_int($ret = mysqli_stmt_execute($sth))) {
         return $ret;
     }
     return new MysqliResultSet($sth);
 }

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

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

function saveimage($name, $image)
{
    $con = mysqli_connect("localhost", "root", "root", "Alegatori") or die("Error " . mysqli_error($link));
    $qry = "INSERT INTO Candidati (Nume,Voturi,Poza,Sfarsit) VALUES ('{$name}',0,'{$image}',0)";
    $stmt = mysqli_prepare($con, $qry);
    mysqli_stmt_execute($stmt);
}

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

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

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

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