/**
* 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']);
