function myBindParam($stmt, $params, $types)
{
$bindArray = refValues($params);
array_unshift($bindArray, $types);
#edump($bindArray);
$res = call_user_func_array(array($stmt, 'bind_param'), $bindArray);
return $res;
}
function sql_deleteHiker($con, $ADK_USER_ID)
{
$queries = array("SET SQL_SAFE_UPDATES = 0;", "DELETE FROM ADK_FILE WHERE ADK_FILE_ID IN(\n\t\t\t\tSELECT MF.ADK_FILE_ID FROM ADK_MESSAGE_FILE_JCT MF LEFT JOIN ADK_MESSAGE M ON MF.ADK_MESSAGE_ID = M.ADK_MESSAGE_ID\n\t\t\t\tWHERE M.ADK_MESSAGE_FROM_USER_ID = ? OR M.ADK_MESSAGE_TO_USER_ID = ?);", "DELETE FROM ADK_FILE WHERE ADK_FILE_ID IN(\n\t\t\t\tSELECT HF.ADK_FILE_ID FROM ADK_HIKE_FILE_JCT HF LEFT JOIN ADK_HIKE H ON HF.ADK_HIKE_ID = H.ADK_HIKE_ID\n\t\t\t\tWHERE H.ADK_USER_ID = ?);", "DELETE FROM ADK_MESSAGE_FILE_JCT WHERE ADK_MESSAGE_ID IN(SELECT ADK_MESSAGE_ID FROM ADK_MESSAGE WHERE ADK_MESSAGE_FROM_USER_ID = ? OR ADK_MESSAGE_TO_USER_ID = ?);", "DELETE FROM ADK_MESSAGE WHERE ADK_MESSAGE_FROM_USER_ID = ? OR ADK_MESSAGE_TO_USER_ID = ?;", "DELETE FROM ADK_HIKE_PEAK_JCT WHERE ADK_HIKE_ID IN(SELECT ADK_HIKE_ID FROM ADK_HIKE WHERE ADK_USER_ID = ?);", "DELETE FROM ADK_HIKE WHERE ADK_USER_ID = ?;", "DELETE FROM ADK_HIKER WHERE ADK_USER_ID = ?;", "DELETE FROM ADK_USER WHERE ADK_USER_ID = ?;", "SET SQL_SAFE_UPDATES = 1;");
$sql_queries = array($con->prepare($queries[0]), $con->prepare($queries[1]), $con->prepare($queries[2]), $con->prepare($queries[3]), $con->prepare($queries[4]), $con->prepare($queries[5]), $con->prepare($queries[6]), $con->prepare($queries[7]), $con->prepare($queries[8]), $con->prepare($queries[9]));
for ($i = 0; $i < count($sql_queries); $i++) {
$_ADK_USER_ID = [];
$types = '';
$qCount = substr_count($queries[$i], '?');
for ($j = 0; $j < $qCount; $j++) {
array_push($_ADK_USER_ID, $ADK_USER_ID);
}
foreach ($_ADK_USER_ID as $id) {
$types .= 'i';
}
$_ADK_USER_ID = array_merge(array($types), $_ADK_USER_ID);
if ($qCount > 0) {
call_user_func_array(array($sql_queries[$i], 'bind_param'), refValues($_ADK_USER_ID));
}
}
return $sql_queries;
}
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;
}
/**
* Perform matching
*
* @param mixed $mysqli
* @param mixed $imp_session
* @param mixed $params
*/
function matchingMultivalues($mysqli, $imp_session, $params)
{
$imp_session['validation'] = array("count_update" => 0, "count_insert" => 0, "count_update_rows" => 0, "count_insert_rows" => 0, "count_error" => 0, "error" => array(), "recs_insert" => array(), "recs_update" => array());
$import_table = $imp_session['import_table'];
$multivalue_field_name = $params['multifield'];
//name of multivalue field
$multivalue_field_name_idx = 0;
$cnt_update_rows = 0;
$cnt_insert_rows = 0;
//disambiguation resolution
$disamb_ids = @$params['disamb_id'];
//record ids
$disamb_keys = @$params['disamb_key'];
//key values
$disamb_resolv = array();
if ($disamb_keys) {
foreach ($disamb_keys as $idx => $keyvalue) {
$disamb_resolv[$disamb_ids[$idx]] = str_replace("\\'", "'", $keyvalue);
//rec_id => keyvalue
}
}
//get rectype to import
$recordType = @$params['sa_rectype'];
if (intval($recordType) < 1) {
return "record type not defined";
}
//create search query - based on mapping (search for sa_keyfield_ - checkboxes in UI)
//for update
$select_query_update_from = array("Records");
$select_query_update_where = array("rec_RecTypeID=" . $recordType);
$sel_fields = array();
$detDefs = getAllDetailTypeStructures(true);
$detDefs = $detDefs['typedefs'];
$idx_dt_type = $detDefs['fieldNamesToIndex']['dty_Type'];
$mapped_fields = array();
foreach ($params as $key => $field_type) {
if (strpos($key, "sa_keyfield_") === 0 && $field_type) {
//get index
$index = substr($key, 12);
$field_name = "field_" . $index;
$mapped_fields[$field_name] = $field_type;
if ($field_type == "url" || $field_type == "id") {
// || $field_type=="scratchpad"){
array_push($select_query_update_where, "rec_" . $field_type . "=?");
} else {
if (is_numeric($field_type)) {
$where = "d" . $index . ".dtl_DetailTypeID=" . $field_type . " and ";
$dt_type = $detDefs[$field_type]['commonFields'][$idx_dt_type];
if ($dt_type == "enum" || $dt_type == "relationtype") {
//if fieldname is numeric - compare it with dtl_Value directly
$where = $where . "( d" . $index . ".dtl_Value=t" . $index . ".trm_ID and t" . $index . ".trm_Label=?)";
//." if(concat('',? * 1) = ?,d".$index.".dtl_Value=?,t".$index.".trm_Label=?) ";
array_push($select_query_update_from, "defTerms t" . $index);
} else {
$where = $where . " (d" . $index . ".dtl_Value=?)";
}
array_push($select_query_update_where, "rec_ID=d" . $index . ".dtl_RecID and " . $where);
array_push($select_query_update_from, "recDetails d" . $index);
} else {
continue;
}
}
array_push($sel_fields, $field_name);
if ($multivalue_field_name == $field_name) {
$multivalue_field_name_idx = count($sel_fields);
}
}
}
//keep mapping field_XXX => dty_ID
$imp_session['validation']['mapped_fields'] = $mapped_fields;
//query to search record ids
$search_query = "SELECT rec_ID, rec_Title " . " FROM " . implode(",", $select_query_update_from) . " WHERE " . implode(" and ", $select_query_update_where);
$search_stmt = $mysqli->prepare($search_query);
$params_dt = str_repeat('s', count($sel_fields));
//$search_stmt->bind_param('s', $field_value);
$search_stmt->bind_result($rec_ID, $rec_Title);
//already founded IDs
$pairs = array();
//to avoid search
$records = array();
$disambiguation = array();
$tmp_idx_insert = array();
//to keep indexes
$tmp_idx_update = array();
//to keep indexes
//loop all records in import table and detect what is for insert and what for update
$select_query = "SELECT imp_id, " . implode(",", $sel_fields) . " FROM " . $import_table;
$res = $mysqli->query($select_query);
if ($res) {
$ind = -1;
while ($row = $res->fetch_row()) {
$imp_id = $row[0];
$row[0] = $params_dt;
$is_update = false;
$is_insert = false;
$multivalue = $row[$multivalue_field_name_idx];
$ids = array();
//split multivalue field
$values = getMultiValues($multivalue, $params['csv_enclosure'], $params['csv_mvsep']);
foreach ($values as $idx => $value) {
$row[$multivalue_field_name_idx] = $value;
//verify that not empty
$fc = $row;
array_shift($fc);
$fc = trim(implode("", $fc));
if ($fc == null || $fc == "") {
continue;
}
//key is empty
$fc = $row;
array_walk($fc, 'trim_lower_accent2');
$keyvalue = implode($params['csv_mvsep'], $fc);
//csv_mvsep - separator
//error_log($keyvalue.' ='.implode(' ',$row));
if (!@$pairs[$keyvalue]) {
//was $value && $value!="" &&
//search for ID
//assign parameters for search query
call_user_func_array(array($search_stmt, 'bind_param'), refValues($row));
$search_stmt->execute();
$disamb = array();
while ($search_stmt->fetch()) {
//keep pair ID => key value
$disamb[$rec_ID] = $rec_Title;
//get value from binding
}
if (count($disamb) == 0) {
//nothing found - insert
$new_id = $ind;
$ind--;
$rec = $row;
$rec[0] = $imp_id;
$tmp_idx_insert[$keyvalue] = count($imp_session['validation']['recs_insert']);
//keep index in rec_insert
array_push($imp_session['validation']['recs_insert'], $rec);
//group_concat(imp_id), ".implode(",",$sel_query)
$is_insert = true;
} else {
if (count($disamb) == 1 || array_search($keyvalue, $disamb_resolv, true) !== false) {
// @$disamb_resolv[addslashes($keyvalue)]){
//either found exact or disamiguation is resolved
$new_id = $rec_ID;
$rec = $row;
$rec[0] = $imp_id;
array_unshift($rec, $rec_ID);
$tmp_idx_update[$keyvalue] = count($imp_session['validation']['recs_update']);
//keep index in rec_update
array_push($imp_session['validation']['recs_update'], $rec);
//rec_ID, group_concat(imp_id), ".implode(",",$sel_query)
$is_update = true;
} else {
$new_id = 'Found:' . count($disamb);
//Disambiguation!
$disambiguation[$keyvalue] = $disamb;
}
}
$pairs[$keyvalue] = $new_id;
array_push($ids, $new_id);
} else {
//already found
if (array_key_exists($keyvalue, $tmp_idx_insert)) {
$imp_session['validation']['recs_insert'][$tmp_idx_insert[$keyvalue]][0] .= "," . $imp_id;
$is_insert = true;
} else {
if (array_key_exists($keyvalue, $tmp_idx_update)) {
$imp_session['validation']['recs_update'][$tmp_idx_update[$keyvalue]][1] .= "," . $imp_id;
$is_update = true;
}
}
array_push($ids, $pairs[$keyvalue]);
}
}
//foreach multivalues
$records[$imp_id] = implode($params['csv_mvsep'], $ids);
//IDS to be added to import table
if ($is_update) {
$cnt_update_rows++;
}
if ($is_insert) {
$cnt_insert_rows++;
}
}
//while import table
}
$search_stmt->close();
// result of work - counts of records to be inserted, updated
$imp_session['validation']['count_update'] = count($imp_session['validation']['recs_update']);
$imp_session['validation']['count_insert'] = count($imp_session['validation']['recs_insert']);
$imp_session['validation']['count_update_rows'] = $cnt_update_rows;
$imp_session['validation']['count_insert_rows'] = $cnt_insert_rows;
$imp_session['validation']['disambiguation'] = $disambiguation;
$imp_session['validation']['disambiguation_lines'] = '';
$imp_session['validation']['pairs'] = $pairs;
//keyvalues => record id - count number of unique values
//MAIN RESULT - ids to be assigned to each record in import table
$imp_session['validation']['records'] = $records;
//imp_id(line#) => list of records ids
return $imp_session;
}
array_push($where_array, $nach_datum);
}
//gar keine zu extrahierende E-Mail-Adressen ausgewählt
if (!$criteria_selected) {
echo "<p class=\"error\">\n";
echo "Es wurden keine Auswahlkriterien eingegeben.<br>";
echo "</p>\n";
exit;
}
//Parameter für den bind_param aufruf zusammenbauen (erster Parameter ist der param_string, weitere Pameter sind die zu bindenen Werte)
$param_array = $where_array;
array_unshift($param_array, $param_string);
//Mitgliederdaten aus der Datenbank holen
$stmt = $mysqli->prepare($query_string);
//Rufe bind_param() auf dem Objekt stmt mit den Parametern aus dem param_array auf, verwende Hilfsfunktion um Übergabe als Reference zu realisieren
call_user_func_array(array(&$stmt, 'bind_param'), refValues($param_array));
$stmt->execute();
$result = $stmt->get_result();
//DB-Abfrage erfolgreich
if ($result) {
echo "<h2>Gefundene Mitglieder:</h2>\n";
echo "<br>\n";
//Gefundene Einträge in Tabelle formatiert ausgeben
while ($recordObj = $result->fetch_assoc()) {
//gefundene Mitgliedsdaten in Originalform in separatem Array abspeichern
$data_db = $recordObj;
//gefundene Mitgliedsdaten in Ausgabeform für die Webseite umwandeln
$data_output['mid'] = $data_db['mid'];
$data_output['eintrittsdatum'] = date("d.m.Y", strtotime($data_db['eintrittsdatum']));
$data_output['geschlecht'] = $data_db['geschlecht'];
$data_output['titel'] = $data_db['titel'];
/**
* Execute a query (can be parameterised) and return either rows affected or data
* @param object [$mysqli] mysqli object used to execute the query;
* @param string [$sql] SQL statement to execute;
* @param array [$parameters] = array of type and values of the parameters (if any)
* @param boolean [$retCnt] true to return row count false to return an array with the values;
*/
function execSQL($mysqli, $sql, $params, $retCnt = true)
{
$result;
if ($params == null || count($params) < 1) {
// not parameterised
if ($result = $mysqli->query($sql)) {
if ($retCnt) {
$result = $mysqli->affected_rows;
}
} else {
$result = $mysqli->error;
if ($result == "") {
$result = $mysqli->affected_rows;
} else {
}
}
} else {
//prepared query
$stmt = $mysqli->prepare($sql) or die("Failed to prepare the SQL statement: " . $sql);
call_user_func_array(array($stmt, 'bind_param'), refValues($params));
$stmt->execute();
if ($retCnt) {
$result = $mysqli->error;
if ($result == "") {
$result = $mysqli->affected_rows;
} else {
}
} else {
$meta = $stmt->result_metadata();
while ($field = $meta->fetch_field()) {
$parameters[] =& $row[$field->name];
}
call_user_func_array(array($stmt, 'bind_result'), refValues($parameters));
while ($stmt->fetch()) {
$x = array();
foreach ($row as $key => $val) {
$x[$key] = $val;
}
$results[] = $x;
}
$result = $results;
}
$stmt->close();
}
return $result;
}
/** TAKEN FROM utils_db.php
* insert or update record for given table
*
* returns record ID in case success or error message
*
* @param mixed $mysqli
* @param mixed $table_name
* @param mixed $table_prefix
* @param mixed $record - array(fieldname=>value) - all values considered as String except when field ended with ID
* fields that don't have specified prefix are ignored
*/
function mysql__insertupdate($database, $table_name, $table_prefix, $record)
{
$mysqli = server_connect();
mysql__usedatabase($mysqli, $database);
$ret = null;
if (substr($table_prefix, -1) !== '_') {
$table_prefix = $table_prefix . '_';
}
$rec_ID = intval(@$record[$table_prefix . 'ID']);
$isinsert = $rec_ID < 1;
if ($isinsert) {
$query = "INSERT into {$table_name} (";
$query2 = ') VALUES (';
} else {
$query = "UPDATE {$table_name} set ";
}
$params = array();
$params[0] = '';
foreach ($record as $fieldname => $value) {
if (strpos($fieldname, $table_prefix) !== 0) {
//ignore fields without prefix
//$fieldname = $table_prefix.$fieldname;
continue;
}
if ($isinsert) {
$query = $query . $fieldname . ', ';
$query2 = $query2 . '?, ';
} else {
if ($fieldname == $table_prefix . "ID") {
continue;
}
$query = $query . $fieldname . '=?, ';
}
$dtype = substr($fieldname, -2) === 'ID' || substr($fieldname, -2) === 'Id' ? 'i' : 's';
$params[0] = $params[0] . $dtype;
if ($dtype == 'i' && $value == '') {
$value = null;
}
array_push($params, $value);
}
$query = substr($query, 0, strlen($query) - 2);
if ($isinsert) {
$query2 = substr($query2, 0, strlen($query2) - 2) . ")";
$query = $query . $query2;
} else {
$query = $query . " where " . $table_prefix . "ID=" . $rec_ID;
}
//error_log($query);
//error_log(print_r($params, true));
$stmt = $mysqli->prepare($query);
if ($stmt) {
call_user_func_array(array($stmt, 'bind_param'), refValues($params));
if (!$stmt->execute()) {
$ret = $mysqli->error;
} else {
$ret = $isinsert ? $stmt->insert_id : $rec_ID;
}
$stmt->close();
} else {
$ret = $mysqli->error;
}
return $ret;
}
$param_keys[] = "img = ?";
} elseif ($key == "info") {
$param_keys[] = "info = ?";
}
}
$sql_q = "UPDATE users SET " . implode(',', $param_keys) . " WHERE id = ?";
$sql_s = $mysql->prepare($sql_q);
$params = new BindParam();
foreach ($change as $key => $value) {
if ($key == "username") {
$params->add('s', $value);
} elseif ($key == "email") {
$params->add('s', $value);
} elseif ($key == "password") {
$passhash = hashpass($value);
$params->add('s', $params);
} elseif ($key == "img") {
$params->add('s', $value);
} elseif ($key == "info") {
$params->add('s', $value);
}
}
$params->add('i', $uid);
call_user_func_array(array($sql_s, "bind_param"), refValues($params->get()));
$sql_s->execute();
$sql_s->close();
if (isset($change['password'])) {
$change['password'] = '******';
}
log_api_action($api_caller['id'], "editing user: "******" change data: " . http_build_query($change));
}
function dbUpdateTable($table, $data, $id, $insert = false)
{
global $db;
$idfield = preg_match("/_cstm\$/", $table) ? 'id_c' : 'id';
$sql = "update {$table} set ";
if ($insert) {
$sql = "insert into {$table} set ";
$data[$idfield] = $id;
}
$sep = "";
$args = array('');
foreach ($data as $key => $val) {
$sql .= $sep . $key . " = ?";
$args[0] .= "s";
// you'll need to map these based on name
$args[] = $val;
$sep = ", ";
}
if (!$insert) {
$sql .= " WHERE {$idfield} = ?";
$args[] = $id;
$args[0] .= "s";
// you'll need to map these based on name
}
$realdb = $db->getDatabase();
$stmt = $realdb->stmt_init();
if ($stmt->prepare($sql)) {
call_user_func_array(array($stmt, 'bind_param'), refValues($args));
if (!$stmt->execute()) {
print $realdb->error;
}
$stmt->close();
} else {
print $realdb->error;
}
// $id = $id == null ? $realdb->insert_id : $id;
// return $id;
}
function sql_deleteChecklist($con, $listID)
{
$queries = array("DELETE FROM LISTITEM WHERE LIST_ID = ?;", "DELETE FROM LIST WHERE LIST_ID = ?;");
$sql_queries = array($con->prepare($queries[0]), $con->prepare($queries[1]));
for ($i = 0; $i < count($sql_queries); $i++) {
$_listID = [];
$types = '';
$qCount = substr_count($queries[$i], '?');
for ($j = 0; $j < $qCount; $j++) {
array_push($_listID, $listID);
}
foreach ($_listID as $id) {
$types .= 'i';
}
$_listID = array_merge(array($types), $_listID);
if ($qCount > 0) {
call_user_func_array(array($sql_queries[$i], 'bind_param'), refValues($_listID));
}
}
return $sql_queries;
}
function sql_request($request, $requestArray)
{
global $mysqli;
if (!$requestArray || is_array($requestArray) && count($requestArray) < 1) {
$result = $mysqli->query($request);
} else {
if (!is_array($requestArray)) {
$requestArray = array($requestArray);
}
$stmt = $mysqli->stmt_init();
$r = $stmt->prepare($request);
if (!$r) {
printf("Error: %s\n", $mysqli->error);
exit;
}
$types = "";
foreach ($requestArray as $value) {
if ($value === 0 || $value === "0") {
$types .= "i";
} else {
$iv = (int) $value;
$dv = (double) $value;
if ($iv != 0 && $iv == $dv && strlen($value) == strlen($iv)) {
$types .= "i";
} else {
if ($dv != 0 && strlen($value) == strlen($dv)) {
$types .= "d";
} else {
$types .= "s";
}
}
}
}
$params = array($types);
foreach ($requestArray as $value) {
array_push($params, $value);
}
$r = call_user_func_array(array($stmt, "bind_param"), refValues($params));
if (!$r) {
printf("Error: %s\n", $mysqli->error);
exit;
}
$stmt->execute();
//if mysqlnd is installed
//$result = $stmt->get_result();
$result = iimysqli_stmt_get_result($stmt);
}
if (!$result) {
printf("Error: %s\n", $mysqli->error);
//To make sure big data conversion don't pile up errors we will exit upon the first error
//If for some reason you know what you are doing, remove the exit();
exit;
}
return $result;
}
function _query($sql, $inputarr)
{
global $ADODB_COUNTRECS;
if (is_array($sql)) {
$stmt = $sql[1];
$a = '';
foreach ($inputarr as $k => $v) {
if (is_string($v)) {
$a .= 's';
} else {
if (is_integer($v)) {
$a .= 'i';
} else {
$a .= 'd';
}
}
}
$ret = call_user_func_array('mysqli_stmt_bind_param', array_merge(array($stmt, $a), refValues($inputarr)));
$ret = mysqli_stmt_execute($stmt);
$rs = mysqli_stmt_get_result($stmt);
if (!$rs && $ret) {
return mysqli_stmt_store_result($stmt);
}
return $rs;
}
if ($rs = mysqli_multi_query($this->_connectionID, $sql . ';')) {
$rs = $ADODB_COUNTRECS ? @mysqli_store_result($this->_connectionID) : @mysqli_use_result($this->_connectionID);
return $rs ? $rs : true;
// mysqli_more_results( $this->_connectionID )
} else {
if ($this->debug) {
ADOConnection::outp("Query: " . $sql . " failed. " . $this->ErrorMsg());
}
return false;
}
}
if (isset($_POST['tags'])) {
$tags = explode("|", $_POST['tags']);
$numOfTags = count($tags);
$query = "SELECT video_id, \n\t\t\t SUM(CASE WHEN video_id=video_id THEN 1 ELSE 0 END) AS hits \n\t\t\t FROM (\n\t\t\t SELECT videos.id AS video_id, tags.name AS tag FROM relations\n\t\t\t JOIN videos\n\t\t\t ON relations.video_id = videos.id\n\t\t\t JOIN tags\n\t\t\t ON relations.tag_id = tags.id\n\t\t\t WHERE ";
for ($i = 0; $i < $numOfTags - 1; $i++) {
$query .= "tags.name = ? OR ";
}
$query .= "tags.name = ?\n\t\t\t) AS results\n\t\t GROUP BY video_id \n\t\t ORDER BY hits \n\t\t DESC;";
$types = "";
$params = array();
for ($i = 0; $i < $numOfTags; $i++) {
$types .= "s";
array_push($params, $tags[$i]);
}
$stmt = mysqli_prepare($conn, $query);
call_user_func_array("mysqli_stmt_bind_param", array_merge(array($stmt, $types), refValues($params)));
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $result, $hits);
$video_ids = array();
while (mysqli_stmt_fetch($stmt)) {
array_push($video_ids, $result);
}
mysqli_stmt_close($stmt);
echo '<ul id="videoResults">';
foreach ($video_ids as $id) {
$query = "SELECT tag AS tags \n\t\t\t\t\tFROM(\n\t\t\t\t SELECT videos.id AS video_id, \n\t\t\t\t tags.name AS tag \n\t\t\t\t FROM relations\n\t\t\t\t JOIN videos\n\t\t\t\t ON relations.video_id = videos.id\n\t\t\t\t JOIN tags\n\t\t\t\t ON relations.tag_id = tags.id\n\t\t\t\t ) AS results \n\t\t\t\t\tWHERE video_id =" . $id . ";";
$stmt = mysqli_query($conn, $query);
echo '<li class=videoResult><h3>File ID: ' . $id . '</h3><h4>Tags:</h4><ul class="tagResults">';
while ($row = mysqli_fetch_assoc($stmt)) {
echo '<li class="tagResult">' . $row['tags'] . "</li>";
}
function sql_request($request, $requestArray)
{
global $mysqli;
if (!$requestArray) {
$result = $mysqli->query($request);
} else {
if (!is_array($requestArray)) {
$requestArray = array($requestArray);
}
$stmt = $mysqli->prepare($request);
$types = "";
foreach ($requestArray as $value) {
if (is_integer($value)) {
$types .= "i";
} elseif (is_double($value)) {
$types .= "d";
} else {
$types .= "s";
}
}
$params = array($types);
foreach ($requestArray as $value) {
array_push($params, $value);
}
$r = call_user_func_array(array($stmt, "bind_param"), refValues($params));
$stmt->execute();
$result = $stmt->get_result();
}
return $result;
}
/**
* Given a databasse query and the parameters for it, execute the query.
* @return false if something happened, the prepared statement object otherwise
*/
function sql_prepare_and_execute($db, $select, $params)
{
$prep = $db->prepare($select);
if (!$prep) {
echo "{\"error\":\"";
echo "PREPARE failed: (" . $db->errno . ") " . $db->error . " [" . $select . "]\"}";
return;
}
//Find the parameter type. We only support int and string
$types = "";
foreach ($params as $param) {
$types .= is_array($param) ? "i" : is_integer($param) ? "i" : (is_float($param) ? "d" : "s");
}
$parameters = array_merge(array($types), $params);
$answer = call_user_func_array(array($prep, "bind_param"), refValues($parameters));
if (!$answer) {
echo "{\"error\":\"";
echo "BIND failed: (" . $db->errno . ") " . $db->error . "\"}";
return false;
}
if (!$prep->execute()) {
echo "{\"error\":\"";
echo "EXECUTE failed: (" . $db->errno . ") " . $db->error . "\"}";
return false;
}
return $prep;
}
