PHP mysqli::query方法代码示例

 /**
  * Clear Magento instance: remove all tables in DB and use dump to load new ones, clear Magento cache
  *
  * @throws \Exception
  */
 public function clearInstance()
 {
     $magentoBaseDir = dirname(dirname(dirname(MTF_BP)));
     $config = simplexml_load_file($magentoBaseDir . '/app/etc/local.xml');
     $host = (string) $config->connection->host;
     $user = (string) $config->connection->username;
     $password = (string) $config->connection->password;
     $database = (string) $config->connection->dbName;
     $fileName = MTF_BP . '/' . $database . '.sql';
     if (!file_exists($fileName)) {
         echo 'Database dump was not found by path: ' . $fileName;
         return;
     }
     // Drop all tables in database
     $mysqli = new \mysqli($host, $user, $password, $database);
     $mysqli->query('SET foreign_key_checks = 0');
     if ($result = $mysqli->query("SHOW TABLES")) {
         while ($row = $result->fetch_row()) {
             $mysqli->query('DROP TABLE ' . $row[0]);
         }
     }
     $mysqli->query('SET foreign_key_checks = 1');
     $mysqli->close();
     // Load database dump
     exec("mysql -u{$user} -p{$password} {$database} < {$fileName}", $output, $result);
     if ($result) {
         throw new \Exception('Database dump loading has been failed: ' . $output);
     }
     // Clear cache
     exec("rm -rf {$magentoBaseDir}/var/*", $output, $result);
     if ($result) {
         throw new \Exception('Cleaning Magento cache has been failed: ' . $output);
     }
 }

 function connect($name, $path = false)
 {
     $this->name = $name;
     global $cfg;
     $link = new mysqli($cfg->mysqli_host, $cfg->mysqli_username, $cfg->mysqli_password);
     // Make sure database exists
     $res = $link->query("SHOW DATABASES");
     while ($row = $res->fetch_row()) {
         if ($row[0] == $name) {
             $do = true;
         }
     }
     if (!isset($do)) {
         $do = false;
     }
     if (!$do) {
         $link->query("CREATE DATABASE {$name}");
     }
     $link->select_db($name);
     // check connection
     if (mysqli_connect_errno()) {
         printf("Connect failed: %s\n", mysqli_connect_error());
         exit;
     }
     $this->link = $link;
     return $link;
 }

function getfilename($type)
{
    include '/var/www/html/Matching-Game/assets/getconfig.php';
    $conn = new mysqli("localhost", $sqlun, $sqlp, "matchthefollowinggame");
    if ($conn->connect_error) {
        die("Connection Failed:" . $conn->connect_error);
    }
    $query1 = "select max(c1name) as name from pairs where c1type='" . $type . "'";
    $result1 = $conn->query($query1);
    $query2 = "select max(c2name) as name from pairs where c2type='" . $type . "'";
    $result2 = $conn->query($query2);
    if ($result1 && $result2) {
        $data1 = $result1->fetch_assoc();
        $data2 = $result2->fetch_assoc();
        $data1 = $data1['name'];
        $data2 = $data2['name'];
        $data1 = $data1[strlen($type)];
        $data2 = $data2[strlen($type)];
        if ($data1 > $data2) {
            echo "<br>" . $type . "" . ($data1 + 1) . "<br>";
            return $type . "" . ($data1 + 1);
        } else {
            echo "<br>" . $type . "" . ($data2 + 1) . "<br>";
            return $type . "" . ($data2 + 1);
        }
    }
}

 /**
  * Query database
  *
  * @param   string        $sql          SQL Query
  * @param   array|string  $replacement  Replacement
  * @return  mixed
  */
 public function query()
 {
     $args = func_get_args();
     $sql = array_shift($args);
     // Menerapkan $replacement ke pernyataan $sql
     if (!empty($args)) {
         $sql = vsprintf($sql, $args);
     }
     try {
         // Return 'false' kalo belum ada koneksi
         if (!$this->_db) {
             $this->connect();
         }
         $this->_sql = $sql;
         // Eksekusi SQL Query
         if ($results = $this->_db->query($sql)) {
             if (is_bool($results)) {
                 return $results;
             }
             $this->_results = $results;
             $this->_num_rows = $this->_results->num_rows;
             return $this;
         } else {
             App::error($this->_db->error . '<br>' . $this->_sql);
         }
     } catch (Exception $e) {
         set_alert('error', $e->getMessage());
         return false;
     }
 }

function datapages()
{
    if ($_SESSION['userinfo']['status'] === 1) {
        $conn = new mysqli('localhost', 'root', '12345password12345', 'main');
        if ($conn->connect_error) {
            die("Connection failed: " . $conn->connect_error);
        }
        $num_rows_res = $conn->query("SELECT count(*) FROM mlib;");
        $num_rows = mysqli_fetch_array($num_rows_res, MYSQLI_NUM)[0];
        for ($i = 1; $i <= $num_rows; $i++) {
            $pages_sql = "Select * FROM mlib WHERE c_id=" . $i . ";";
            $pages_res = $conn->query($pages_sql);
            $pages[$i] = mysqli_fetch_object($pages_res);
        }
        $conn->close();
    } else {
        $conn = new mysqli('localhost', 'root', '12345password12345', 'main');
        if ($conn->connect_error) {
            die("Connection failed: " . $conn->connect_error);
        }
        $num_rows_res = $conn->query("SELECT count(*) FROM mlib WHERE viewers='all';");
        $num_rows = mysqli_fetch_array($num_rows_res, MYSQLI_NUM)[0];
        for ($i = 1; $i <= $num_rows; $i++) {
            $pages_sql = "Select * FROM mlib WHERE c_id=" . $i . " AND viewers='all';";
            $pages_res = $conn->query($pages_sql);
            $pages[$i] = mysqli_fetch_object($pages_res);
        }
        $conn->close();
    }
    return $pages;
}

 public function execute($sql)
 {
     if ($result = $this->connection->query($sql)) {
         return true || $result->close();
     }
     throw new Exception($this->connection->error);
 }

 public function onPreQuery(\mysqli $db)
 {
     $result = $db->query("SELECT tid, config, (SELECT type FROM tjrequests WHERE team=teams.tid AND user={$this->uid}) AS type FROM teams WHERE name={$this->esc($this->teamName)}");
     $checkFirst = $result->fetch_assoc();
     $result->close();
     if (!is_array($checkFirst)) {
         throw new \RuntimeException(Phrases::CMD_TEAM_ERR_NO_SUCH_TEAM);
     }
     $this->tid = (int) $checkFirst["tid"];
     $this->type = (int) $checkFirst["type"];
     $config = (int) $checkFirst["config"];
     $result->close();
     if ($config & Settings::TEAM_CONFIG_OPEN) {
         $this->type = self::DIRECT_JOIN;
         throw new \RuntimeException(Phrases::CMD_TEAM_JOIN_DIRECTLY_JOINED);
     }
     if ($this->type === self::REQUEST_FROM_USER) {
         throw new \RuntimeException(Phrases::CMD_TEAM_JOIN_ALREADY_REQUESTED);
     } elseif ($this->type === self::REQUEST_FROM_TEAM or $this->type === self::DIRECT_JOIN) {
         $query = $db->query("SELECT (SELECT COUNT(*) FROM users WHERE tid=teams.tid) AS members,slots FROM teams WHERE tid={$this->tid}");
         $row = $query->fetch_assoc();
         $query->close();
         $members = (int) $row["members"];
         $slots = (int) $row["slots"];
         if ($members >= $slots) {
             throw new \RuntimeException(Phrases::CMD_TEAM_ERR_FULL);
         }
     }
 }

 /**
  * 执行一条sql语句
  *
  * @param string $sql sql语句
  * @param bool $cache 是否缓存
  * @return ResultSet
  */
 public function query($sql, $cache = false)
 {
     // 读缓存
     if ($cache) {
         $cache = new Cache();
         $rtn = $cache->get($sql);
         if ($rtn) {
             return unserialize($rtn);
         }
     }
     // 读数据库
     switch (DBManager::workingMode()) {
         case DBManager::MODE_PDO:
             $rs = $this->_link->query($sql);
             break;
         case DBManager::MODE_SQLI:
             $rs = $this->_link->query($sql);
             break;
         case DBManager::MODE_SQL:
             $rs = mysql_query($sql, $this->_link);
             break;
         default:
             $rs = false;
             break;
     }
     $rtn = new ResultSet($rs, $sql, $cache);
     return $rtn;
 }

function IMPORT_TABLES($host, $user, $pass, $dbname, $sql_file)
{
    if (!file_exists($sql_file)) {
        die('Input the SQL filename correctly! <button onclick="window.history.back();">Click Back</button>');
    }
    $allLines = file($sql_file);
    $mysqli = new mysqli($host, $user, $pass, $dbname);
    if (mysqli_connect_errno()) {
        echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }
    $zzzzzz = $mysqli->query('SET foreign_key_checks = 0');
    preg_match_all("/\nCREATE TABLE(.*?)\\`(.*?)\\`/si", "\n" . file_get_contents($sql_file), $target_tables);
    foreach ($target_tables[2] as $table) {
        $mysqli->query('DROP TABLE IF EXISTS ' . $table);
    }
    $zzzzzz = $mysqli->query('SET foreign_key_checks = 1');
    $mysqli->query("SET NAMES 'utf8'");
    $templine = '';
    // Temporary variable, used to store current query
    foreach ($allLines as $line) {
        // Loop through each line
        if (substr($line, 0, 2) != '--' && $line != '') {
            $templine .= $line;
            // (if it is not a comment..) Add this line to the current segment
            if (substr(trim($line), -1, 1) == ';') {
                // If it has a semicolon at the end, it's the end of the query
                $mysqli->query($templine) or print 'Error performing query \'<strong>' . $templine . '\': ' . $mysqli->error . '<br /><br />';
                $templine = '';
                // Reset temp variable to empty
            }
        }
    }
    echo 'Importing finished. Now, Delete the import file.';
}

function getRoomName($room_id)
{
    $room_name = "";
    $conn = new mysqli("localhost", "mrbs", "mrbs-password", 'mrbs');
    if ($conn->connect_error) {
        die("connection failed.<>" . $conn->connect_error);
    }
    // echo "connected :) ";
    $query = "select room_name from mrbs_room where id = {$room_id}";
    // print $query;
    if ($conn->query($query) == True) {
        // echo "here1";
        $res = $conn->query($query);
        // print ($res[1]);
        foreach ($res as $room) {
            // print ($room["room_name"]);
            $room_name = $room["room_name"];
            // print $room_name;
        }
        // print "room found";
        // print $room_name;
        return $room_name;
    } else {
        return "Error in room_id: " . $room_id[0];
    }
    // return $room_name;
}

function enterdata($fdata, $clientid, $period, $py, $lieu, $nav, $edt, $eau, $comptetype)
{
    $totalfcp = 0;
    for ($counter = 0; $counter < count($fdata); $counter += 1) {
        $detail = explode("#", $fdata[$counter]);
        $totalfcp += $detail[1] * $detail[2];
    }
    $totalfcp += $edt;
    $totalfcp += $eau;
    $totaleuro = $totalfcp / 120;
    $totaleuro = round($totaleuro, 2);
    $today = date("Y-m-d");
    $mysqli = new mysqli(DBSERVER, DBUSER, DBPWD, DB);
    $query = "SELECT `chrono` FROM `" . DB . "`.`chrono` ORDER BY `id` DESC LIMIT 1;";
    $result = $mysqli->query($query);
    $row = $result->fetch_array(MYSQLI_ASSOC);
    $chrono = $row["chrono"] + 1;
    $query = "INSERT INTO `" . DB . "`.`chrono` (`chrono`) VALUES ('" . $chrono . "')";
    $mysqli->query($query);
    $query = "INSERT INTO `" . DB . "`.`factures_amarrage` (`idfacture`, `idclient`, `type_client`," . " `datefacture`, `communeid`, `montantfcp`, `montanteuro`, `restearegler`,`obs`,`PY`,`lieu`,`navire`,`edt`,`eau`)" . " VALUES (NULL, '" . $clientid . "', '" . $comptetype . "', '" . $today . "', '" . $chrono . "', '" . $totalfcp . "', '" . $totaleuro . "', '" . $totalfcp . "', '" . $period . "', '" . $py . "', '" . $lieu . "', '" . $nav . "', '" . $edt . "', '" . $eau . "')";
    //return $query;
    $mysqli->query($query);
    $lastid = $mysqli->insert_id;
    //use it to insert the details.
    // insert details now
    for ($counter = 0; $counter < count($fdata); $counter += 1) {
        $detail = explode("#", $fdata[$counter]);
        $query = "INSERT INTO `" . DB . "`.`factures_amarrage_details` (`iddetail`, `idfacture`," . " `idtarif`, `quant`)" . " VALUES (NULL, '" . $lastid . "', '" . $detail[0] . "', '" . $detail[1] . "')";
        $mysqli->query($query);
    }
    $mysqli->close();
    return $lastid;
}

 public function GetTop3()
 {
     global $whichUser;
     $myDBConnector = new DBConnector();
     $dbARY = $myDBConnector->infos();
     $connection = new mysqli($dbARY[0], $dbARY[1], $dbARY[2], $dbARY[3]);
     if ($connection->connect_error) {
         return false;
     } else {
         $connection->set_charset("utf8");
         $ress0 = $connection->query("SELECT * FROM users WHERE userName = '{$whichUser}'");
         if ($ress0->num_rows == 0) {
             echo "<script type=\"text/javascript\"> window.location='oops.php'; </script>";
         }
         $query = "SELECT * FROM `threads` WHERE threadWriter='{$whichUser}' ORDER BY `threadPoint` DESC LIMIT 3 ";
         $results = $connection->query($query);
         if ($results->num_rows < 3) {
             return false;
         } else {
             $returnAry = array();
             while ($curResult = $results->fetch_assoc()) {
                 $returnAry[] = $curResult["threadPicture"];
                 $returnAry[] = $curResult["threadName"];
                 $returnAry[] = $curResult["threadID"];
             }
             return $returnAry;
         }
     }
 }

function CreateGroupParticipations(mysqli $objAcsDatabase, Group $objGroup, GroupRole $objRole, $intAcsGroupId, $intAcsReserveId)
{
    print "Adding for Group: " . $objGroup->Name . "\r\n      ";
    if ($intAcsReserveId) {
        $objResult = $objAcsDatabase->query(sprintf('select * from awgrrost where groupid=%s AND reserveid1=%s', $intAcsGroupId, $intAcsReserveId));
    } else {
        $objResult = $objAcsDatabase->query(sprintf('select * from awgrrost where groupid=%s', $intAcsGroupId));
    }
    while ($objRow = $objResult->fetch_array()) {
        $objAttributeValueArray = AttributeValue::QueryArray(QQ::AndCondition(QQ::Equal(QQN::AttributeValue()->AttributeId, 2), QQ::Equal(QQN::AttributeValue()->TextValue, $objRow['indvid'])));
        if (count($objAttributeValueArray) != 1) {
            printf("Issue with awgrrost.pkid of %s - IndvId %s for Group %s - AVCount of %s\r\n", $objRow['pkid'], $objRow['indvid'], $objGroup->Name, count($objAttributeValueArray));
        } else {
            $objPerson = $objAttributeValueArray[0]->Person;
            $dttStartDate = new QDateTime($objRow['dateadded']);
            if ($objRow['dateremoved']) {
                $dttEndDate = new QDateTime($objRow['dateremoved']);
            } else {
                $dttEndDate = null;
            }
            $objGroup->AddPerson($objPerson, $objRole->Id, $dttStartDate, $dttEndDate);
            print "*";
        }
    }
    print "\r\n      Done.\r\n\r\n";
}

 /**
  * @param string $text
  * @param string $author
  * @throws DbException
  */
 public function addMessage($text, $author)
 {
     $result = $this->mysqli->query("INSERT INTO `test`(`text`,`author`) VALUES ('" . $text . "', '" . $author . "')");
     if (!$result) {
         throw new DbException('Не удалось добавить сообщение');
     }
 }

 public static function detail($eventid)
 {
     $config = new Config();
     $mysqli = new mysqli($config->host, $config->user, $config->pass, $config->db);
     if ($mysqli->connect_errno) {
         return print json_encode(array('success' => false, 'status' => 400, 'msg' => 'Failed to connect to MySQL: (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error));
     } else {
         if ($eventid == 'all') {
             $query1 = "select a.*,b.* from criteria a right join events b on a.eventid = b.eventid";
             $result1 = $mysqli->query($query1);
             $data = array();
             while ($row = $result1->fetch_array(MYSQLI_ASSOC)) {
                 array_push($data, $row);
             }
             print json_encode(array('success' => true, 'status' => 200, 'childs' => $data), JSON_PRETTY_PRINT);
         } else {
             $query1 = "select a.*,b.* from criteria a right join events b on a.eventid = b.eventid and a.eventid = {$eventid}";
             $result1 = $mysqli->query($query1);
             $data = array();
             while ($row = $result1->fetch_array(MYSQLI_ASSOC)) {
                 array_push($data, $row);
             }
             print json_encode(array('success' => true, 'status' => 200, 'childs' => $data), JSON_PRETTY_PRINT);
         }
     }
 }