PHP MySQLi::prepare方法代码示例

function del_imgs($kw)
{
    if (strlen(trim($kw)) <= 0) {
        return "";
    }
    $db = new MySQLi(DB_HOST, DB_USER, DB_PASS, DB_NAME);
    if ($db->connect_error) {
        return "";
    }
    echo "<table class='table'><tr><th class='text-center'>序号</th><th class='text-center'>删除文件</th>";
    echo "<th class='text-center'><button class='btn btn-danger btn-xs' onclick='self_close();' >关闭</button></th></tr>";
    $ps = explode(",", $kw);
    $count = 0;
    $res = null;
    foreach ($ps as $p) {
        if (strpos($p, "img/") !== 0) {
            continue;
        }
        if (!file_exists($p)) {
            continue;
        }
        $count++;
        $sql = "delete from " . TB_PIC . " where url='" . $p . "'";
        echo "<tr><td class='text-center'>{$count}</td><td>{$p}</td>";
        $res = $db->prepare($sql);
        $res->execute();
        unlink($p);
        if ($res) {
            echo "<td class='text-center'>Success</td></tr>";
        } else {
            echo "<td class='text-center'>Fail</td></tr>";
        }
    }
    echo "</table>";
}

 /**
  * To prevent random errors being thrown to the users, I override this
  * method and catch the error as-it-happens, to send it to the debug
  * channel afterwards. The behaviour of the method is not changed.
  * 
  * @param string $sStatement The statement to prepare.
  * @return MySQLi_STMT
  */
 public function prepare($sStatement)
 {
     $pStatement = parent::prepare($sStatement);
     if (!is_object($pStatement)) {
         ModuleManager::getInstance()->offsetGet('LVPEchoHandler')->error(null, LVP::DEBUG_CHANNEL, 'Preparing statement failed: ' . $this->error);
         return false;
     }
     return $pStatement;
 }

 /**
  * Adds a user to the authorized_users table. Returns the username.
  * @param String $username
  * @param String $password
  * @return AdminUser the new user.
  */
 public function add_user($username, $password)
 {
     //We do not want to store the password as plain text. Instead, we
     //are going to get a hash of the password using password_hash.
     //This way, in the event of a database comprimise, your user's
     //passwords will not be given away to attackers. This is especially
     //important becuase many people use the same password on all sites.
     $phash = password_hash($password, PASSWORD_DEFAULT);
     $query = 'INSERT INTO authorized_users (username, phash) VALUES (?,?)';
     $stmt = $this->mysqli->prepare($query);
     $stmt->bind_param('ss', $username, $phash);
     $stmt->execute();
     if ($stmt->error) {
         return self::$DATABASE_ERROR;
     } else {
         return new AdminUser($username, $phash);
     }
 }

/**
 * Data sourcing - this is a little bit of commentary on the SQL as I found it
 * interesting.
 * 
 * We want to get each goal, and it's latest update (but not if no update exists).
 *
 * The method of doing this is fairly complex (I need to learn about it) - the
 * "per-group-maximum" question. Source:
 * http://stackoverflow.com/questions/3448573/is-it-possible-to/3448816#3448816
 * http://kristiannielsen.livejournal.com/6745.html
 *
 * This could be done using an ORM, by loading all of the goals and then
 * getting the latest status for each one - however this means executing 1 +
 * (the number of results) queries. - this single slow(er) query is better than
 * sending lots of queries, although an ORM is easier to understand and could be
 * cached to remove the bottleneck.
 */
function getGoals(MySQLi $db)
{
    $sql = <<<SQL
    SELECT
        Goal.id as id,
        Goal.name as name,
        g1.value AS value,
        Goal.value_mask AS mask,
        Goal.created AS created,
        g1.created AS updated
    FROM GoalStatus g1                      -- The first GoalStatus.
    LEFT JOIN Goal ON Goal.id = g1.goal     -- Goal information.
    WHERE EXISTS                            -- Check the goal exists if statuses
        (SELECT id                          -- exist from a deleted goal.
         FROM Goal 
         WHERE id = g1.goal)
    AND g1.created =                        -- Find the highest GS.created, by
        (SELECT MAX(created)                -- comparing the current GS.created to
         FROM GoalStatus g2                 -- the highest in the table.
         WHERE g1.goal = g2.goal);
SQL;
    $goalStmt = $db->prepare($sql);
    $goalStmt->execute();
    $goalStmt->bind_result($id, $name, $value, $mask, $created, $updated);
    $goals = array();
    $i = 0;
    // Fill an array
    while ($goalStmt->fetch()) {
        $value = (int) $value;
        $goals[] = array("id" => $id, "name" => $name, "value" => $value, "mask" => $mask, "value_label" => "", "created" => $created, "updated" => $updated, "width" => $value > 100 ? 100 : $value);
        // For done/not done tasks
        if (trim($mask) == "?" && ($value == 1 || $value == 0)) {
            $goals[$i]["value_label"] = $value == 1 ? "Completed" : "Not completed";
            $goals[$i]["width"] = $value == 1 ? 100 : 0;
        } else {
            $goals[$i]["value_label"] = str_replace("?", $value, $mask);
        }
        $i++;
    }
    $goalStmt->close();
    return $goals;
}

function getPlayers()
{
    $mysqli = new MySQLi("localhost", "wotmanager", "DT5HtrpaUlJ3iVGm", "testing", 3306);
    $stmt = $mysqli->prepare("SELECT ID,nick,battles,tier,wn8,wr,rwn8,rwr,clan,time FROM result order by time desc");
    $stmt->bind_result($id, $nick, $battles, $tier, $wn8, $wr, $rwn8, $rwr, $clan, $updated);
    $stmt->execute();
    while ($stmt->fetch()) {
        echo '<tr>
      <td><b><a href="http://stats.wotmanager.com/na/player/' . $nick . '" target="_blank">' . $nick . '</a></b></td>
      <td><b><font color="' . getColor($battles, "bat") . '">' . $battles . '</font></b></td>
      <td><b><font color="' . getColor($tier, "tier") . '">' . $tier . '</font></b></td>
      <td><b><font color="' . getColor($wn8, "wn8") . '">' . $wn8 . '</font></b></td>
      <td><b><font color="' . getColor($wr, "wr") . '">' . $wr . '</font></b></td>
      <td><b><font color="' . getColor($rwn8, "wn8") . '">' . $rwn8 . '</font></b></td>
      <td><b><font color="' . getColor($rwr, "wr") . '">' . $rwr . '</font></b></td>
      <td>' . $clan . '</td>
      <td>' . $updated . '</td>
      <td><a href="http://forum.worldoftanks.com/index.php?app=members&module=messaging&section=send&do=form&preview=1&_from=quickPM&fromMemberID=' . $id . '"target="_blank"><i class="fa fa-envelope fa-lg"></i></a></td>
    </tr>';
    }
}

function get_imgs($kw)
{
    if (strlen(trim($kw)) <= 0) {
        return "";
    }
    $db = new MySQLi(DB_HOST, DB_USER, DB_PASS, DB_NAME);
    if ($db->connect_error) {
        return "";
    }
    $sql = "select url from " . TB_PIC . generate_sub_sql($kw) . "ORDER BY name";
    $res = $db->prepare($sql);
    $res->execute();
    $url = null;
    $res->bind_result($url);
    $img = "[ ";
    while ($res->fetch()) {
        if (file_exists($url . 'rs')) {
            $img .= '"' . $url . 'rs",';
        }
    }
    $img = substr($img, 0, strlen($img) - 1) . " ]";
    return $img;
}

 public function prepare($query)
 {
     return parent::prepare($this->instagram_stmt($query));
 }

 if (!preg_match('/^(?:(?:25[0-5]|2[0-4][0-9]|[0-1]?[0-9]?[0-9])\\.){3}(?:25[0-5]|2[0-4][0-9]|[0-1]?[0-9]?[0-9])$/', $request['server']) && !preg_match('/^[a-z0-9\\-\\._]+\\.[a-z]{2,4}$/i', $request['server'])) {
     $errors['server'] = 'unmatch';
 }
 // validate port number to be a number
 if (!preg_match('/^\\d{2,5}$/', $request['port'])) {
     $errors['port'] = 'unmatch';
 }
 // validate key to be 10 chars length and contain numbers and capital letters
 if (!preg_match('/^[A-Z0-9]{10}$/', $request['key'])) {
     $errors['key'] = 'unmatch';
 }
 // if validation passed
 if (!count($errors)) {
     // check did we issued code entered through the form
     $q = 'SELECT 1 FROM `keys` WHERE `key`=' . (PDOAvailable ? ':key' : '?');
     $query = $db->prepare($q);
     if (PDOAvailable) {
         $query->bindValue(':key', $request['key'], PDO::PARAM_STR);
     } else {
         $query->bind_param('s', $request['key']);
     }
     $query->execute();
     $keyExists = PDOAvailable ? $query->rowCount() : $query->num_rows;
     // if code does not exists - get off
     if (!$keyExists) {
         $errors['key'] = 'invalid';
     } else {
         // keys can be used only one
         $q = 'DELETE FROM `keys` WHERE `key`=' . (PDOAvailable ? ':key' : '?');
         $query = $db->prepare($q);
         if (PDOAvailable) {

 /**
  * Perform a query
  * @param  string      $queryText The prepared SQL statement that will be executed
  * @param  bool|string $typeDef   (Optional) The types of values that will be passed through the prepared statement. One letter per parameter
  * @param  bool|array  $params    (Optional) The array of values that will be binded to the prepared statement
  * @return mixed       Returns an array of the values received from the query or returns false on empty
  */
 private function doQuery($queryText, $typeDef = false, $params = false)
 {
     $multiQuery = true;
     if ($stmt = $this->dbc->prepare($queryText)) {
         if (count($params) == count($params, 1)) {
             $params = array($params);
             $multiQuery = false;
         }
         if ($typeDef) {
             $bindParams = array();
             $bindParamsReferences = array();
             $bindParams = array_pad($bindParams, (count($params, 1) - count($params)) / count($params), "");
             foreach ($bindParams as $key => $value) {
                 $bindParamsReferences[$key] =& $bindParams[$key];
             }
             array_unshift($bindParamsReferences, $typeDef);
             $bindParamsMethod = new ReflectionMethod('mysqli_stmt', 'bind_param');
             $bindParamsMethod->invokeArgs($stmt, $bindParamsReferences);
         }
         $result = array();
         foreach ($params as $queryKey => $query) {
             if ($typeDef) {
                 foreach ($bindParams as $paramKey => $value) {
                     $bindParams[$paramKey] = $query[$paramKey];
                 }
             }
             $queryResult = array();
             if ($stmt->execute()) {
                 $resultMetaData = $stmt->result_metadata();
                 $this->last_id = $stmt->insert_id;
                 if ($resultMetaData) {
                     $stmtRow = array();
                     $rowReferences = array();
                     while ($field = $resultMetaData->fetch_field()) {
                         $rowReferences[] =& $stmtRow[$field->name];
                     }
                     mysqli_free_result($resultMetaData);
                     $bindResultMethod = new ReflectionMethod('mysqli_stmt', 'bind_result');
                     $bindResultMethod->invokeArgs($stmt, $rowReferences);
                     while (mysqli_stmt_fetch($stmt)) {
                         $row = array();
                         foreach ($stmtRow as $key => $value) {
                             $row[$key] = $value;
                         }
                         $queryResult[] = $row;
                     }
                     mysqli_stmt_free_result($stmt);
                 } else {
                     $queryResult[] = mysqli_stmt_affected_rows($stmt);
                 }
             } else {
                 $this->error($this->dbc->error, $this->dbc->errno);
                 $queryResult[] = false;
             }
             $result[$queryKey] = $queryResult;
         }
         mysqli_stmt_close($stmt);
     } else {
         $result = false;
     }
     if ($this->dbc->error) {
         $this->error($this->dbc->error, $this->dbc->errno);
     }
     if ($multiQuery) {
         return $result;
     } else {
         return $result[0];
     }
 }

<?php

$type = $_POST['seller'];
$user = $_POST['username'];
$pass = $_POST['password1'];
$mysqli = new MySQLi("localhost", "root", "", "flat_finder");
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL:(" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}
if (!($stmt = $mysqli->prepare("SELECT Password FROM info WHERE Username=? AND BuyerSeller=?"))) {
    echo "Prepare failed : (" . $mysqli->errno . ")" . $mysqli->error;
}
if (!$stmt->bind_param('ss', $user, $type)) {
    echo "Binding failed : (" . $stmt->errno . ")" . $stmt->error;
}
if (!$stmt->execute()) {
    echo "Execution failed : (" . $stmt->errno . ")" . $stmt->error;
}
if (!$stmt->bind_result($pswd)) {
    echo "Binding result failed : (" . $stmt->errno . ")" . $stmt->error;
}
$count = 0;
while ($stmt->fetch()) {
    $hash = $pswd;
    if (password_verify($pass, $hash)) {
        $count = 1;
        /*if(!strcmp($ty,"eller"))
        		{
        			$check=0;
        		}
        		else

 /**
  * Prepares a MySQLi statement and wraps it within an
  * RPG_Database_Statement object.
  *
  * @param  string $sql
  */
 public function prepare($sql)
 {
     return new RPG_Database_Statement($this->_mysqli->prepare($sql));
 }

function show_search_result($kw, $total, $pn, $size = 25)
{
    global $imgs;
    echo "<table class='table table-striped'>";
    echo "<tr><th class='text-center'>序号</th><th class='text-center'>IP</th><th class='text-center'>时间</th>";
    echo "<th class='text-center'><button id='j_del' class='btn btn-danger btn-xs' onclick='del_pics();' >删除</button>&nbsp;";
    echo "<button class='btn btn-success btn-xs' onclick='select_all();' >全选</button></tr>";
    if ($total === 0) {
        echo "</table>";
        return;
    }
    $db = new MySQLi(DB_HOST, DB_USER, DB_PASS, DB_NAME);
    if ($db->connect_error) {
        echo "</table>";
        return;
    }
    $sql = "select ip,name,url from " . TB_PIC . generate_sub_sql($kw) . "ORDER BY name LIMIT {$pn},{$size}";
    # echo $sql;
    $res = $db->prepare($sql);
    $res->execute();
    $ip = $name = $url = null;
    $res->bind_result($ip, $name, $url);
    $count = 0;
    while ($res->fetch()) {
        $count++;
        echo "<tr>";
        echo "<td class='text-center'>{$count}</td>";
        echo "<td class='text-center'>{$ip}</td>";
        echo "<td class='text-center'><a id='j_img_{$count}' href='view.php?kw={$name}' target='_blank' title=''>";
        echo "{$name}</a></td>";
        echo "<td class='text-center' ><input type='checkbox' value='{$url}' name='j_checkbox' ></td>";
        echo "</tr>";
        $url_resize = $url . 'rs';
        if (file_exists($url_resize)) {
            $imgs .= "\$( \"#j_img_{$count}\" ).tooltip({ content: '<img src=\"{$url_resize}\" alt=\"{$name}\" width=\"280\"/>' }); \n";
        } else {
            $imgs .= "\$( \"#j_img_{$count}\" ).tooltip({ content: '<img src=\"{$url}\" alt=\"{$name}\" width=\"280\"/>' }); \n";
        }
    }
    echo "</table>";
}

 /**
  * Prepares the query for execution
  */
 private function _prepareQuery()
 {
     $this->_query = $this->_db->prepare($this->_query_string);
 }

 private function AddSet()
 {
     if ($this->get_request_method() != "POST") {
         $this->response('', 406);
     }
     $action = $this->_request['Action'];
     $query = "";
     $setName = $this->_request['Name'];
     $setNumber = $this->_request['Number'];
     $revision = $this->_request['Revision'];
     $universal = $setNumber . "-" . $revision;
     $pieces = $this->_request['Pieces'];
     $theme = $this->_request['Theme'];
     $subtheme = $this->_request['Subtheme'];
     $wptag = $this->_request['Tag'];
     $image = $this->_request['Image'];
     $released = $this->_request['Start'];
     $retired = $this->_request['End'];
     $description = $this->_request['Description'];
     $stub = $this->stubify($setName);
     if ($released == '') {
         $released = null;
     }
     if ($retired == '') {
         $retired = null;
     }
     $mysqli = new MySQLi(self::DB_SERVER, self::DB_USER, self::DB_PASSWORD, self::DB);
     if ($action == "new") {
         // $query = $mysqli->prepare("INSERT into test_setguide_main
         // set_number,revision,set_name,set_stub,theme_id,subtheme_id,pieces,
         // root_image,date_released,date_retired,wordpress_tag)
         // values(?,?,?,?,?,?,?,?,?,?,?)");
         // $query->bind_param('iisssiiisiis',$number,$revision,$name,$stub,$theme,$subtheme,
         // $pieces,$image,$released,$retired,$wptag);
         $query = $mysqli->prepare("INSERT into test_setguide_main \r\n\t\t\t\t\t(set_number,set_name,theme_id,subtheme_id,pieces,root_image,revision,\r\n\t\t\t\t\tset_stub,date_released,date_retired,wordpress_tag,universal_id,`description`) \r\n\t\t\t\t\tvalues(?,?,?,?,?,?,?,?,?,?,?,?,?)");
         $query->bind_param('isiiisisiisss', $setNumber, $setName, $theme, $subtheme, $pieces, $image, $revision, $stub, $released, $retired, $wptag, $universal, $description);
     } else {
         $query = $mysqli->prepare("UPDATE test_setguide_main SET \r\n\t\t\t\t\tset_name=?,theme_id=?,subtheme_id=?,pieces=?,\r\n\t\t\t\t\troot_image=?,revision=?,set_stub=?,date_released=?,date_retired=?,\r\n\t\t\t\t\twordpress_tag=?,universal_id=?,`description`=? \r\n\t\t\t\t\tWHERE  set_number=" . $setNumber);
         $query->bind_param('siiisisiisss', $setName, $theme, $subtheme, $pieces, $image, $revision, $stub, $released, $retired, $wptag, $universal, $description);
     }
     $query->execute();
     $mysqli->close();
     $this->response('', 200);
 }

 /**
  * @param $query
  * @return \mysqli_stmt
  */
 public function prepare($query)
 {
     return $this->_db->prepare($query);
 }