PHP mysqli::prepare方法代码示例

function writeLog($Redirected_URL)
{
    //	Add logging to MySQL database
    $mySQL_username = "";
    $mySQL_password = "";
    $mySQL_database = "";
    //	Connect to database
    $mysqli = new mysqli('localhost', $mySQL_username, $mySQL_password, $mySQL_database);
    /* check connection */
    if (!mysqli_connect_errno()) {
        /* create a prepared statement */
        if ($stmt = $mysqli->prepare("INSERT INTO `stats`\t(`Datetime`,\t`UA`,\t`IP`,\t`Languages`,\t`Domain`,\t`Path`,\t`Destination`) \n\t\t\t\t\t\t\t\t\tVALUES \t(?,\t\t\t\t?,\t\t?,\t\t?,\t\t\t\t \t?,\t\t\t\t?,\t\t\t?)")) {
            $stmt->bind_param('sssssss', $datetime, $ua, $ip, $languages, $domain, $path, $destination);
            $datetime = date("Y-m-d H:i:s");
            $ua = $_SERVER['HTTP_USER_AGENT'];
            $ip = $_SERVER["REMOTE_ADDR"];
            $languages = $_SERVER['HTTP_ACCEPT_LANGUAGE'];
            $domain = $_SERVER['SERVER_NAME'];
            $path = stripslashes($_GET['title']);
            $destination = $Redirected_URL;
            /* execute prepared statement */
            $stmt->execute();
            /* close statement and connection */
            $stmt->close();
            // Write a log file entry for each visitor
            $myFile = "log.txt";
            $fh = fopen($myFile, 'a+');
            // Tab separated. Date/Time	User Agent	IP Address	Language	Server requested	Page requested
            $stringData = $datetime . "\t" . $ua . "\t" . $ip . "\t" . $languages . "\t" . $domain . "\t" . $path . "\t" . $destination . "\n";
            fwrite($fh, $stringData);
            fclose($fh);
        }
    }
}

    public function saveMessage($msg)
    {
        $data = json_decode($msg);
        $conversationId = $data->id;
        $userId = $data->userId;
        $content = $data->content;
        $db = new \mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
        $stmt = $db->prepare('
		INSERT INTO messages
		(
		conversationId,
		userId,
		content,
		date
		)
		VALUES
		(
		?,
		?,
		?,
		?
		)
		');
        if ($stmt) {
            $stmt->bind_param('iiss', $conversationId, $userId, $content, date('Y-m-d H:i:s'));
            $stmt->execute();
            $stmt->close();
            $db->close();
            return true;
        } else {
            return false;
        }
    }

function getMailData()
{
    $mysqli = new mysqli($GLOBALS["servername"], $GLOBALS["server_username"], $GLOBALS["server_password"], $GLOBALS["database"]);
    $stmt = $mysqli->prepare("SELECT comment_id, user_id, text FROM eksam_comment WHERE send_email = ?");
    $stmt->bind_param("s", $_SESSION["user_email"]);
    $stmt->bind_result($comment_id, $id_mail, $text);
    $stmt->execute();
    // tühi massiiv kus hoiame objekte (1 rida andmeid)
    $array = array();
    // tee tsüklit nii mitu korda, kui saad
    // ab'ist ühe rea andmeid
    while ($stmt->fetch()) {
        // loon objekti iga while tsükli kord
        $mail = new StdClass();
        $mail->comment_id = $comment_id;
        $mail->id_mail = $id_mail;
        $mail->text = $text;
        // lisame selle massiivi
        array_push($array, $mail);
        //echo "<pre>";
        //var_dump($array);
        //echo "</pre>";
    }
    $stmt->close();
    $mysqli->close();
    return $array;
}

function loadData($request)
{
    global $hostname_ISANdb;
    global $database_ISANdb;
    global $username_ISANdb;
    global $password_ISANdb;
    $mysqli = new mysqli($hostname_ISANdb, $username_ISANdb, $password_ISANdb, $database_ISANdb);
    if ($mysqli->connect_error) {
        die('Connect Error (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error);
    }
    $mysqli->query("SET NAMES 'UTF8'");
    if ($request == "person") {
        if ($stmt = $mysqli->prepare("SELECT id, firstName, lastName, patrName FROM `{$request}` WHERE 1")) {
            $stmt->execute();
            $stmt->bind_result($id, $fname, $lname, $pname);
            while ($stmt->fetch()) {
                $result .= "<option value=\"{$id}\">{$lname} {$fname} {$pname}</option>";
            }
            $mysqli->close();
            return $result;
        }
    }
    if ($stmt = $mysqli->prepare("SELECT id, name FROM `{$request}` WHERE 1")) {
        $stmt->execute();
        $stmt->bind_result($id, $name);
        $result = "<option value=\"0\">Не выбрано</option>";
        while ($stmt->fetch()) {
            $result .= "<option value=\"{$id}\">{$name}</option>";
        }
        $mysqli->close();
        return $result;
    }
}

function getClassi($idClasse)
{
    // Create connection
    $conn = new mysqli(AIRO_CONN_SERVERNAME, AIRO_CONN_USERNAME, AIRO_CONN_PASSWORD, AIRO_CONN_DBNAME);
    // Check connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }
    // preparazione dello statement SQL
    // se il parametro è null, viene scelto uno statement SQL senza criteri di filtro.
    if (is_null($idClasse)) {
        $stmt = $conn->prepare(AIRO_SQL_GET_CLASSI_ALL);
    } else {
        $stmt = $conn->prepare(AIRO_SQL_GET_CLASSI_BY_ID);
        $stmt->bind_param("s", $idClasse);
    }
    $stmt->execute();
    $result = $stmt->get_result();
    $esito = "";
    if ($result->num_rows > 0) {
        $row = $result->fetch_assoc();
        $esito = $row['DescrizioneClasse'];
    }
    $conn->close();
    return $esito;
}

function authenticateUser($username, $password)
{
    $con = new mysqli('localhost', 'root', 'rooty', 'glossary');
    $Squery = "SELECT `password_salt` FROM `users` WHERE `username` = ? OR `email` = ?";
    $Sstmt = $con->prepare($Squery);
    $Sstmt->bind_param('ss', $username, $password);
    $Sstmt->execute();
    $Sstmt->bind_result($salt);
    while ($Sstmt->fetch()) {
        $salt = $salt;
    }
    $password_hash = sha1($password . $salt);
    $Lquery = "SELECT `user_id`, `user_type`, `full_name` FROM `users` WHERE (`username` = ? OR `email` = ?) AND `password_hash` = ?";
    $Lstmt = $con->prepare($Lquery);
    $Lstmt->bind_param('sss', $username, $email, $password_hash);
    $Lstmt->execute();
    $Lstmt->bind_result($user_id, $user_type, $full_name);
    while ($Lstmt->fetch()) {
        $user_id = $user_id;
        $user_type = $user_type;
        $full_name = $full_name;
    }
    if (!is_null($user_id)) {
        $_SESSION['user_id'] = $user_id;
        $_SESSION['user_type'] = $user_type;
        $_SESSION['username'] = $username;
        $_SESSION['full_name'] = $full_name;
        return true;
    } else {
        return false;
    }
}

 /**
  * Delete model by id.
  *
  * @param ModelBase $model
  * @throws ControllerException
  */
 protected function _deleteModelById($sql, $model)
 {
     if ($model->validateForDelete()) {
         $stmt = $this->_dbh->prepare($sql);
         if (!$stmt) {
             throw new ControllerException('Prepared statement failed for ' . $sql);
         }
         $id = $model->getId();
         if (!$stmt->bind_param('i', $id)) {
             throw new ControllerException('Binding parameters for prepared statement failed.');
         }
         if (!$stmt->execute()) {
             throw new ControllerException('Failed to execute DELETE statement. (' . $this->_dbh->error . ')');
         }
         /**
          * @SuppressWarnings checkAliases
          */
         if (!$stmt->close()) {
             throw new ControllerException('Something broke while trying to close the prepared statement.');
         }
         return;
     } else {
         throw new ControllerException('Invalid data');
     }
 }

function registerPlayer($xml)
{
    global $sqlhost, $sqlusername, $sqlpassword;
    #Check if registration credentials are valid.
    if ($xml->username == null or $xml->password == null) {
        error_log("registerPlayer.php - Registration rejected");
        return false;
    } else {
        #Clean up registration credentials.
        $tempUsername = preg_replace("/[^A-Za-z0-9]/", '', $xml->username);
        $tempPassword = preg_replace("/[^A-Za-z0-9]/", '', $xml->password);
        #Validate that username and password are legal.
        if (strlen($xml->username) == 0) {
            return false;
        } else {
            if (strlen($xml->password) == 0) {
                return false;
            } else {
                if ($tempUsername !== (string) $xml->username) {
                    return false;
                } else {
                    if ($tempPassword !== (string) $xml->password) {
                        return false;
                    } else {
                        $conn = new mysqli($sqlhost, $sqlusername, $sqlpassword);
                        if ($conn->connect_error) {
                            error_log("registerPlayer.php - Connection failed: " . $conn->connect_error);
                            return false;
                        }
                        #Check if username already taken
                        if ($checkStmt = $conn->prepare("SELECT COUNT(*) FROM sweepelite.players WHERE username=?")) {
                            $checkStmt->bind_param("s", $xml->username);
                            $checkStmt->execute();
                            $checkStmt->bind_result($count);
                            $checkStmt->close();
                            if ($count == 0) {
                                #Register the player in the MySQL database.
                                if ($registerStmt = $conn->prepare("INSERT INTO sweepelite.players (username, password, salt) VALUES (?,?,?)")) {
                                    $salt = sec_getNewSalt();
                                    $saltedPW = sec_getHashedValue($xml->password, $salt);
                                    $registerStmt->bind_param("sss", $xml->username, $saltedPW, $salt);
                                    $registerStmt->execute();
                                    if ($registerStmt->affected_rows > 0) {
                                        return true;
                                    } else {
                                        error_log("registerPlayer.php - Unable to register player.");
                                    }
                                }
                            }
                        } else {
                            error_log("registerPlayer.php - Unable to prepare statement for checking registration.");
                        }
                    }
                }
            }
        }
    }
    return false;
}

 public function addAchievements()
 {
     /* @var $stmt mysqli_stmt */
     $stmt = $this->db->prepare("INSERT INTO `achievements` (`user_id`, `count`) VALUES (?, 1)\r\n            ON DUPLICATE KEY UPDATE `count` = `count` + 1");
     $stmt->bind_param('s', $this->userid);
     $stmt->execute();
     return $this->getAchievements();
 }

function clear_uploads()
{
    $db = new mysqli(getenv('HOST_IP'), 'root', getenv('MYSQL_ROOT_PASSWORD'), getenv('WORDPRESS_DATABASE'));
    $statement = $db->prepare("DELETE wp_postmeta FROM wp_postmeta JOIN wp_posts ON wp_posts.ID = wp_postmeta.post_id WHERE wp_posts.post_type = 'attachment'");
    $statement->execute();
    $statement = $db->prepare("DELETE FROM wp_posts WHERE wp_posts.post_type = 'attachment'");
    $statement->execute();
    shell_exec('docker exec -it wordpress' . getenv('WORDPRESS_VERSION') . ' rm -rf wp-content/uploads');
}

 public function insertReport(CrashReport $report)
 {
     if ($stmt = $this->db->prepare(self::$INSERT_REPORT)) {
         $stmt->bind_param("ssississiii", $report->getCausingPlugin(), $report->getVersion()->get(true), $report->getVersion()->getBuild(), $report->getFile(), $report->getMessage(), $report->getLine(), $report->getType(), $report->getOS(), $report->getReportType(), time(), $report->getDate());
         $stmt->execute();
         return $this->db->insert_id;
     }
     return -1;
 }

 private function checkPreparedStatement($queryname, $sql)
 {
     if (!isset($this->db_statements[$queryname])) {
         $this->db_statements[$queryname] = $this->db->prepare($sql);
     }
     if ($this->db_statements[$queryname] === false) {
         $this->criticalError("Database error preparing query for  " . $queryname . ": " . $this->db->error);
         return false;
     }
     return true;
 }

 /**
  * @param query $query
  * @return query
  */
 public function execute($query)
 {
     $stmt = $this->resource->prepare($query->getQueryString());
     foreach ($query->getArguments() as $value) {
         $stmt->bind_param($value['type'], $value['argument']);
     }
     $stmt->execute();
     $result = $stmt->get_result();
     $query->setResult($result->fetch_all())->setAffectedRows($stmt->affected_rows)->setInsertId($this->resource->insert_id);
     $stmt->close();
     $result->close();
     return $query;
 }

 /**
  * @param string $session_id
  * @param string $session_data
  * @return bool|void
  */
 public function write($session_id, $session_data)
 {
     $now = time();
     if ($this->read($session_id) === '') {
         $stmt = $this->connection->prepare("INSERT INTO session_store(session_id, session_data, updated_at) VALUES (?, ?, ?)");
         $stmt->bind_param("ssd", $session_id, $session_data, $now);
         $stmt->execute();
         $stmt->close();
     } else {
         $stmt = $this->connection->prepare("UPDATE session_store SET session_data=?, updated_at=? WHERE session_id=?");
         $stmt->bind_param("sds", $session_data, $now, $session_id);
         $stmt->execute();
         $stmt->close();
     }
 }

 function connect()
 {
     $host = 'localhost';
     $user = 'barril1_MyUser';
     $password = 'illini';
     $dbName = 'barril1_ProjectComments';
     $dbconn = new mysqli($host, $user, $password, $dbName);
     $tableExists = $dbconn->prepare("SELECT * FROM {$this->projectID}");
     if (!$tableExists) {
         $query = "CREATE TABLE {$this->projectID}(Name VARCHAR(25), Comment VARCHAR(255))";
         $stmt = $dbconn->prepare($query);
         $stmt->execute();
     }
     return $dbconn;
 }