/** * Rabota s khranimy`mi protcedurami i funktciiami. * * Vy`polnenie khranimy`kh protcedur i funktcii` cherez metod peregruzki metodov * * @param string $store_procedure_name imia khranimoi` protcedury` * @param array $params spisok parametrov khranimoi` protcedury` * @return array rezul`tat */ public static function Call_Procedure($store_procedure_name, $params = []) { $quotedparams = []; foreach ($params as $param) { array_push($quotedparams, $param === null ? 'NULL' : "'" . self::$DB->real_escape_string($param) . "'"); } $sql = 'CALL ' . $store_procedure_name . '(' . implode(',', $quotedparams) . ');'; /* execute multi query */ if (!self::Query_Real($sql)) { return false; } $results = []; do { if (false != ($result = self::$DB->use_result())) { $rows = []; while (false != ($row = $result->fetch_assoc())) { $rows[] = $row; } $result->close(); $results[] = $rows; } } while (self::$DB->more_results() && self::$DB->next_result()); if (1 < count($results)) { return $results; } else { if (1 < count($results[0])) { return $results[0]; } else { if (1 < count($results[0][0])) { return $results[0][0]; } } } return array_shift($results[0][0]); }
protected function setConnectionCharset() { $result = $this->getWrappedConnection()->set_charset($this->charset); if (!$result) { throw new \Exception(sprintf('Impossible to set charset (%s): %S', $this->charset, \mysqli::$error)); } $this->connection->use_result(); }
/** * Create new databases. */ protected static function createDB() { // Setup DB $m = new \mysqli(ini_get('mysqli.default_host'), ini_get('mysqli.default_user') ?: 'root', ini_get('mysqli.default_pw')); if ($m->connect_error) { throw new \PHPUnit_Framework_SkippedTestError("Failed to connect to mysql: " . $m->connect_error); } $sql = file_get_contents(__DIR__ . '/../../support/db.sql'); if (!$m->multi_query($sql)) { throw new \PHPUnit_Framework_SkippedTestError("Failed to initialise DBs: " . $m->error); } // Make sure everything is executed do { $m->use_result(); } while ($m->more_results() && $m->next_result()); self::$reuse_db = true; }
if (!$error) { $mysqli = new mysqli($db_host, $db_user, $db_pass, $db_name); if (mysqli_connect_errno()) { echo "<p class=\"error\">Database connection failed due to " . mysqli_connect_error() . "</p>"; $error = true; } } // ********************************************************* // Show actual Heat pump state // Status 0: AT Off, 3: Heizung, 8: Warmwasser, 7: Solevorlauf, 6: Pause, 12: ??? // ********************************************************* echo "<h2>Wärmepumpe: Status.</h2>\n"; //$query = "SELECT CONVERT_TZ(`datetime`,'GMT','MET') AS `Letzter Eintrag`, vwmon_wp_stat.description AS `Status`, amu_comp_h_sum AS `Kompressorlauf, Std.`, amu_comp_starts AS `Kompressor Starts`, mv_yield_sum AS `Ertrag, kWh`, mv_heat_press_press AS `Heizanlagendruck, bar`, mv_brine_press_press AS `Soledruck, bar`, mv_EI_current AS `Energieintegral, min°`, mv_at_temp_temp AS `Außentemperatur, °C`, cir2_at_off AS `Außenabschalttemperatur, °C`, mv_boiler_temp_temp AS `Warmwassertemperatur, °C`, ci_cir2_set_temp AS `Vorlaufsolltemperatur, °C`, mv_VF2_temp_temp AS `VF2-Sensor, °C`, cir2_rt_day AS `Raumsolltemperatur, °C`, cir2_rt_night AS `Absenktemperatur, °C` FROM `vwmon_history` INNER JOIN `vwmon_wp_stat` ON `vwmon_history`.amu_wp_stat=`vwmon_wp_stat`.id ORDER BY `datetime` DESC LIMIT 0,1"; $query = "SELECT @last_datetime := MAX(`datetime`) FROM `vwmon_history`;SELECT CONVERT_TZ(`datetime`,'GMT','MET') AS `Letzter Eintrag`, vwmon_wp_stat.description AS `Status`, amu_comp_h_sum AS `Kompressorlauf, Std.`, amu_comp_starts AS `Kompressor Starts`, mv_yield_sum AS `Ertrag, kWh`, mv_heat_press_press AS `Heizanlagendruck, bar`, mv_brine_press_press AS `Soledruck, bar`, mv_EI_current AS `Energieintegral, min°`, mv_at_temp_temp AS `Außentemperatur, °C`, cir2_at_off AS `Außenabschalttemperatur, °C`, mv_boiler_temp_temp AS `Warmwassertemperatur, °C`, ci_cir2_set_temp AS `Vorlaufsolltemperatur, °C`, mv_VF2_temp_temp AS `VF2-Sensor, °C`, cir2_rt_day AS `Raumsolltemperatur, °C`, cir2_rt_night AS `Absenktemperatur, °C` FROM `vwmon_history` INNER JOIN `vwmon_wp_stat` ON `vwmon_history`.amu_wp_stat=`vwmon_wp_stat`.id WHERE `datetime` = @last_datetime"; // Execute multi query and print the 2nd result set if ($mysqli->multi_query($query) && ($result = $mysqli->use_result())) { $result->close(); $mysqli->next_result(); if (($result = $mysqli->use_result()) && ($row = $result->fetch_assoc())) { echo "<div class=\"cell2cell\"><table class=\"details\">\n"; foreach ($row as $key => $value) { echo "<tr><td>{$key}</td><td>{$value}</td></tr>\n"; } echo "</table></div>\n"; $result->close(); } else { echo "<p class=\"error\">No heat pump data found.</p>\n"; } } else { echo "<p class=\"error\">Error using mysqli.</p>\n"; echo "<p class=\"error\">MySQL: " . $mysqli->error . "</p>\n";
echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error; } $res = $mysqli->query("SELECT id FROM test ORDER BY id ASC"); echo "Reverse order...\n"; for ($row_no = $res->num_rows - 1; $row_no >= 0; $row_no--) { $res->data_seek($row_no); $row = $res->fetch_assoc(); echo " id = " . $row['id'] . "\n"; } echo "Result set order...\n"; $res->data_seek(1); while ($row = $res->fetch_assoc()) { echo " id = " . $row['id'] . "\n"; } $mysqli->real_query("SELECT id FROM test ORDER BY id ASC"); $res = $mysqli->use_result(); echo "Result set order...\n"; while ($row = $res->fetch_assoc()) { echo " id = " . $row['id'] . "\n"; } if (!$mysqli->query("DROP TABLE IF EXISTS test") || !$mysqli->query("CREATE TABLE test(id INT, label CHAR(1))") || !$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'a')")) { echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error; } $res = $mysqli->query("SELECT id, label FROM test WHERE id = 1"); $row = $res->fetch_assoc(); printf("id = %s (%s)\n", $row['id'], gettype($row['id'])); printf("label = %s (%s)\n", $row['label'], gettype($row['label'])); /* Prepared statement, stage 1: prepare */ if (!($stmt = $mysqli->prepare("INSERT INTO test(id) VALUES (?)"))) { echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error; }
<?php //LOGOWANIE TaWuA8kf $haslo = $_GET['password']; $login = $_GET['name']; $servername = "db.zut.edu.pl"; $dbname = "gg36296"; $username = "******"; $password = "******"; // Create connection $conn = new mysqli($servername, $username, $password, $dbname); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } else { $sql = "SELECT name,password FROM accountdata WHERE name LIKE '{$login}' AND password LIKE '{$haslo}' "; $conn->real_query($sql); $res = $conn->use_result(); while ($row = $res->fetch_assoc()) { if ($login == $row['name'] && $haslo == $row['password']) { echo "xD"; } else { echo "login or password is incorrect"; } } }
<?php $IP = "localhost"; $USER = "******"; $PASS = "******"; $BD = "shop"; $mysqli = new mysqli($IP, $USER, $PASS, $BD); if ($mysqli->connect_errno) { echo "Falló la conexión con MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error; } $mysqli->real_query("SELECT * FROM books"); $resultado = $mysqli->use_result(); while ($fila = $resultado->fetch_assoc()) { echo "Id = " . $fila['book_id'] . "<br>"; echo "Titulo = " . $fila['title'] . "<br>"; echo "Autor = " . $fila['author'] . "<br>"; echo "Precio = " . $fila['price'] . "<br>"; echo "<br>"; }
/** * Created by PhpStorm. * User: Applexiang * Date: 2015/9/9 * Time: 15:50 * Fun: 面向对象获取多结果集 */ $mysqli = new mysqli('127.0.0.1', 'root', 'begin110', 'ifel'); $i = 1; $sp = "call sp_get_user_list({$i})"; $rss = array(); if ($mysqli->multi_query($sp)) { do { $rs = array(); if ($result = $mysqli->use_result()) { while ($row = $result->fetch_array(MYSQL_ASSOC)) { $rs[] = $row; } $result->close(); $rss[] = $rs; } } while ($mysqli->more_results() && $mysqli->next_result()); $mysqli->close(); } echo "<br>"; var_dump($rss[0]); echo "<br>"; var_dump($rss[1]); echo "<br>"; var_dump($rss[2]);
/** * Отчистка соединения с базой данных. * * Соединение с БД может быть рассинхронизоровано. * Наример, запрос вернул несколько наборов результов, а клиент забрал только * часть из них и пытается выполнить следующий запрос. В такой ситуции сервер возвращает * ошибку и ждет пока ползователь заберет все наборы результатов. * Для решение проблемы необходимо забрать все наборы с сервера, чем и занимается данный метод. * * DB::query() и DB::multiQuery() оставляют соединение с базой "чистым". * Метод будет полезен при использования объекта mysqli, полученного с помощью метода * {@link DB::getMysqli() DB::getMysqli()}. * * @param mixed $result если передан объект mysqli_result, то выполнится его закрытие; */ public static function clearResultset($result = null) { if (is_object($result)) { $result->free(); } while (self::$mysqli->more_results() && self::$mysqli->next_result()) { if ($result = self::$mysqli->use_result()) { $result->free(); } } }
/** * Executes the specified stored procedure. * * @param string $procedure The stored procedure to call * * @return array $data The data from the stored procedure or FALSE if an error occured **/ function executeStoredProcedure($procedure) { $mysqli = new mysqli(MYSQL_HOST, MYSQL_USER, MYSQL_PASSWORD, MYSQL_DATABASE, MYSQL_PORT); if (mysqli_connect_errno()) { // an error occured // printf("Connect failed: %s\n", mysqli_connect_error()); $this->error_msg = printf("An error occured while connecting to the database, please try again later %s\n", mysqli_connect_error()); return false; } # check that the stored procedure includes the word CALL if (substr(strtolower($procedure), 0, 4) != "call") { # error occured $this->error_msg = "The query for the stored procedure must include the CALL keyword"; return false; } // execute the stored procedure // returns true if the query was sucessful //check if the query was successful if ($mysqli->multi_query($procedure)) { // check if the result is an instance of the resultset class if ($result = $mysqli->store_result()) { // do nothing //free the resultset $result->close(); } //clear the other result(s) from buffer //loop through each result using the next_result() method while ($mysqli->next_result()) { //free each result. $result = $mysqli->use_result(); $result->close(); } $mysqli->close(); return true; } else { // error occured $this->error_msg = "An error occured while updating the database, please try again later " . $mysqli->error; # TODO: Log the query and error message $mysqli->close(); return false; } }
/** * Executes a query using a bulk handler. * * @param BulkHandler $bulkHandler The bulk handler. * @param string $query The SQL statement. * * @since 1.0.0 * @api */ public static function executeBulk($bulkHandler, $query) { self::realQuery($query); $bulkHandler->start(); $result = self::$mysqli->use_result(); while ($row = $result->fetch_assoc()) { $bulkHandler->row($row); } $result->free(); $bulkHandler->stop(); if (self::$mysqli->more_results()) { self::$mysqli->next_result(); } }
/** * Calls a Stored Procedure and returns the results as an array of rows. * @param mysqli $dbLink An open mysqli object. * @param string $procName The name of the procedure to call. * @param string $params The parameter string to be used * @return array An array of rows returned by the call. */ function c_mysqli_call(mysqli $dbLink, $procName, $params = "") { if (!$dbLink) { throw new Exception("The MySQLi connection is invalid."); } else { // Execute the SQL command. // The multy_query method is used here to get the buffered results, // so they can be freeded later to avoid the out of sync error. $sql = "CALL {$procName}({$params});"; $sqlSuccess = $dbLink->multi_query($sql); if ($sqlSuccess) { if ($dbLink->more_results()) { // Get the first buffered result set, the one with our data. $result = $dbLink->use_result(); $output = array(); // Put the rows into the outpu array while ($row = $result->fetch_assoc()) { $output[] = $row; } // Free the first result set. // If you forget this one, you will get the "out of sync" error. $result->free(); // Go through each remaining buffered result and free them as well. // This removes all extra result sets returned, clearing the way // for the next SQL command. while ($dbLink->more_results() && $dbLink->next_result()) { $extraResult = $dbLink->use_result(); if ($extraResult instanceof mysqli_result) { $extraResult->free(); } } return $output; } else { return false; } } else { throw new Exception("The call failed: " . $dbLink->error); } } }
<?php if ($_SERVER['REMOTE_ADDR'] != '127.0.0.1') { die(__('You must run this script from your localhost (to stop hackers)')); } if (isset($_POST['action']) && $_POST['action'] == 'convert') { $mysqli = new mysqli($_POST['hostname'], $_POST['username'], $_POST['password'], $_POST['database']); if ($mysqli->connect_error) { die(__('Connect Error') . ' (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error); } // { products $query = $mysqli->real_query('select * from wp_wpsc_product_list'); if (!($result = $mysqli->use_result())) { die(__('error retrieving products list')); } $products = array(); while ($row = $result->fetch_array()) { $products[] = array('id' => $row['id'], 'name' => $row['name'], 'product_type_id' => 1, 'image_default' => 0, 'enabled' => $row['active'], 'date_created' => $row['date_added'], 'data_fields' => json_encode(array(array('n' => 'description', 'v' => $row['description']), array('n' => 'additional_description', 'v' => $row['additional_description']))), 'images_directory' => '/products/product-images/' . $row['id'], 'online_store_fields' => json_encode(array('_price' => $row['price'], '_weight(kg)' => $row['weight'], '_vatfree' => $row['notax']))); } // } // { images $images = array(); $query2 = $mysqli->real_query('select * from wp_wpsc_product_images order by product_id,image_order'); if (!($result = $mysqli->use_result())) { die(__('error retrieving products list')); } while ($row = $result->fetch_array()) { if ($row['image_order'] == '') { $row['image_order'] = '_'; } $images[] = array($row['product_id'] . '/' . $row['image_order'] . $row['image'], $_POST['url'] . 'wp-content/uploads/wpsc/product_images/' . $row['image']);