/**
* Function to generate unique condition for specified row.
*
* @param resource $handle current query result
* @param integer $fields_cnt number of fields
* @param array $fields_meta meta information about fields
* @param array $row current row
* @param boolean $force_unique generate condition only on pk or unique
*
* @access public
*
* @return array the calculated condition and whether condition is unique
*/
function PMA_getUniqueCondition($handle, $fields_cnt, $fields_meta, $row, $force_unique = false)
{
$primary_key = '';
$unique_key = '';
$nonprimary_condition = '';
$preferred_condition = '';
$primary_key_array = array();
$unique_key_array = array();
$nonprimary_condition_array = array();
$condition_array = array();
for ($i = 0; $i < $fields_cnt; ++$i) {
$condition = '';
$con_key = '';
$con_val = '';
$field_flags = PMA_DBI_field_flags($handle, $i);
$meta = $fields_meta[$i];
// do not use a column alias in a condition
if (!isset($meta->orgname) || !strlen($meta->orgname)) {
$meta->orgname = $meta->name;
if (isset($GLOBALS['analyzed_sql'][0]['select_expr']) && is_array($GLOBALS['analyzed_sql'][0]['select_expr'])) {
foreach ($GLOBALS['analyzed_sql'][0]['select_expr'] as $select_expr) {
// need (string) === (string)
// '' !== 0 but '' == 0
if ((string) $select_expr['alias'] === (string) $meta->name) {
$meta->orgname = $select_expr['column'];
break;
}
// end if
}
// end foreach
}
}
// Do not use a table alias in a condition.
// Test case is:
// select * from galerie x WHERE
//(select count(*) from galerie y where y.datum=x.datum)>1
//
// But orgtable is present only with mysqli extension so the
// fix is only for mysqli.
// Also, do not use the original table name if we are dealing with
// a view because this view might be updatable.
// (The isView() verification should not be costly in most cases
// because there is some caching in the function).
if (isset($meta->orgtable) && $meta->table != $meta->orgtable && !PMA_Table::isView($GLOBALS['db'], $meta->table)) {
$meta->table = $meta->orgtable;
}
// to fix the bug where float fields (primary or not)
// can't be matched because of the imprecision of
// floating comparison, use CONCAT
// (also, the syntax "CONCAT(field) IS NULL"
// that we need on the next "if" will work)
if ($meta->type == 'real') {
$con_key = 'CONCAT(' . PMA_backquote($meta->table) . '.' . PMA_backquote($meta->orgname) . ')';
} else {
$con_key = PMA_backquote($meta->table) . '.' . PMA_backquote($meta->orgname);
}
// end if... else...
$condition = ' ' . $con_key . ' ';
if (!isset($row[$i]) || is_null($row[$i])) {
$con_val = 'IS NULL';
} else {
// timestamp is numeric on some MySQL 4.1
// for real we use CONCAT above and it should compare to string
if ($meta->numeric && $meta->type != 'timestamp' && $meta->type != 'real') {
$con_val = '= ' . $row[$i];
} elseif (($meta->type == 'blob' || $meta->type == 'string') && stristr($field_flags, 'BINARY') && !empty($row[$i])) {
// do not waste memory building a too big condition
if (strlen($row[$i]) < 1000) {
// use a CAST if possible, to avoid problems
// if the field contains wildcard characters % or _
$con_val = '= CAST(0x' . bin2hex($row[$i]) . ' AS BINARY)';
} else {
// this blob won't be part of the final condition
$con_val = null;
}
} elseif (in_array($meta->type, PMA_getGISDatatypes()) && !empty($row[$i])) {
// do not build a too big condition
if (strlen($row[$i]) < 5000) {
$condition .= '=0x' . bin2hex($row[$i]) . ' AND';
} else {
$condition = '';
}
} elseif ($meta->type == 'bit') {
$con_val = "= b'" . PMA_printable_bit_value($row[$i], $meta->length) . "'";
} else {
$con_val = '= \'' . PMA_sqlAddSlashes($row[$i], false, true) . '\'';
}
}
if ($con_val != null) {
$condition .= $con_val . ' AND';
if ($meta->primary_key > 0) {
$primary_key .= $condition;
$primary_key_array[$con_key] = $con_val;
} elseif ($meta->unique_key > 0) {
$unique_key .= $condition;
$unique_key_array[$con_key] = $con_val;
}
$nonprimary_condition .= $condition;
$nonprimary_condition_array[$con_key] = $con_val;
}
}
// end for
// Correction University of Virginia 19991216:
// prefer primary or unique keys for condition,
// but use conjunction of all values if no primary key
$clause_is_unique = true;
if ($primary_key) {
$preferred_condition = $primary_key;
$condition_array = $primary_key_array;
} elseif ($unique_key) {
$preferred_condition = $unique_key;
$condition_array = $unique_key_array;
} elseif (!$force_unique) {
$preferred_condition = $nonprimary_condition;
$condition_array = $nonprimary_condition_array;
$clause_is_unique = false;
}
$where_clause = trim(preg_replace('|\\s?AND$|', '', $preferred_condition));
return array($where_clause, $clause_is_unique, $condition_array);
}
*/
require_once 'libraries/common.inc.php';
require_once 'libraries/mysql_charsets.lib.php';
require_once 'libraries/tbl_select.lib.php';
$GLOBALS['js_include'][] = 'makegrid.js';
$GLOBALS['js_include'][] = 'sql.js';
$GLOBALS['js_include'][] = 'tbl_select.js';
$GLOBALS['js_include'][] = 'tbl_change.js';
$GLOBALS['js_include'][] = 'jquery/jquery-ui-1.8.16.custom.js';
$GLOBALS['js_include'][] = 'jquery/timepicker.js';
$GLOBALS['js_include'][] = 'gis_data_editor.js';
$titles['Browse'] = PMA_getIcon('b_browse.png', __('Browse foreign values'));
$geom_types = PMA_getGISDatatypes();
$post_params = array(
'ajax_request',
'collations',
'db',
'distinct',
'fields',
'func',
'max_number_of_fields',
'names',
'order',
'orderField',
'param',
'session_max_rows',
'table',
/**
* Return the where clause for query generation based on the inputs provided.
*
* @param mixed $fields Search criteria input
* @param string $names Name of the column on which search is submitted
* @param string $types Type of the field
* @param string $collations Field collation
* @param string $func_type Search fucntion/operator
* @param bool $unaryFlag Whether operator unary or not
* @param bool $geom_func Whether geometry functions should be applied
*
* @return string HTML content for viewing foreing data and elements
* for search criteria input.
*/
function PMA_tbl_search_getWhereClause($fields, $names, $types, $collations, $func_type, $unaryFlag, $geom_func = null)
{
/**
* @todo move this to a more apropriate place
*/
$geom_unary_functions = array('IsEmpty' => 1, 'IsSimple' => 1, 'IsRing' => 1, 'IsClosed' => 1);
$w = '';
// If geometry function is set apply it to the field name
if ($geom_func != null && trim($geom_func) != '') {
// Get details about the geometry fucntions
$geom_funcs = PMA_getGISFunctions($types, true, false);
// If the function takes a single parameter
if ($geom_funcs[$geom_func]['params'] == 1) {
$backquoted_name = $geom_func . '(' . PMA_backquote($names) . ')';
} else {
// If the function takes two parameters
// create gis data from the string
$gis_data = PMA_createGISData($fields);
$w = $geom_func . '(' . PMA_backquote($names) . ',' . $gis_data . ')';
return $w;
}
// New output type is the output type of the function being applied
$types = $geom_funcs[$geom_func]['type'];
// If the where clause is something like 'IsEmpty(`spatial_col_name`)'
if (isset($geom_unary_functions[$geom_func]) && trim($fields) == '') {
$w = $backquoted_name;
return $w;
}
} else {
$backquoted_name = PMA_backquote($names);
}
if ($unaryFlag) {
$fields = '';
$w = $backquoted_name . ' ' . $func_type;
} elseif (in_array($types, PMA_getGISDatatypes()) && !empty($fields)) {
// create gis data from the string
$gis_data = PMA_createGISData($fields);
$w = $backquoted_name . ' ' . $func_type . ' ' . $gis_data;
} elseif (strncasecmp($types, 'enum', 4) == 0) {
if (!empty($fields)) {
if (!is_array($fields)) {
$fields = explode(',', $fields);
}
$enum_selected_count = count($fields);
if ($func_type == '=' && $enum_selected_count > 1) {
$func_type = 'IN';
$parens_open = '(';
$parens_close = ')';
} elseif ($func_type == '!=' && $enum_selected_count > 1) {
$func_type = 'NOT IN';
$parens_open = '(';
$parens_close = ')';
} else {
$parens_open = '';
$parens_close = '';
}
$enum_where = '\'' . PMA_sqlAddslashes($fields[0]) . '\'';
for ($e = 1; $e < $enum_selected_count; $e++) {
$enum_where .= ', \'' . PMA_sqlAddslashes($fields[$e]) . '\'';
}
$w = $backquoted_name . ' ' . $func_type . ' ' . $parens_open . $enum_where . $parens_close;
}
} elseif ($fields != '') {
// For these types we quote the value. Even if it's another type (like INT),
// for a LIKE we always quote the value. MySQL converts strings to numbers
// and numbers to strings as necessary during the comparison
if (preg_match('@char|binary|blob|text|set|date|time|year@i', $types) || strpos(' ' . $func_type, 'LIKE')) {
$quot = '\'';
} else {
$quot = '';
}
// LIKE %...%
if ($func_type == 'LIKE %...%') {
$func_type = 'LIKE';
$fields = '%' . $fields . '%';
}
if ($func_type == 'REGEXP ^...$') {
$func_type = 'REGEXP';
$fields = '^' . $fields . '$';
}
if ($func_type == 'IN (...)' || $func_type == 'NOT IN (...)' || $func_type == 'BETWEEN' || $func_type == 'NOT BETWEEN') {
$func_type = str_replace(' (...)', '', $func_type);
// quote values one by one
$values = explode(',', $fields);
foreach ($values as &$value) {
$value = $quot . PMA_sqlAddslashes(trim($value)) . $quot;
}
if ($func_type == 'BETWEEN' || $func_type == 'NOT BETWEEN') {
$w = $backquoted_name . ' ' . $func_type . ' ' . (isset($values[0]) ? $values[0] : '') . ' AND ' . (isset($values[1]) ? $values[1] : '');
} else {
$w = $backquoted_name . ' ' . $func_type . ' (' . implode(',', $values) . ')';
}
} else {
$w = $backquoted_name . ' ' . $func_type . ' ' . $quot . PMA_sqlAddslashes($fields) . $quot;
}
}
// end if
return $w;
}
if ($cfg['ShowFieldTypesInDataEditView']) {
?>
<td class="center<?php
echo $field['wrap'];
?>
"><span class="column_type"><?php
echo $field['pma_type'];
?>
</span>
</td>
<?php
}
//End if
// Get a list of GIS data types.
$gis_data_types = PMA_getGISDatatypes();
// Prepares the field value
$real_null_value = false;
$special_chars_encoded = '';
if (isset($vrow)) {
// (we are editing)
if (is_null($vrow[$field['Field']])) {
$real_null_value = true;
$vrow[$field['Field']] = '';
$special_chars = '';
$data = $vrow[$field['Field']];
} elseif ($field['True_Type'] == 'bit') {
$special_chars = PMA_printableBitValue($vrow[$field['Field']], $extracted_columnspec['spec_in_brackets']);
} elseif (in_array($field['True_Type'], $gis_data_types)) {
// Convert gis data to Well Know Text format
$vrow[$field['Field']] = PMA_asWKT($vrow[$field['Field']], true);
