public function safeinsert($table, $dataArray) { $field = ""; $safeparam = ""; $params = ""; $paramarr = []; if (!is_array($dataArray) || count($dataArray) <= 0) { $this->halt('没有要插入的数据'); return false; } $paramsnum = 0; while (list($key, $val) = each($dataArray)) { $nowtype = is_string($val) ? 's' : 'i'; $paramarr[] = $val; $field .= "{$key},"; $safeparam .= "?,"; $paramsnum++; $params .= $nowtype; //之过滤字符串,int字形不用过滤 } $field = substr($field, 0, -1); $safeparam = substr($safeparam, 0, -1); $sql = "insert into {$table}({$field}) values({$safeparam})"; $stmt = mysqli_stmt_init($this->link); mysqli_stmt_prepare($stmt, $sql); array_unshift($paramarr, $stmt, $params); //把资源句柄和字符类型插入数组前两位 //参数要传引用。具体见PHP手册mysqli_stmt_bind_param $parmlist = array(); foreach ($paramarr as $key => $value) { $parmlist[$key] =& $paramarr[$key]; } call_user_func_array("mysqli_stmt_bind_param", $parmlist); $result = mysqli_stmt_execute($stmt); $this->write_log("安全插入"); if (!$result) { return false; } return true; }
printf("[018] [%d] %s\n", mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt)); } if (!is_object($res = mysqli_stmt_get_result($stmt)) || 'mysqli_result' != get_class($res)) { printf("[019] Expecting object/mysqli_result got %s/%s, [%d] %s\n", gettype($res), $res, mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt)); } $id = $label = null; if (!mysqli_stmt_bind_result($stmt, $id, $label)) { printf("[020] [%d] %s\n", mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt)); } $row = mysqli_fetch_assoc($res); if (NULL !== $id || NULL !== $label) { printf("[021] Bound variables should not have been set\n"); } mysqli_free_result($res); mysqli_stmt_close($stmt); if (!($stmt = mysqli_stmt_init($link)) || !mysqli_stmt_prepare($stmt, "SELECT id, label FROM test ORDER BY id ASC LIMIT 2") || !mysqli_stmt_execute($stmt)) { printf("[022] [%d] %s\n", mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt)); } if (!is_object($res = mysqli_stmt_get_result($stmt)) || 'mysqli_result' != get_class($res)) { printf("[023] Expecting object/mysqli_result got %s/%s, [%d] %s\n", gettype($res), $res, mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt)); } if (!in_array($res->type, array(MYSQLI_STORE_RESULT, MYSQLI_USE_RESULT))) { printf("[024] Unknown result set type %s\n", $res->type); } if ($res->type !== MYSQLI_STORE_RESULT) { printf("[025] Expecting int/%d got %s/%s", MYSQLI_STORE_RESULT, gettype($res->type), $res->type); } mysqli_free_result($res); mysqli_stmt_close($stmt); mysqli_close($link); if (NULL !== ($res = mysqli_stmt_get_result($stmt))) {
public function insertItems(Items $items) { $con = self::openConnection(); $affected = 0; mysqli_begin_transaction($con); $stm = mysqli_stmt_init($con); $sql = "INSERT INTO product VALUES (?, ?, ?, ?, ?, ?, ?, ?)"; mysqli_stmt_prepare($stm, $sql); foreach ($items->getItems() as $item) { $code = $item->getCode(); $articul = $item->getArticul(); $name = $item->getName(); $bmuID = $item->getBasicMeasurementUnit() == null ? null : $item->getBasicMeasurementUnit()->getId(); $price = $item->getPrice(); $curID = $item->getCurrency() == null ? null : $item->getCurrency()->getId(); $muID = $item->getMeasurementUnit() == null ? null : $item->getMeasurementUnit()->getId(); $parent = $item->getParent() == null ? null : $item->getParent()->getCode(); mysqli_stmt_bind_param($stm, 'sssdddds', $code, $articul, $name, $bmuID, $price, $curID, $muID, $parent); mysqli_stmt_execute($stm); if (mysqli_affected_rows($con) == 1) { $affected++; } } if ($affected > 0) { mysqli_commit($con); } else { mysqli_rollback($con); } return $affected; }
function update_vote($image_id) { //get number of votes and update global $link; /*$result = mysqli_query($link, "SELECT `amount` FROM `votes_amount` WHERE `imageID`=".$image_id.";") or die(mysqli_error($link)); $amount = mysqli_fetch_assoc($result); $new_amount = $amount['amount']+1; mysqli_query($link, "UPDATE `votes_amount` SET `amount`=".$new_amount." WHERE `imageID`=".$image_id.";") or die(mysqli_error($link));*/ $stmt = mysqli_stmt_init($link); mysqli_stmt_prepare($stmt, "SELECT `amount` FROM `votes_amount` WHERE `imageID`=?;") or die(mysqli_error($link)); mysqli_stmt_bind_param($stmt, 'i', $image_id); mysqli_stmt_execute($stmt); $result = mysqli_stmt_get_result($stmt); mysqli_stmt_close($stmt); $amount = mysqli_fetch_assoc($result); $new_amount = $amount['amount'] + 1; $stmt = mysqli_prepare($link, "UPDATE `votes_amount` SET `amount`=" . $new_amount . " WHERE `imageID`=?;") or die(mysqli_error($link)); mysqli_stmt_bind_param($stmt, 'i', $image_id); mysqli_stmt_execute($stmt); mysqli_stmt_close($stmt); //return ajax data if (isset($_SESSION['id']) && !isset($_POST['action']) && !isset($_POST['votePic'])) { $data = array('new_amount' => $new_amount, 'imageID' => $image_id); } elseif (isset($_POST['action']) && $_POST['action'] == 'anonymous_voting') { //get another two images $result = mysqli_query($link, "SELECT * FROM `image` ORDER BY RAND() LIMIT 2;") or die(mysqli_error($link)); $data = array(); while ($row = mysqli_fetch_assoc($result)) { $data[] = $row; } } mysqli_close($link); return $data; }
public function insertItems(Items $items) { $con = self::openConnection(); $affected = 0; mysqli_begin_transaction($con); $stm = mysqli_stmt_init($con); $sql = "INSERT INTO category VALUES (?, ?, ?)"; mysqli_stmt_prepare($stm, $sql); foreach ($items->getItems() as $item) { $code = $item->getCode(); $name = $item->getName(); $parent = $item->getParent() == null ? null : $item->getParent()->getCode(); mysqli_stmt_bind_param($stm, 'sss', $code, $name, $parent); mysqli_stmt_execute($stm); if (mysqli_affected_rows($con) == 1) { $affected++; } } if ($affected > 0) { mysqli_commit($con); } else { mysqli_rollback($con); } return $affected; }
function bind_twice($link, $engine, $sql_type1, $sql_type2, $bind_type1, $bind_type2, $bind_value1, $bind_value2, $offset) { if (!mysqli_query($link, "DROP TABLE IF EXISTS test_mysqli_stmt_bind_param_type_juggling_table_1")) { printf("[%03d + 1] [%d] %s\n", $offset, mysqli_errno($link), mysqli_error($link)); return false; } mysqli_autocommit($link, true); $sql = sprintf("CREATE TABLE test_mysqli_stmt_bind_param_type_juggling_table_1(col1 %s, col2 %s) ENGINE=%s", $sql_type1, $sql_type2, $engine); if (!mysqli_query($link, $sql)) { printf("[%03d + 2] [%d] %s\n", $offset, mysqli_errno($link), mysqli_error($link)); return false; } if (!($stmt = mysqli_stmt_init($link))) { printf("[%03d + 3] [%d] %s\n", $offset, mysqli_errno($link), mysqli_error($link)); return false; } if (!mysqli_stmt_prepare($stmt, "INSERT INTO test_mysqli_stmt_bind_param_type_juggling_table_1(col1, col2) VALUES (?, ?)")) { printf("[%03d + 4] [%d] %s\n", $offset, mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt)); return false; } if (!mysqli_stmt_bind_param($stmt, $bind_type1 . $bind_type2, $bind_value1, $bind_value1)) { printf("[%03d + 5] [%d] %s\n", $offset, mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt)); return false; } if (!mysqli_stmt_execute($stmt)) { printf("[%03d + 6] [%d] %s\n", $offset, mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt)); return false; } if (!mysqli_stmt_bind_param($stmt, $bind_type1 . $bind_type2, $bind_value1, $bind_value2)) { printf("[%03d + 7] [%d] %s\n", $offset, mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt)); return false; } if (!mysqli_stmt_execute($stmt)) { printf("[%03d + 8] [%d] %s\n", $offset, mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt)); return false; } mysqli_stmt_close($stmt); if (!($res = mysqli_query($link, "SELECT col1, col2 FROM test_mysqli_stmt_bind_param_type_juggling_table_1"))) { printf("[%03d + 9] [%d] %s\n", $offset, mysqli_errno($link), mysqli_error($link)); return false; } if (2 !== ($tmp = mysqli_num_rows($res))) { printf("[%03d + 10] Expecting 2 rows, got %d rows [%d] %s\n", $offset, $tmp, mysqli_errno($link), mysqli_error($link)); } $row = mysqli_fetch_assoc($res); if ($row['col1'] != $bind_value1 || $row['col2'] != $bind_value1) { printf("[%03d + 11] Expecting col1 = %s, col2 = %s got col1 = %s, col2 = %s - [%d] %s\n", $offset, $bind_value1, $bind_value1, $row['col1'], $row['col2'], mysqli_errno($link), mysqli_error($link)); return false; } $row = mysqli_fetch_assoc($res); if ($row['col1'] != $bind_value1 || $row['col2'] != $bind_value2) { printf("[%03d + 12] Expecting col1 = %s, col2 = %s got col1 = %s, col2 = %s - [%d] %s\n", $offset, $bind_value1, $bind_value2, $row['col1'], $row['col2'], mysqli_errno($link), mysqli_error($link)); return false; } mysqli_free_result($res); return true; }
function mysqli_query_insert($type, $len, $runs, $host, $user, $passwd, $db, $port, $socket) { $errors = $times = array(); foreach ($runs as $k => $run) { $times['INSERT ' . $type . ' ' . $run . 'x = #rows overall'] = microtime(true); do { if (!($link = @mysqli_connect($host, $user, $passwd, $db, $port, $socket))) { $errors[] = sprintf("INSERT %s %dx = #rows connect failure (original code = %s)", $type, $run, $flag_original_code ? 'yes' : 'no'); break 2; } if (!mysqli_query($link, "DROP TABLE IF EXISTS test")) { $errors[] = sprintf("INSERT %s %dx = #rows drop table failure (original code = %s): [%d] %s", $type, $run, $flag_original_code ? 'yes' : 'no', mysqli_errno($link), mysqli_error($link)); break 2; } if (!mysqli_query($link, sprintf("CREATE TABLE test(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, label %s)", $type))) { $errors[] = sprintf("INSERT %s %dx = #rows create table failure (original code = %s): [%d] %s", $type, $run, $flag_original_code ? 'yes' : 'no', mysqli_errno($link), mysqli_error($link)); break 2; } $label = ''; for ($i = 0; $i < $len; $i++) { $label .= chr(mt_rand(65, 90)); } $start = microtime(true); if (!($stmt = mysqli_stmt_init($link))) { $error[] = sprintf("INSERT %s %dx = #rows mysqli_stmt_init() failed (original code = %s): [%d] %s", $type, $run, $flag_original_code ? 'yes' : 'no', mysqli_errno($link), mysqli_error($link)); break 2; } $ret = mysqli_stmt_prepare($stmt, "INSERT INTO test(id, label) VALUES (?, ?)"); $times['INSERT ' . $type . ' ' . $run . 'x = #rows stmt_init() + stmt_prepare()'] += microtime(true) - $start; if (!$ret) { $error[] = sprintf("INSERT %s %dx = #rows mysqli_stmt_init() failed (original code = %s): [%d] %s", $type, $run, $flag_original_code ? 'yes' : 'no', mysqli_errno($link), mysqli_error($link)); break 2; } $start = microtime(true); $ret = mysqli_stmt_bind_param($stmt, 'is', $i, $label); $times['INSERT ' . $type . ' ' . $run . 'x = #rows stmt_bind_param()'] += microtime(true) - $start; if (!$ret) { $error[] = sprintf("INSERT %s %dx = #rows mysqli_stmt_bind_param failed (original code = %s): [%d] %s", $type, $run, $flag_original_code ? 'yes' : 'no', mysqli_errno($link), mysqli_error($link)); break 2; } for ($i = 1; $i <= $run; $i++) { $start = microtime(true); $ret = mysqli_stmt_execute($stmt); $times['INSERT ' . $type . ' ' . $run . 'x = #rows stmt_execute()'] += microtime(true) - $start; if (!$ret) { $errors[] = sprintf("INSERT %s %dx = #rows stmt_execute failure (original code = %s): [%d] %s", $type, $run, $flag_original_code ? 'yes' : 'no', mysqli_errno($link), mysqli_error($link)); break 3; } } mysqli_stmt_close($stmt); mysqli_close($link); } while (false); $times['INSERT ' . $type . ' ' . $run . 'x = #rows overall'] = microtime(true) - $times['INSERT ' . $type . ' ' . $run . 'x = #rows overall']; } return array($errors, $times); }
function test_format($link, $format, $from, $order_by, $expected, $offset) { if (!($stmt = mysqli_stmt_init($link))) { printf("[%03d] Cannot create PS, [%d] %s\n", $offset, mysqli_errno($link), mysqli_error($link)); return false; } if ($order_by) { $sql = sprintf('SELECT %s AS _format FROM %s ORDER BY %s', $format, $from, $order_by); } else { $sql = sprintf('SELECT %s AS _format FROM %s', $format, $from); } if (!mysqli_stmt_prepare($stmt, $sql)) { printf("[%03d] Cannot prepare PS, [%d] %s\n", $offset + 1, mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt)); return false; } if (!mysqli_stmt_execute($stmt)) { printf("[%03d] Cannot execute PS, [%d] %s\n", $offset + 2, mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt)); return false; } if (!mysqli_stmt_store_result($stmt)) { printf("[%03d] Cannot store result set, [%d] %s\n", $offset + 3, mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt)); return false; } if (!is_array($expected)) { $result = null; if (!mysqli_stmt_bind_result($stmt, $result)) { printf("[%03d] Cannot bind result, [%d] %s\n", $offset + 4, mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt)); return false; } if (!mysqli_stmt_fetch($stmt)) { printf("[%03d] Cannot fetch result,, [%d] %s\n", $offset + 5, mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt)); return false; } if ($result !== $expected) { printf("[%03d] Expecting %s/%s got %s/%s with %s - %s.\n", $offset + 6, gettype($expected), $expected, gettype($result), $result, $format, $sql); } } else { $order_by_col = $result = null; if (!mysqli_stmt_bind_result($stmt, $order_by_col, $result)) { printf("[%03d] Cannot bind result, [%d] %s\n", $offset + 7, mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt)); return false; } reset($expected); while ((list($k, $v) = each($expected)) && mysqli_stmt_fetch($stmt)) { if ($result !== $v) { printf("[%03d] Row %d - expecting %s/%s got %s/%s [%s] with %s - %s.\n", $offset + 8, $k, gettype($v), $v, gettype($result), $result, $order_by_col, $format, $sql); } } } mysqli_stmt_free_result($stmt); mysqli_stmt_close($stmt); return true; }
function func_mysqli_stmt_bind_datatype($link, $engine, $bind_type, $sql_type, $bind_value, $offset, $alternative = null) { if (!mysqli_query($link, "DROP TABLE IF EXISTS test_mysqli_stmt_bind_param_table_1")) { printf("[%03d] [%d] %s\n", $offset, mysqli_errno($link), mysqli_error($link)); return false; } if (!mysqli_query($link, sprintf("CREATE TABLE test_mysqli_stmt_bind_param_table_1(id INT NOT NULL, label %s, PRIMARY KEY(id)) ENGINE = %s", $sql_type, $engine))) { // don't bail - it might be that the server does not support the data type return false; } if (!($stmt = mysqli_stmt_init($link))) { printf("[%03d] [%d] %s\n", $offset + 1, mysqli_errno($link), mysqli_error($link)); return false; } if (!mysqli_stmt_prepare($stmt, "INSERT INTO test_mysqli_stmt_bind_param_table_1(id, label) VALUE (?, ?)")) { printf("[%03d] [%d] %s\n", $offset + 2, mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt)); return false; } $id = 1; if (!mysqli_stmt_bind_param($stmt, "i" . $bind_type, $id, $bind_value)) { printf("[%03d] [%d] %s\n", $offset + 3, mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt)); return false; } if (!mysqli_stmt_execute($stmt)) { printf("[%03d] [%d] %s\n", $offset + 4, mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt)); return false; } mysqli_stmt_close($stmt); if (!($res = mysqli_query($link, "SELECT id, label FROM test_mysqli_stmt_bind_param_table_1"))) { printf("[%03d] [%d] %s\n", $offset + 5, mysqli_errno($link), mysqli_error($link)); return false; } if (!($row = mysqli_fetch_assoc($res))) { printf("[%03d] [%d] %s\n", $offset + 5, mysqli_errno($link), mysqli_error($link)); return false; } if ($alternative) { if ($row['id'] != $id || $row['label'] != $bind_value && $row['label'] != $alternative) { printf("[%03d] Testing '%s', '%s': expecting '%s'/'%s' (%s), got '%s'/'%s'\n", $offset + 6, $bind_type, $sql_type, $id, $bind_value, gettype($bind_value), $row['id'], $row['label']); return false; } } else { if ($row['id'] != $id || $row['label'] != $bind_value) { printf("[%03d] Testing '%s', '%s': expecting '%s'/'%s', got '%s'/'%s'\n", $offset + 6, $bind_type, $sql_type, $id, $bind_value, $row['id'], $row['label']); return false; } } mysqli_free_result($res); return true; }
/** * This function removes record with id $_GET[ID'] from the table Jobs */ function deleteJob() { global $db_conn; $redirectlocation = "index.php?action=jobs"; $stmt = mysqli_stmt_init($db_conn); global $db_conn; $sql = "DELETE FROM `jobs` WHERE `JobID` = ?"; if (!mysqli_stmt_prepare($stmt, $sql)) { print "Failed to prepare statement\n"; } else { mysqli_stmt_bind_param($stmt, "i", $_GET['id']); mysqli_execute($stmt); mysqli_stmt_close($stmt); } immediate_redirect_to($redirectlocation); // return to jobs }
function saveOrder($dt) { global $link, $basket; $goods = myBasket(); $stmt = mysqli_stmt_init($link); $sql = 'INSERT INTO orders(title, author, pubyear,price, quantity, orderid, datetime) VALUES(?,?,?,?,?,?,?)'; if (mysqli_stmt_prepare($stmt, $sql)) { return false; } foreach ($goods as $item) { mysqli_stmt_bind_param($stmt, 'ssiiisi', $item['title'], $item['author'], $item['pubyear'], $item['price'], $item['quantity'], $basket['orderid'], $dt); mysqli_stmt_execute($stmt); mysqli_stmt_close($stmt); setcookie('basket', "", time() - 3600); return true; } }
function db_query($sql, $bind = null) { $db = get_var('db'); $query = false; $stmt = mysqli_stmt_init($db); $sql = trim($sql); if (mysqli_stmt_prepare($stmt, $sql)) { if (!empty($bind)) { $types = ''; $values = array(); foreach ($bind as $key => &$value) { $value = stripslashes($value); if (is_numeric($value)) { $float = floatval($value); $types .= $float && intval($float) != $float ? 'd' : 'i'; } else { $types .= 's'; } $values[$key] =& $bind[$key]; } $params = array_merge(array($stmt, $types), $bind); call_user_func_array('mysqli_stmt_bind_param', $params); } if (mysqli_stmt_execute($stmt)) { if (preg_match('/^(SELECT|SHOW)/i', $sql)) { if (db_native_driver()) { $query = mysqli_stmt_get_result($stmt); mysqli_stmt_close($stmt); } else { return $stmt; } } else { $query = TRUE; mysqli_stmt_close($stmt); } } else { trigger_error(mysqli_stmt_error($stmt), E_USER_WARNING); } } else { trigger_error(mysqli_error($db), E_USER_WARNING); } return $query; }
function executeQuery($conn, $sql, array $parameters = []){ /*For matching the data type for binding*/ $typesTable = [ 'integer' => 'i', 'double' => 'd', 'string' => 's' ]; $type = ''; $stmt = mysqli_stmt_init($conn); if (!mysqli_stmt_prepare($stmt, $sql)){ raiseIssue('failed to prepare statement'); return false; } /*This bit should only run if any parameters are provided*/ if (!empty($parameters)){ foreach ($parameters as $parameter){ /*Look up the type from the types table */ $type .= $typesTable[gettype($parameter)]; } array_unshift($parameters, $stmt, $type); /*bit hacky because of call_user_func_array, it will not like $parameters by itself so it needs to be "passed in by reference" but calltime pass by reference is deprecated*/ $preparedParams = []; foreach ($parameters as $index => &$label){ $preparedParams[$index] = &$label; } call_user_func_array('mysqli_stmt_bind_param', $preparedParams); } mysqli_stmt_execute($stmt); /*Generating the result set for use. This gives you the column names as keys on each row*/ $result = mysqli_stmt_get_result($stmt); $resultSet = []; if(!$result){ return $resultSet; /*skips the result fetching if no results obtained*/} while ($row = mysqli_fetch_assoc($result)){ $resultSet[] = $row; } mysqli_stmt_close($stmt); return $resultSet; }
public function insertUnits(Units $units) { $con = self::openConnection(); $affected = 0; mysqli_begin_transaction($con); $stm = mysqli_stmt_init($con); $sql = "INSERT INTO currency (code) VALUE (?)"; mysqli_stmt_prepare($stm, $sql); foreach ($units->getUnits() as $unit) { $code = $unit->getCode(); mysqli_stmt_bind_param($stm, 's', $code); mysqli_stmt_execute($stm); if (mysqli_affected_rows($con) == 1) { $affected++; } } if ($affected > 0) { mysqli_commit($con); } else { mysqli_rollback($con); } return $affected; }
function add_login($user_id, $username, $passwd) { if (!($db_link = get_connection())) { return -1; } $sql = 'insert into login (id, username, passwd) values (?,?,?)'; $stmt = mysqli_stmt_init($db_link); if (mysqli_stmt_prepare($stmt, $sql)) { mysqli_stmt_bind_param($stmt, 'iss', $user_id, $username, $passwd); if (mysqli_stmt_execute($stmt)) { mysqli_stmt_close($stmt); mysqli_close($db_link); return 1; } else { mysqli_stmt_close($stmt); mysqli_close($db_link); return -2; } } else { mysqli_close($db_link); return -2; } }
public static function prepare($conn, $sqlElement, $params, $bind = true) { if ($conn && strlen($sqlElement) > 0) { $sql = mysqli_stmt_init($conn); mysqli_stmt_prepare($sql, (string) $sqlElement); if (!$bind) { return $sql; } if (is_array($params)) { $t = ""; $cnt = count($params); for ($i = 0; $i < $cnt; $i++) { $v = $params[$i]; if (is_string($v)) { $t .= "s"; } else { if (is_int($v)) { $t .= "i"; } else { if (is_float($v)) { $t .= "d"; } else { $t .= "b"; } } } $ar[] =& $params[$i]; } if ($t) { call_user_func_array('mysqli_stmt_bind_param', array_merge(array($sql, $t), $ar)); } } return $sql; } return false; }
} if (!mysqli_stmt_prepare($stmt, "SELECT id, label FROM test ORDER BY id LIMIT 2")) { printf("[005] [%d] %s\n", mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt)); } // FIXME - different versions return different values ?! if (NULL !== ($tmp = mysqli_stmt_fetch($stmt)) && false !== $tmp) { printf("[006] Expecting NULL or boolean/false, got %s/%s\n", gettype($tmp), $tmp); } if (!mysqli_stmt_execute($stmt)) { printf("[007] [%d] %s\n", mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt)); } if (true !== ($tmp = mysqli_stmt_fetch($stmt))) { printf("[008] NULL, got %s/%s\n", gettype($tmp), $tmp); } mysqli_stmt_close($stmt); if (!($stmt = mysqli_stmt_init($link))) { printf("[009] [%d] %s\n", mysqli_errno($link), mysqli_error($link)); } if (!mysqli_stmt_prepare($stmt, "SELECT id, label FROM test ORDER BY id LIMIT 2")) { printf("[010] [%d] %s\n", mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt)); } if (!mysqli_stmt_execute($stmt)) { printf("[011] [%d] %s\n", mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt)); } $id = NULL; $label = NULL; if (true !== ($tmp = mysqli_stmt_bind_result($stmt, $id, $label))) { printf("[012] Expecting boolean/true, got %s/%s\n", gettype($tmp), $tmp); } if (true !== ($tmp = mysqli_stmt_fetch($stmt))) { printf("[013] Expecting boolean/true, got %s/%s, [%d] %s\n", gettype($tmp), $tmp, mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt));
function func_mysqli_stmt_get_result($link, $engine, $bind_type, $sql_type, $bind_value, $offset, $type_hint = null) { if (!mysqli_query($link, "DROP TABLE IF EXISTS test_mysqli_stmt_get_result_types_table_1")) { printf("[%04d] [%d] %s\n", $offset, mysqli_errno($link), mysqli_error($link)); return false; } if (!mysqli_query($link, sprintf("CREATE TABLE test_mysqli_stmt_get_result_types_table_1(id INT, label %s, PRIMARY KEY(id)) ENGINE = %s", $sql_type, $engine))) { // don't bail - column type might not be supported by the server, ignore this return false; } if (!($stmt = mysqli_stmt_init($link))) { printf("[%04d] [%d] %s\n", $offset + 1, mysqli_errno($link), mysqli_error($link)); return false; } if (!mysqli_stmt_prepare($stmt, "INSERT INTO test_mysqli_stmt_get_result_types_table_1(id, label) VALUES (?, ?)")) { printf("[%04d] [%d] %s\n", $offset + 2, mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt)); return false; } $id = null; if (!mysqli_stmt_bind_param($stmt, "i" . $bind_type, $id, $bind_value)) { printf("[%04d] [%d] %s\n", $offset + 3, mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt)); mysqli_stmt_close($stmt); return false; } for ($id = 1; $id < 4; $id++) { if (!mysqli_stmt_execute($stmt)) { printf("[%04d] [%d] %s\n", $offset + 3 + $id, mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt)); mysqli_stmt_close($stmt); return false; } } mysqli_stmt_close($stmt); $stmt = mysqli_stmt_init($link); if (!mysqli_stmt_prepare($stmt, "SELECT id, label FROM test_mysqli_stmt_get_result_types_table_1")) { printf("[%04d] [%d] %s\n", $offset + 7, mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt)); mysqli_stmt_close($stmt); return false; } if (!mysqli_stmt_execute($stmt)) { printf("[%04d] [%d] %s\n", $offset + 8, mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt)); mysqli_stmt_close($stmt); return false; } $result = mysqli_stmt_result_metadata($stmt); if (!($res = mysqli_stmt_get_result($stmt))) { printf("[%04d] [%d] %s\n", $offset + 9, mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt)); mysqli_stmt_close($stmt); return false; } $num = 0; $fields = mysqli_fetch_fields($result); while ($row = mysqli_fetch_assoc($res)) { $bind_res =& $row['label']; if (!gettype($bind_res) == 'unicode') { if ($bind_res !== $bind_value && (!$type_hint || $type_hint !== gettype($bind_res))) { printf("[%04d] [%d] Expecting %s/'%s' [type hint = %s], got %s/'%s'\n", $offset + 10, $num, gettype($bind_value), $bind_value, $type_hint, gettype($bind_res), $bind_res); mysqli_free_result($res); mysqli_stmt_close($stmt); return false; } } $num++; } if ($num != 3) { printf("[%04d] [%d] %s, expecting 3 results, got only %d results\n", $offset + 11, mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt), $num); mysqli_free_result($res); mysqli_stmt_close($stmt); return false; } mysqli_free_result($res); mysqli_stmt_close($stmt); return true; }
<?php require_once "connect.inc"; $tmp = NULL; $link = NULL; if (!is_null($tmp = @mysqli_stmt_field_count())) { printf("[001] Expecting NULL, got %s/%s\n", gettype($tmp), $tmp); } if (!is_null($tmp = @mysqli_stmt_field_count($link))) { printf("[002] Expecting NULL, got %s/%s\n", gettype($tmp), $tmp); } require 'table.inc'; $stmt = mysqli_stmt_init($link); if (!is_null($tmp = mysqli_stmt_field_count($stmt))) { printf("[003] Expecting NULL, got %s/%s\n", gettype($tmp), $tmp); } if (mysqli_stmt_prepare($stmt, '')) { printf("[004] Prepare should fail for an empty statement\n"); } if (!is_null($tmp = mysqli_stmt_field_count($stmt))) { printf("[005] Expecting NULL, got %s/%s\n", gettype($tmp), $tmp); } if (!mysqli_stmt_prepare($stmt, 'SELECT 1')) { printf("[006] [%d] %s\n", mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt)); } if (1 !== ($tmp = mysqli_stmt_field_count($stmt))) { printf("[007] Expecting int/1, got %s/%s\n", gettype($tmp), $tmp); } if (!mysqli_stmt_prepare($stmt, 'SELECT 1, 2')) { printf("[008] [%d] %s\n", mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt)); }
function func_mysqli_stmt_get_result_geom($link, $engine, $sql_type, $bind_value, $offset) { if (!mysqli_query($link, "DROP TABLE IF EXISTS test_mysqli_stmt_get_result_geom_table_1")) { printf("[%04d] [%d] %s\n", $offset, mysqli_errno($link), mysqli_error($link)); return false; } if (!mysqli_query($link, sprintf("CREATE TABLE test_mysqli_stmt_get_result_geom_table_1(id INT, label %s, PRIMARY KEY(id)) ENGINE = %s", $sql_type, $engine))) { // don't bail - column type might not be supported by the server, ignore this return false; } for ($id = 1; $id < 4; $id++) { $sql = sprintf("INSERT INTO test_mysqli_stmt_get_result_geom_table_1(id, label) VALUES (%d, %s)", $id, $bind_value); if (!mysqli_query($link, $sql)) { printf("[%04d] [%d] %s\n", $offset + 2 + $id, mysqli_errno($link), mysqli_error($link)); } } if (!($stmt = mysqli_stmt_init($link))) { printf("[%04d] [%d] %s\n", $offset + 6, mysqli_errno($link), mysqli_error($link)); return false; } if (!mysqli_stmt_prepare($stmt, "SELECT id, label FROM test_mysqli_stmt_get_result_geom_table_1")) { printf("[%04d] [%d] %s\n", $offset + 7, mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt)); mysqli_stmt_close($stmt); return false; } if (!mysqli_stmt_execute($stmt)) { printf("[%04d] [%d] %s\n", $offset + 8, mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt)); mysqli_stmt_close($stmt); return false; } if (!($res = mysqli_stmt_get_result($stmt))) { printf("[%04d] [%d] %s\n", $offset + 9, mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt)); mysqli_stmt_close($stmt); return false; } $result = mysqli_stmt_result_metadata($stmt); $fields = mysqli_fetch_fields($result); if ($fields[1]->type != MYSQLI_TYPE_GEOMETRY) { printf("[%04d] [%d] %s wrong type %d\n", $offset + 10, mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt), $fields[1]->type); } $num = 0; while ($row = mysqli_fetch_assoc($res)) { $bind_res =& $row['label']; if (!($stmt2 = mysqli_stmt_init($link))) { printf("[%04d] [%d] %s\n", $offset + 11, mysqli_errno($link), mysqli_error($link)); return false; } if (!mysqli_stmt_prepare($stmt2, "INSERT INTO test_mysqli_stmt_get_result_geom_table_1(id, label) VALUES (?, ?)")) { printf("[%04d] [%d] %s\n", $offset + 12, mysqli_stmt_errno($stmt2), mysqli_stmt_error($stmt2)); return false; } $id = $row['id'] + 10; if (!mysqli_stmt_bind_param($stmt2, "is", $id, $bind_res)) { printf("[%04d] [%d] %s\n", $offset + 13, mysqli_stmt_errno($stmt2), mysqli_stmt_error($stmt2)); return false; } if (!mysqli_stmt_execute($stmt2)) { printf("[%04d] [%d] %s\n", $offset + 14, mysqli_stmt_errno($stmt2), mysqli_stmt_error($stmt2)); return false; } mysqli_stmt_close($stmt2); if (!($res_normal = mysqli_query($link, sprintf("SELECT id, label FROM test_mysqli_stmt_get_result_geom_table_1 WHERE id = %d", $row['id'] + 10)))) { printf("[%04d] [%d] %s\n", $offset + 15, mysqli_errno($link), mysqli_error($link)); return false; } if (!($row_normal = mysqli_fetch_assoc($res_normal))) { printf("[%04d] [%d] %s\n", $offset + 16, mysqli_errno($link), mysqli_error($link)); return false; } if ($row_normal['label'] != $bind_res) { printf("[%04d] PS and non-PS return different data.\n", $offset + 17); return false; } mysqli_free_result($res_normal); $num++; } if ($num != 3) { printf("[%04d] [%d] %s, expecting 3 results, got only %d results\n", $offset + 18, mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt), $num); mysqli_free_result($res); mysqli_stmt_close($stmt); return false; } mysqli_free_result($res); mysqli_stmt_close($stmt); return true; }
function addUser($login, $password, $name, $country, $email) { global $link; $solt = md5(time()); $passCrypt = passEncrypt($password); $stmt = mysqli_stmt_init($link); $query = "INSERT INTO datareg (login, password, name, country, email, SOLT) VALUES (?,?,?,?,?,?)"; mysqli_stmt_prepare($stmt, $query); mysqli_stmt_bind_param($stmt, "ssssss", $login, $passCrypt, $name, $country, $email, $solt); mysqli_stmt_execute($stmt); mysqli_stmt_close($stmt); return true; }
function query($sql) { $args = func_get_args(); assert(!empty($args)); $sql = array_shift($args); if (empty($this->Handle)) { $this->connect(); } $this->startTimer(); $stmt = mysqli_stmt_init($this->Handle); $stmt->prepare($sql) or trigger_error(__CLASS__ . "::" . __FUNCTION__ . "() mysqli_prepare() failed: " . mysqli_error($this->Handle), E_USER_ERROR); if (!empty($args)) { call_user_func_array(array($stmt, 'bind_param'), array_merge(array(array_reduce($args, array($this, '_reduceBindTypes'))), $args)); } $stmt->execute(); $r = array(); $rowBindVars = array(); $rProto = array(); foreach (mysqli_fetch_fields($stmt->result_metadata()) as $f) { $rProto[$f->name] = 0xbadf00d; $rowBindVars[$f->name] =& $rProto[$f->name]; } call_user_func_array(array($stmt, 'bind_result'), $rowBindVars); while ($stmt->fetch()) { $ra = array(); foreach ($rowBindVars as $k => $v) { $ra[$k] = $v; } $r[] = $ra; } unset($stmt); $this->stopTimer($sql); return $r; }
<?php ignore_user_abort(true); checkArgs(array('check1', 'check2', 'check3', 'check4', 'check5', 'drop1', 'drop2', 'day', 'notes')); //Prepare variables for ($i = 1; $i <= 5; $i++) { $_POST['check' . $i] == 'true' ? $_POST['check' . $i] = 1 : ($_POST['check' . $i] = 0); } $_POST['notes'] = $_POST['notes'] == '' ? 'None' : $_POST['notes']; //urlDecode these $_POST['day'] = urldecode($_POST['day']); $_POST['notes'] = urldecode($_POST['notes']); //Make sure old entry doesn't exist $query = "SELECT * FROM log WHERE day = ? AND owner = ?"; $stmt = mysqli_stmt_init($con); $stmt->prepare($query); $stmt->bind_param('ss', $_POST['day'], $_SESSION['email']); $stmt->execute(); $resultSql = $stmt->get_result(); if ($resultSql->num_rows != 0) { echo json_encode(array('type' => 'danger', 'msg' => 'A mood has already been set for that hour.')); die; } //Enter new entry $query = "INSERT INTO log (day, notes, check1, check2, check3, check4, check5, drop1, drop2, owner) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?);"; $stmt = mysqli_stmt_init($con); $stmt->prepare($query); $stmt->bind_param('ssiiiiiiis', $_POST['day'], $_POST['notes'], $_POST['check1'], $_POST['check2'], $_POST['check3'], $_POST['check4'], $_POST['check5'], $_POST['drop1'], $_POST['drop2'], $_SESSION['email']); $stmt->execute(); $result = array('type' => 'success', 'msg' => 'Successfully updated log!'); echo json_encode($result);
printf("[003 - %d] [%d] %s\n", $bits, mysqli_errno($link_ins), mysqli_error($link_ins)); } if (!mysqli_query($link_ins, sprintf("CREATE TABLE test(id BIGINT, bit_value BIT(%d) NOT NULL, bit_null BIT(%d) DEFAULT NULL) ENGINE = %s", $bits, $bits, $engine))) { // don't bail - column type might not be supported by the server, ignore this continue; } if (!($stmt_ins = mysqli_stmt_init($link_ins))) { printf("[004 - %d] [%d] %s\n", $bits, mysqli_errno($link_ins), mysqli_error($link_ins)); continue; } if (!mysqli_stmt_prepare($stmt_ins, "INSERT INTO test(id, bit_value) VALUES (?, ?)")) { printf("[005 - %d] [%d] %s\n", $bits, mysqli_stmt_errno($stmt_ins), mysqli_stmt_error($stmt_ins)); mysqli_stmt_close($stmt_ins); continue; } if (!($stmt_sel = mysqli_stmt_init($link_sel))) { printf("[006 - %d] [%d] %s\n", $bits, mysqli_errno($link_sel), mysqli_error($link_sel)); mysqli_stmt_close($stmt_ins); continue; } $tests = 0; $rand_max = mt_getrandmax(); while ($tests < 10) { $tests++; if (1 == $tests) { $value = 0; } else { if (2 == $tests) { $value = $max_value; } else { if ($max_value > $rand_max) {
function delShareS($shareKey, $con, $userId) { $stmt = mysqli_stmt_init($con); mysqli_stmt_prepare($stmt, 'SELECT cuser FROM sd_sskey WHERE sskey=?'); mysqli_stmt_bind_param($stmt, "s", $shareKey); mysqli_stmt_execute($stmt); $results = mysqli_stmt_bind_result($stmt, $upuser); while (mysqli_stmt_fetch($stmt)) { if ($userId != $upuser || empty($upuser)) { return 'bad.无权'; exit; } $deleteAction = "delete from sd_sskey where sskey = '{$shareKey}'"; mysqli_query($con, $deleteAction); return "ok.删除成功"; } }
function testStatement($offset, $link, $sql, $expected_lib, $expected_mysqlnd, $check_mysqlnd, $compare) { if (!($stmt = mysqli_stmt_init($link))) { printf("[%04d - %s] [%d] %s\n", $offset, $sql, mysqli_errno($link), mysqli_error($link)); return false; } if (!@mysqli_stmt_prepare($stmt, $sql)) { /* Not all server versions will support all statements */ /* Failing to prepare is OK */ return true; } if (empty($expected_lib) && false !== $res) { printf("[%04d - %s] No metadata expected\n", $offset + 1, $sql); return false; } else { if (!empty($expected_lib) && false == $res) { printf("[%04d - %s] Metadata expected\n", $offset + 2, $sql); return false; } } if (!empty($expected_lib)) { if (!is_object($res)) { printf("[%04d - %s] [%d] %s\n", $offset + 3, $sql, mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt)); return false; } if (get_class($res) != 'mysqli_result') { printf("[%04d - %s] Expecting object/mysqli_result got object/%s\n", $offset + 4, $sql, get_class($res)); return false; } $meta = array('num_fields' => mysqli_num_fields($res), 'fetch_field' => mysqli_fetch_field($res), 'fetch_field_direct0' => mysqli_fetch_field_direct($res, 0), 'fetch_field_direct1' => @mysqli_fetch_field_direct($res, 1), 'fetch_fields' => count(mysqli_fetch_fields($res)), 'field_count' => $res->field_count, 'field_seek-1' => @mysqli_field_seek($res, -1), 'field_seek0' => mysqli_field_seek($res, 0), 'field_tell' => mysqli_field_tell($res)); if (is_object($meta['fetch_field'])) { $meta['fetch_field']->charsetnr = 'ignore'; $meta['fetch_field']->flags = 'ignore'; } if (is_object($meta['fetch_field_direct0'])) { $meta['fetch_field_direct0']->charsetnr = 'ignore'; $meta['fetch_field_direct0']->flags = 'ignore'; } if (is_object($meta['fetch_field_direct1'])) { $meta['fetch_field_direct1']->charsetnr = 'ignore'; $meta['fetch_field_direct1']->flags = 'ignore'; } mysqli_free_result($res); if ($meta != $expected_lib) { printf("[%04d - %s] Metadata differs from expected values\n", $offset + 5, $sql); var_dump($meta); var_dump($expected_lib); return false; } } if (function_exists('mysqli_stmt_get_result')) { /* mysqlnd only */ if (!mysqli_stmt_execute($stmt)) { printf("[%04d - %s] [%d] %s\n", $offset + 6, $sql, mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt)); return false; } $res = mysqli_stmt_get_result($stmt); if (false === $res && !empty($expected_mysqlnd)) { printf("[%04d - %s] Expecting resultset [%d] %s\n", $offset + 7, $sql, mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt)); return false; } else { if (empty($expected_mysqlnd) && false !== $res) { printf("[%04d - %s] Unexpected resultset [%d] %s\n", $offset + 8, $sql, mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt)); return false; } } if (!is_object($res)) { printf("[%04d - %s] [%d] %s\n", $offset + 9, $sql, mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt)); return false; } if ('mysqli_result' != get_class($res)) { printf("[%04d - %s] Expecting object/mysqli_result got object/%s\n", $offset + 10, $sql, get_class($res)); return false; } $meta_res = array('num_fields' => mysqli_num_fields($res), 'fetch_field' => mysqli_fetch_field($res), 'fetch_field_direct0' => mysqli_fetch_field_direct($res, 0), 'fetch_field_direct1' => @mysqli_fetch_field_direct($res, 1), 'fetch_fields' => count(mysqli_fetch_fields($res)), 'field_count' => mysqli_field_count($link), 'field_seek-1' => @mysqli_field_seek($res, -1), 'field_seek0' => mysqli_field_seek($res, 0), 'field_tell' => mysqli_field_tell($res)); if (is_object($meta_res['fetch_field'])) { $meta_res['fetch_field']->charsetnr = 'ignore'; $meta_res['fetch_field']->flags = 'ignore'; } if (is_object($meta_res['fetch_field_direct0'])) { $meta_res['fetch_field_direct0']->charsetnr = 'ignore'; $meta_res['fetch_field_direct0']->flags = 'ignore'; } if (is_object($meta_res['fetch_field_direct1'])) { $meta_res['fetch_field_direct1']->charsetnr = 'ignore'; $meta_res['fetch_field_direct1']->flags = 'ignore'; } mysqli_free_result($res); if ($check_mysqlnd && $meta_res != $expected_mysqlnd) { printf("[%04d - %s] Metadata differs from expected\n", $offset + 11, $sql); var_dump($meta_res); var_dump($expected_mysqlnd); } else { if ($meta_res['field_count'] < 1) { printf("[%04d - %s] Metadata seems wrong, no fields?\n", $offset + 12, $sql); var_dump($meta_res); var_dump(mysqli_fetch_assoc($res)); } } if ($compare && $meta_res != $meta) { printf("[%04d - %s] Metadata returned by mysqli_stmt_result_metadata() and mysqli_stmt_get_result() differ\n", $offset + 13, $sql); var_dump($meta_res); var_dump($meta); } } mysqli_stmt_close($stmt); return true; }
if (!mysqli_stmt_execute($stmt)) { printf("[003] [%d] %s\n", mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt)); } if (!is_object($res = mysqli_stmt_get_result($stmt)) || 'mysqli_result' != get_class($res)) { printf("[004] Expecting object/mysqli_result got %s/%s, [%d] %s\n", gettype($res), $res, mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt)); } if (!is_object($res_meta = mysqli_stmt_result_metadata($stmt)) || 'mysqli_result' != get_class($res_meta)) { printf("[005] Expecting object/mysqli_result got %s/%s, [%d] %s\n", gettype($res), $res, mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt)); } var_dump(mysqli_fetch_assoc($res)); var_dump(mysqli_fetch_assoc($res_meta)); mysqli_free_result($res); mysqli_free_result($res_meta); mysqli_stmt_close($stmt); // !mysqli_stmt_prepare($stmt, "SELECT id, label, id + 1 as _id, concat(label, '_') _label FROM test as _test ORDER BY id ASC LIMIT 3") || if (!($stmt = mysqli_stmt_init($link)) || !mysqli_stmt_prepare($stmt, "SELECT id , label, id + 1 AS _id, label AS _label, null AS _null, CONCAT(label, '_') _label_concat FROM test _test ORDER BY id ASC LIMIT 3") || !mysqli_stmt_execute($stmt)) { printf("[006] [%d] %s\n", mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt)); } if (!is_object($res = mysqli_stmt_get_result($stmt)) || 'mysqli_result' != get_class($res)) { printf("[007] Expecting object/mysqli_result got %s/%s, [%d] %s\n", gettype($res), $res, mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt)); } if (!is_object($res_meta = mysqli_stmt_result_metadata($stmt)) || 'mysqli_result' != get_class($res_meta)) { printf("[008] Expecting object/mysqli_result got %s/%s, [%d] %s\n", gettype($res), $res, mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt)); } if (($tmp1 = mysqli_num_fields($res)) !== ($tmp2 = mysqli_num_fields($res_meta))) { printf("[009] %s/%s !== %s/%s\n", gettype($tmp1), $tmp1, gettype($tmp2), $tmp2); } /* if (($tmp1 = mysqli_field_count($link)) !== ($tmp2 = $res->field_count())) printf("[010] %s/%s !== %s/%s\n", gettype($tmp1), $tmp1, gettype($tmp2), $tmp2);
function generateView($readOnly, $user) { global $con; //Get names $query = "SELECT drop1, drop2, check1, check2, check3, check4, check5, opt1, opt2 FROM users WHERE email = ?"; $stmt = mysqli_stmt_init($con); $stmt->prepare($query); $stmt->bind_param('s', $user); $stmt->execute(); $resultSql = $stmt->get_result(); $names = mysqli_fetch_assoc($resultSql); //Get log $query = "SELECT * FROM log WHERE owner = ? ORDER BY day DESC"; $stmt = mysqli_stmt_init($con); $stmt->prepare($query); $stmt->bind_param('s', $user); $stmt->execute(); $resultSql = $stmt->get_result(); //Create worriedNumToText $unk = array('Unknown'); $exploded = explode('|', $names['opt2']); $worriedNumToText = array_merge($unk, $exploded); ?> <div style="padding-left: 1%; padding-right: 1%;" id="graphPanel"> <div class="panel panel-primary"> <div class="panel-heading">Mood Log <?php $readOnly ? '(read-only)' : ''; ?> </div> <div class="panel-body"> <div> <div class="col-md-2"> <button class="btn btn-primary" onclick="graphLim();">Graph last X records</button> </div> <div class="col-md-1"> <input class="form-control" id="count" type="number" value="<?php echo isset($_GET['count']) && is_numeric($_GET['count']) ? $_GET['count'] : '20'; ?> "></input> </div> </div> <br> <br> <div class="col-md-1"> <a class="btn btn-primary" onclick="graphAvg();">Graph per-day average</a><br> </div> <br> <br> <div> <div class="col-md-2"> <button class="btn btn-primary" onclick="graphDate();">Graph between two days</button> </div> <div class="col-md-2"> <input class="form-control" id="d1" type="text" value="<?php echo isset($_GET['d1']) ? $_GET['d1'] : date('Y-m-d'); ?> "></input> </div> <div class="col-md-2"> <input class="form-control" id="d2" type="text" value="<?php echo isset($_GET['d2']) ? $_GET['d2'] : date('Y-m-d'); ?> "></input> </div> Year-month-date </div> <br> <div id="graphDiv"> <canvas id="graph" width="1000" height="500"></canvas> </div> <?php if ($user == '*****@*****.**') { ?> <?php //This is done via ajax so it can be in the session if (isset($_SESSION['showAll']) && $_SESSION['showAll'] == 1) { ?> <span>Graphing unimportant data.</span> <a onclick="request('showAll.php', {}, 'POST');">Click here to not graph unimportant data</a> <?php } else { ?> <span>Not graphing unimportant data.</span> <a onclick="request('showAll.php', {}, 'POST');">Click here to graph all data</a> <?php } ?> <br> <span data-useless="true">Showing unimportant data.</span> <span data-useless="true" style="display:none;">Not showing unimportant data.</span> <a onclick="$('[data-useless=true]').toggle();">Toggle hiding unimportant data</a> <?php } ?> <div class="table-responsive" id="logTable"> <table class="table table-hover"> <thead> <tr> <th class="col-xs-1">Date</th> <th class="col-xs-1"><?php echo $names['drop1']; ?> </th> <th class="col-xs-1"><?php echo $names['drop2']; ?> </th> <th class="col-xs-1"><?php echo $names['check1']; ?> </th> <th class="col-xs-1"><?php echo $names['check2']; ?> </th> <th class="col-xs-1"><?php echo $names['check3']; ?> </th> <th class="col-xs-1"><?php echo $names['check5']; ?> </th> <th class="col-xs-4" id="notesTH">Notes - <a data-showing-now="false" onclick="showNotes(this);">Show</a></th> <?php if (!$readOnly) { ?> <th class="col-xs-1">Functions</th> <?php } ?> </tr> </thead> <tbody> <?php mysqli_data_seek($resultSql, 0); while ($row = mysqli_fetch_assoc($resultSql)) { $formatted = date('M d - h A', strtotime($row['day'])); ?> <tr data-drop1="<?php echo $row['drop1']; ?> " data-drop2="<?php echo $row['drop2']; ?> " data-check1="<?php echo $row['check1']; ?> " data-check2="<?php echo $row['check2']; ?> " data-check3="<?php echo $row['check3']; ?> " data-check4="<?php echo $row['check4']; ?> " data-check5="<?php echo $row['check5']; ?> " data-notes="<?php echo $row['notes']; ?> " data-day="<?php echo $row['day']; ?> " data-currentTime="<?php echo $row['currentTime']; ?> " <?php if ($user == '*****@*****.**') { if ($row['check4'] == 1) { echo 'style="color: #bdbdbd"'; } echo ' data-useless="' . ($row['drop1'] == 5 && $row['drop2'] == 1 && $row['check1'] == 0 && $row['check2'] == 0 && $row['check3'] == 0 && $row['check4'] == 1 && $row['check5'] == 0 ? 'true' : 'false') . '"'; } else { echo 'data-useless="false"'; } ?> > <!-- This ends the tr block --> <td><?php echo $formatted; ?> </td> <td><?php echo $row['drop1'] == 0 ? 'Unknown' : $row['drop1']; ?> </td> <td><?php echo $worriedNumToText[$row['drop2']]; ?> </td> <td><?php echo $row['check1'] == 1 ? 'Yes' : 'No'; ?> </td> <td><?php echo $row['check2'] == 1 ? 'Yes' : 'No'; ?> </td> <td><?php echo $row['check3'] == 1 ? 'Yes' : 'No'; ?> </td> <td><?php echo $row['check5'] == 1 ? 'Yes' : 'No'; ?> </td> <td class="notes">Hidden</td> <?php if (!$readOnly) { ?> <td><a href="#fullNavbar" onclick="modify(this);">Modify</a>/<a onclick="del(this);" data-onconfirm="false" style="color:red;">Delete</a></td> <?php } ?> </tr> <?php } ?> </tbody> </table> </div> </div> </div> </div> <script type="text/javascript"> // http://stackoverflow.com/a/13317303/1524950 function getCurentFileName(){ var pagePathName= window.location.pathname; return pagePathName.substring(pagePathName.lastIndexOf("/") + 1); } var graphPanelAnchor = ''; if (getCurentFileName() == 'view.php') { graphPanelAnchor = '#graphPanel'; } function graphLim() { window.location.href = 'https://frankie.salmick.com/mood/' + getCurentFileName() + '?type=lim&count=' + encodeURIComponent($("#count").val()) + graphPanelAnchor; } function graphDate() { window.location.href = 'https://frankie.salmick.com/mood/' + getCurentFileName() + '?type=dates&d1=' + encodeURIComponent($("#d1").val()) + "&d2=" + encodeURIComponent($("#d2").val()) + graphPanelAnchor; } function graphAvg() { window.location.href = 'https://frankie.salmick.com/mood/' + getCurentFileName() + '?type=avg' + graphPanelAnchor; } <?php if (1 == 2 && $_SESSION['email'] == '*****@*****.**') { ?> function graphRaw() { window.location.href = 'https://frankie.salmick.com/mood/' + getCurentFileName() + '?type=raw&query=' + encodeURIComponent($("#query").val()) + graphPanelAnchor; } <?php } ?> //Graph data var data = { labels: [ <?php //Defaults $type = 'lim'; $myIgnoreBit = ' '; if ($user == '*****@*****.**' && !(isset($_SESSION['showAll']) && $_SESSION['showAll'] == 1)) { $myIgnoreBit = " AND (drop1 <> 5 OR drop2 <> 1 OR check1 <> 0 OR check2 <> 0 OR check3 <> 0 OR check4 <> 1 OR check5 <> 0) "; } $count = 20; if (isset($_GET['type'])) { $type = $_GET['type']; } if (isset($_GET['count']) && is_numeric($_GET['count'])) { $count = $_GET['count']; } if ($type == 'avg') { $query = "SELECT day, AVG(NULLIF(drop1, 0)) AS drop1, AVG(NULLIF(drop2, 0)) AS drop2 FROM log WHERE owner = ?" . $myIgnoreBit . "GROUP BY DATE(DATE_SUB(day, INTERVAL 4 HOUR))"; } else { if ($type == 'dates') { $query = "SELECT * FROM log WHERE day >= ? AND day <= ? AND owner = ?" . $myIgnoreBit . "ORDER BY day DESC"; } else { if (1 == 2 && $type == 'raw' && $_SESSION['email'] == '*****@*****.**') { $query = urldecode($_GET['query']); } else { $query = "SELECT * FROM log WHERE owner = ?" . $myIgnoreBit . " ORDER BY day DESC LIMIT ?"; } } } $stmt = mysqli_stmt_init($con); $stmt->prepare($query); if ($type == 'avg') { $stmt->bind_param('s', $user); } else { if ($type == 'dates') { $d1 = $_GET['d1'] . ' 0'; $d2 = $_GET['d2'] . ' 24'; $stmt->bind_param('sss', $d1, $d2, $user); } else { if ($type != 'raw') { $stmt->bind_param('si', $user, $count); } } } $stmt->execute(); $resultSql = $stmt->get_result(); $first = true; $lastDay = ''; $flipped = array(); while ($row = mysqli_fetch_assoc($resultSql)) { $flipped[] = $row; } if ($type != 'avg') { $flipped = array_reverse($flipped, true); } foreach ($flipped as $row) { //Convert MySQL datetime to human-readable date $day = date('M d \'y', strtotime($row['day'])); if (!$first) { echo ', '; } //See if the day has changed (so we don't output the same day 6+ times) echo '"'; if ($lastDay != $day || $first) { echo $day . ' '; } if ($type != 'avg') { echo date('g A', strtotime($row['day'])); } echo '"'; //Set the lastDay to this day $lastDay = $day; $first = false; } ?> ], datasets: [ { label: "Overall", /* fillColor: "rgba(255, 148, 77, 0.4)", strokeColor: "rgba(255, 148, 77, 1)", pointColor: "rgba(255, 148, 77, 1)", pointStrokeColor: "#fff", pointHighlightFill: "#fff", pointHighlightStroke: "rgba(255, 148, 77, 1)", */ fillColor: "rgba(50, 255, 100, 0.2)", strokeColor: "rgba(50, 255, 100, 1)", pointColor: "rgba(50, 255, 100, 1)", pointStrokeColor: "#fff", pointHighlightFill: "#fff", pointHighlightStroke: "rgba(50, 255, 100, 1)", data: [ <?php mysqli_data_seek($resultSql, 0); $first = true; $prev = 6; $count = 0; foreach ($flipped as $row) { if (!$first) { echo ', '; } $first = false; echo '"'; if ($row['drop1'] == 0) { echo $prev; } else { echo $row['drop1']; $prev = $row['drop1']; } echo '"'; $count++; } ?> ] }, { label: "Worried", /* fillColor: "rgba(179, 0, 178,0.2)", strokeColor: "rgba(179, 0, 178,1)", pointColor: "rgba(179, 0, 178,1)", pointStrokeColor: "#fff", pointHighlightFill: "#fff", pointHighlightStroke: "rgba(179, 0, 178,1)", */ fillColor: "rgba(255, 0, 0, 0.1)", strokeColor: "rgba(255, 0, 0, 1)", pointColor: "rgba(255, 0, 0, 1)", pointStrokeColor: "#fff", pointHighlightFill: "#fff", pointHighlightStroke: "rgba(255, 0, 0, 1)", data: [ <?php mysqli_data_seek($resultSql, 0); $first = true; $prev = 0; foreach ($flipped as $row) { if (!$first) { echo ', '; } $first = false; if ($row['drop2'] == 0) { echo $prev; } else { echo $row['drop2'] - 1; $prev = $row['drop2'] - 1; } } ?> ] }, { label: "goalMin", fillColor: "rgba(151,187,205,0)", strokeColor: "rgba(0,0,0,0.15)", pointColor: "rgba(151,187,205,0)", pointStrokeColor: "rgba(0,0,0,0)", pointHighlightFill: "rgba(0,0,0,0)", pointHighlightStroke: "rgba(151,187,205,0)", data: [ <?php $first = true; for ($i = 0; $i < $count; $i++) { if (!$first) { echo ', '; } $first = false; echo '5'; } ?> ] } ] }; var options = { scaleOverride: true, scaleSteps: 9, scaleStepWidth: 1, scaleStartValue: 0, animation: false, bezierCurve: false, responsive: false }; $(document).ready(function() { // Get the context of the canvas element we want to select var ctx = document.getElementById("graph").getContext("2d"); var myNewChart = new Chart(ctx).Line(data, options); //$("#graphDiv").slideUp(0); }); </script> <?php //End function }
function execSQL($query, $params, $close) { global $error_message; global $conn; // LOG LOG_MSG('DEBUG', "execSQL(): START"); LOG_MSG('DEBUG', " QUERY=[" . $query . "]"); LOG_MSG('DEBUG', " PARAMS\n[" . print_r($params, true) . "]"); $log_query = preg_replace("/\t/", " ", $query); $log_query = preg_replace("/\n/", " ", $log_query); $log_query = preg_replace("/[\\s]+/", " ", $log_query); LOG_MSG('INFO', " QUERY=[{$log_query}] PARAMS=[" . implode("|", $params) . "]"); // Reset result set before starting $resp = array("STATUS" => "ERROR"); // For DMLs $resp[0]['STATUS'] = "ERROR"; // For Selects $error_message = "There was an error proccessing your request. Please check and try again"; // INIT STATEMENT if (!($stmt = mysqli_stmt_init($conn))) { LOG_MSG('ERROR', "execSQL(): Error initializing statement: [" . mysqli_errno($conn) . ": " . mysqli_error($conn) . "]. "); $resp['SQL_ERROR_CODE'] = mysqli_errno($conn); return $resp; } LOG_MSG('DEBUG', "execSQL():\t Init query"); // PREPARE if (!mysqli_stmt_prepare($stmt, $query)) { LOG_MSG('ERROR', "execSQL(): Error preparing statement: [" . mysqli_errno($conn) . ": " . mysqli_error($conn) . "]."); $resp['SQL_ERROR_CODE'] = mysqli_errno($conn); return $resp; } LOG_MSG('DEBUG', "execSQL():\t Prepared query"); // BIND PARAMS if (!empty($params)) { // Bind input params if (!call_user_func_array(array($stmt, 'bind_param'), refValues($params))) { LOG_MSG('ERROR', "execSQL(): Error binding input params: [" . mysqli_errno($conn) . ": " . mysqli_error($conn) . "]."); $resp['SQL_ERROR_CODE'] = mysqli_errno($conn); mysqli_stmt_close($stmt); // Close statement return $resp; } } LOG_MSG('DEBUG', "execSQL():\t Bound query parameters"); // EXECUTE $qry_exec_time = microtime(true); $status = mysqli_stmt_execute($stmt); $qry_exec_time = number_format(microtime(true) - $qry_exec_time, 4); if (!$status) { LOG_MSG('ERROR', "execSQL(): Error executing statement: [" . mysqli_errno($conn) . ": " . mysqli_error($conn) . "]."); $resp['SQL_ERROR_CODE'] = mysqli_errno($conn); mysqli_stmt_close($stmt); // Close statement return $resp; } LOG_MSG('INFO', " Executed query in {$qry_exec_time} secs"); // DMLs (insert/update/delete) // If CLOSE, then return no of rows affected if ($close) { unset($resp[0]); $error_message = ""; $resp["STATUS"] = "OK"; $resp["EXECUTE_STATUS"] = $status; $resp["NROWS"] = $conn->affected_rows; $resp["INSERT_ID"] = $conn->insert_id; mysqli_stmt_close($stmt); // Close statement LOG_MSG('INFO', " Status=[OK] Affected rows [" . $resp['NROWS'] . "]"); LOG_MSG('DEBUG', "execSQL(): UPDATE/INSERT response:\n[" . print_r($resp, true) . "]"); LOG_MSG('DEBUG', "execSQL(): END"); return $resp; } // SELECT $result_set = mysqli_stmt_result_metadata($stmt); while ($field = mysqli_fetch_field($result_set)) { $parameters[] =& $row[$field->name]; } // BIND OUTPUT if (!call_user_func_array(array($stmt, 'bind_result'), refValues($parameters))) { LOG_MSG('ERROR', "execSQL(): Error binding output params: [" . mysqli_errno($conn) . ": " . mysqli_error($conn) . "]."); $resp[0]['SQL_ERROR_CODE'] = mysqli_errno($conn); mysqli_free_result($result_set); // Close result set mysqli_stmt_close($stmt); // Close statement return $resp; } LOG_MSG('DEBUG', "execSQL():\t Bound output parameters"); // FETCH DATA $i = 0; while (mysqli_stmt_fetch($stmt)) { $x = array(); foreach ($row as $key => $val) { $x[$key] = $val; } $results[] = $x; $i++; } $results[0]["NROWS"] = $i; $error_message = ""; // Reset Error message $results[0]["STATUS"] = "OK"; // Reset status mysqli_free_result($result_set); // Close result set mysqli_stmt_close($stmt); // Close statement LOG_MSG('INFO', " Status=[OK] Affected rows [" . $results[0]['NROWS'] . "]"); LOG_MSG('DEBUG', "execSQL(): SELECT Response:\n[" . print_r($results[0], true) . "]"); LOG_MSG('DEBUG', "execSQL(): END"); return $results; }
//password is good. //encrypt password $strPassword_hash = password_hash($strPassword, PASSWORD_DEFAULT); //PASSWORD_BCRYPT //echo "hashing password $strPassword .... $strPassword_hash <br>" ; //$strPassword_hash = $strPassword ; //Update Database //$query = "UPDATE ".TBL_USERS." SET password='******' WHERE id = $intUserID " ; //echo "SQL STMNT = " . $query . "<br>"; //$rs = mysqli_query($DB_LINK, $query) or die(mysqli_error()); //echo "SQL.updatesettings = " . $query . "<br>"; if ($DB_MYSQLI->connect_errno) { echo "Failed to connect to MySQL: (" . $DB_MYSQLI->connect_errno . ") " . $DB_MYSQLI->connect_error; } // mysqli_report(MYSQLI_REPORT_ALL); $stmt = mysqli_stmt_init($DB_MYSQLI); if (!($stmt = $DB_MYSQLI->prepare("UPDATE " . TBL_USERS . " SET password = ? WHERE id = ? "))) { echo "Prepare failed: (" . $DB_MYSQLI->errno . ") " . $DB_MYSQLI->error; } if (!$stmt->bind_param('si', $strPassword_hash, $intUserID)) { echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error; } if (!$stmt->execute()) { echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error; } header('Location: ' . PAGE_SETTINGS . "?error_password=password updated"); } $stmt->close(); //Close statement } else { echo "Prepare failed: (" . $DB_MYSQLI->errno . ") " . $DB_MYSQLI->error;