mysql qbe_db_qbe.php

最新推荐文章于 2021-03-25 05:04:04 发布

Alex刘DS爱好者

最新推荐文章于 2021-03-25 05:04:04 发布

阅读量95

点赞数

文章标签： mysql qbe

本文链接：https://blog.csdn.net/weixin_29447549/article/details/113968527

版权

/* vim: set expandtab sw=4 ts=4 sts=4: */

/**

* query by example the whole database

* @version $Id$

* @package phpMyAdmin

/**

* requirements

require_once './libraries/common.inc.php';

require_once './libraries/Table.class.php';

require_once './libraries/relation.lib.php';

/**

* Gets the relation settings

$cfgRelation = PMA_getRelationsParam();

/**

* A query has been submitted -> execute it, else display the headers

if (isset($_REQUEST['submit_sql']) && ! empty($sql_query)) {

$goto = 'db_sql.php';

$zero_rows = htmlspecialchars($GLOBALS['strSuccess']);

require './sql.php';

exit;

} else {

$sub_part = '_qbe';

require './libraries/db_common.inc.php';

$url_query .= '&goto=db_qbe.php';

$url_params['goto'] = 'db_qbe.php';

require './libraries/db_info.inc.php';

}

if (isset($_REQUEST['submit_sql'])

&& ! preg_match('@^SELECT@i', $sql_query)) {

PMA_Message::warning('strHaveToShow')->display();

}

/**

* Initialize some variables

$col_cnt = PMA_ifSetOr($_REQUEST['col_cnt'], 3, 'numeric');

$add_col = PMA_ifSetOr($_REQUEST['add_col'], 0, 'numeric');

$add_row = PMA_ifSetOr($_REQUEST['add_row'], 0, 'numeric');

$rows = PMA_ifSetOr($_REQUEST['rows'], 0, 'numeric');

$ins_col = PMA_ifSetOr($_REQUEST['ins_col'], null, 'array');

$del_col = PMA_ifSetOr($_REQUEST['del_col'], null, 'array');

$prev_criteria = isset($_REQUEST['prev_criteria'])

? $_REQUEST['prev_criteria']

: array();

$criteria = isset($_REQUEST['criteria'])

? $_REQUEST['criteria']

: array_fill(0, $col_cnt, '');

$ins_row = isset($_REQUEST['ins_row'])

? $_REQUEST['ins_row']

: array_fill(0, $col_cnt, '');

$del_row = isset($_REQUEST['del_row'])

? $_REQUEST['del_row']

: array_fill(0, $col_cnt, '');

$and_or_row = isset($_REQUEST['and_or_row'])

? $_REQUEST['and_or_row']

: array_fill(0, $col_cnt, '');

$and_or_col = isset($_REQUEST['and_or_col'])

? $_REQUEST['and_or_col']

: array_fill(0, $col_cnt, '');

// minimum width

$form_column_width = 12;

$col = max($col_cnt + $add_col, 0);

$row = max($rows + $add_row, 0);

// The tables list sent by a previously submitted form

if (PMA_isValid($_REQUEST['TableList'], 'array')) {

foreach ($_REQUEST['TableList'] as $each_table) {

$tbl_names[$each_table] = ' selected="selected"';

}

} // end if

// this was a work in progress, deactivated for now

//$columns = PMA_DBI_get_columns_full($GLOBALS['db']);

//$tables = PMA_DBI_get_columns_full($GLOBALS['db']);

/**

* Prepares the form

$tbl_result = PMA_DBI_query('SHOW TABLES FROM ' . PMA_backquote($db) . ';', null, PMA_DBI_QUERY_STORE);

$tbl_result_cnt = PMA_DBI_num_rows($tbl_result);

if (0 == $tbl_result_cnt) {

PMA_Message::error('strNoTablesFound')->display();

require_once './libraries/footer.inc.php';

exit;

}

// The tables list gets from MySQL

while (list($tbl) = PMA_DBI_fetch_row($tbl_result)) {

$fld_results = PMA_DBI_get_fields($db, $tbl);

if (empty($tbl_names[$tbl]) && !empty($_REQUEST['TableList'])) {

$tbl_names[$tbl] = '';

} else {

$tbl_names[$tbl] = ' selected="selected"';

} // end if

// The fields list per selected tables

if ($tbl_names[$tbl] == ' selected="selected"') {

$each_table = PMA_backquote($tbl);

$fld[] = $each_table . '.*';

foreach ($fld_results as $each_field) {

$each_field = $each_table . '.' . PMA_backquote($each_field['Field']);

$fld[] = $each_field;

// increase the width if necessary

$form_column_width = max(strlen($each_field), $form_column_width);

} // end foreach

} // end if

} // end while

PMA_DBI_free_result($tbl_result);

// largest width found

$realwidth = $form_column_width . 'ex';

/**

* Displays the Query by example form

/**

* Enter description here...

* @param array $columns

* @param numeric $column_number

* @param string $selected

function showColumnSelectCell($columns, $column_number, $selected = '')

{

foreach ($columns as $column) {

if ($column === $selected) {

$sel = ' selected="selected"';

} else {

$sel = '';

}

echo ''

. str_replace(' ', ' ', htmlspecialchars($column)) . '' . "\n";

}

<?php echo $strField; ?>:

$z = 0;

for ($x = 0; $x < $col; $x++) {

if (isset($ins_col[$x]) && $ins_col[$x] == 'on') {

showColumnSelectCell($fld, $z);

$z++;

}

if (! empty($del_col) && isset($del_col[$x]) && $del_col[$x] == 'on') {

continue;

}

$selected = '';

if (isset($Field[$x])) {

$selected = $Field[$x];

$curField[$z] = $Field[$x];

}

showColumnSelectCell($fld, $z, $selected);

$z++;

} // end for

<?php echo $strSort; ?>:

$z = 0;

for ($x = 0; $x < $col; $x++) {

if (!empty($ins_col) && isset($ins_col[$x]) && $ins_col[$x] == 'on') {

$z++;

} // end if

echo "\n";

if (!empty($del_col) && isset($del_col[$x]) && $del_col[$x] == 'on') {

continue;

}

echo "\n";

// If they have chosen all fields using the * selector,

// then sorting is not available

// Robbat2 - Fix for Bug #570698

if (isset($Sort[$x]) && isset($Field[$x])

&& substr($Field[$x], -2) == '.*') {

$Sort[$x] = '';

} //end if

if (isset($Sort[$x]) && $Sort[$x] == 'ASC') {

$curSort[$z] = $Sort[$x];

$sel = ' selected="selected"';

} else {

$sel = '';

} // end if

echo ' ';

echo '' . $strAscending . '' . "\n";

if (isset($Sort[$x]) && $Sort[$x] == 'DESC') {

$curSort[$z] = $Sort[$x];

$sel = ' selected="selected"';

} else {

$sel = '';

} // end if

echo ' ';

echo '' . $strDescending . '' . "\n";

$z++;

echo "\n";

} // end for

<?php echo $strShow; ?>:

$z = 0;

for ($x = 0; $x < $col; $x++) {

if (!empty($ins_col) && isset($ins_col[$x]) && $ins_col[$x] == 'on') {

$z++;

} // end if

echo "\n";

if (!empty($del_col) && isset($del_col[$x]) && $del_col[$x] == 'on') {

continue;

}

if (isset($Show[$x])) {

$checked = ' checked="checked"';

$curShow[$z] = $Show[$x];

} else {

$checked = '';

}

$z++;

echo "\n";

} // end for

<?php echo $strCriteria; ?>:

$z = 0;

for ($x = 0; $x < $col; $x++) {

if (!empty($ins_col) && isset($ins_col[$x]) && $ins_col[$x] == 'on') {

$z++;

} // end if

echo "\n";

if (!empty($del_col) && isset($del_col[$x]) && $del_col[$x] == 'on') {

continue;

}

if (isset($criteria[$x])) {

$stripped_Criteria = $criteria[$x];

}

if ((empty($prev_criteria) || !isset($prev_criteria[$x]))

|| $prev_criteria[$x] != htmlspecialchars($stripped_Criteria)) {

$curCriteria[$z] = $stripped_Criteria;

} else {

$curCriteria[$z] = $prev_criteria[$x];

}

$z++;

echo "\n";

} // end for

$w = 0;

$odd_row = true;

for ($y = 0; $y <= $row; $y++) {

if (isset($ins_row[$y]) && $ins_row[$y] == 'on') {

$chk['or'] = ' checked="checked"';

$chk['and'] = '';

<?php echo $strQBEIns; ?>:	<?php echo $strAnd; ?>:	/>
<?php echo $strQBEDel; ?>:	<?php echo $strOr; ?>:	/>

$z = 0;

for ($x = 0; $x < $col; $x++) {

if (isset($ins_col[$x]) && $ins_col[$x] == 'on') {

echo "\n";

$or = 'Or' . $w . '[' . $z . ']';

$z++;

} // end if

if (isset($del_col[$x]) && $del_col[$x] == 'on') {

continue;

}

echo "\n";

$or = 'Or' . $w . '[' . $z . ']';

$z++;

} // end for

$w++;

echo "\n";

$odd_row =! $odd_row;

} // end if

if (isset($del_row[$y]) && $del_row[$y] == 'on') {

continue;

}

if (isset($and_or_row[$y])) {

$curAndOrRow[$w] = $and_or_row[$y];

}

if (isset($and_or_row[$y]) && $and_or_row[$y] == 'and') {

$chk['and'] = ' checked="checked"';

$chk['or'] = '';

} else {

$chk['or'] = ' checked="checked"';

$chk['and'] = '';

}

echo "\n";

<?php echo $strQBEIns; ?>:	<?php echo $strAnd; ?>:	/>
<?php echo $strQBEDel; ?>:	<?php echo $strOr; ?>:	/>

$z = 0;

for ($x = 0; $x < $col; $x++) {

if (!empty($ins_col) && isset($ins_col[$x]) && $ins_col[$x] == 'on') {

echo "\n";

$or = 'Or' . $w . '[' . $z . ']';

$z++;

} // end if

if (!empty($del_col) && isset($del_col[$x]) && $del_col[$x] == 'on') {

continue;

}

echo "\n";

$or = 'Or' . $y;

if (!isset(${$or})) {

${$or} = '';

}

if (!empty(${$or}) && isset(${$or}[$x])) {

$stripped_or = ${$or}[$x];

} else {

$stripped_or = '';

}

if (!empty(${$or}) && isset(${$or}[$x])) {

${'cur' . $or}[$z] = ${$or}[$x];

}

$z++;

} // end for

$w++;

echo "\n";

$odd_row =! $odd_row;

} // end for

<?php echo $strModify; ?>:

$z = 0;

for ($x = 0; $x < $col; $x++) {

if (!empty($ins_col) && isset($ins_col[$x]) && $ins_col[$x] == 'on') {

$curAndOrCol[$z] = $and_or_col[$y];

if ($and_or_col[$z] == 'or') {

$chk['or'] = ' checked="checked"';

$chk['and'] = '';

} else {

$chk['and'] = ' checked="checked"';

$chk['or'] = '';

}

<?php echo $strOr; ?>:

<?php echo $strAnd; ?>:

$z++;

} // end if

echo "\n";

if (!empty($del_col) && isset($del_col[$x]) && $del_col[$x] == 'on') {

continue;

}

if (isset($and_or_col[$y])) {

$curAndOrCol[$z] = $and_or_col[$y];

}

if (isset($and_or_col[$z]) && $and_or_col[$z] == 'or') {

$chk['or'] = ' checked="checked"';

$chk['and'] = '';

} else {

$chk['and'] = ' checked="checked"';

$chk['or'] = '';

}

<?php echo $strOr; ?>:

<?php echo $strAnd; ?>:

$z++;

echo "\n";

} // end for

$w--;

$url_params['db'] = $db;

$url_params['col_cnt'] = $z;

$url_params['rows'] = $w;

echo PMA_generate_common_hidden_inputs($url_params);

<?php echo $strAddDeleteRow; ?>:

-3

-2

-1

<?php echo $strAddDeleteColumn; ?>:

-3

-2

-1

$strTableListOptions = '';

$numTableListOptions = 0;

foreach ($tbl_names as $key => $val) {

$strTableListOptions .= ' ';

$strTableListOptions .= ''

. str_replace(' ', ' ', htmlspecialchars($key)) . '' . "\n";

$numTableListOptions++;

}

size="<?php echo ($numTableListOptions > 30) ? '15' : '7'; ?>">

rows="<?php echo ($numTableListOptions > 30) ? '15' : '7'; ?>"

dir="<?php echo $text_dir; ?>">

// 1. SELECT

$last_select = 0;

if (!isset($qry_select)) {

$qry_select = '';

}

for ($x = 0; $x < $col; $x++) {

if (!empty($curField[$x]) && isset($curShow[$x]) && $curShow[$x] == 'on') {

if ($last_select) {

$qry_select .= ', ';

}

$qry_select .= $curField[$x];

$last_select = 1;

}

} // end for

if (!empty($qry_select)) {

echo 'SELECT ' . htmlspecialchars($qry_select) . "\n";

}

// 2. FROM

// Create LEFT JOINS out of Relations

// Code originally by Mike Beck

// If we can use Relations we could make some left joins.

// First find out if relations are available in this database.

// First we need the really needed Tables - those in TableList might still be

// all Tables.

if (isset($Field) && count($Field) > 0) {

// Initialize some variables

$tab_all = array();

$col_all = array();

$tab_wher = array();

$tab_know = array();

$tab_left = array();

$col_where = array();

$fromclause = '';

// We only start this if we have fields, otherwise it would be dumb

foreach ($Field as $value) {

$parts = explode('.', $value);

if (!empty($parts[0]) && !empty($parts[1])) {

$tab_raw = $parts[0];

$tab = str_replace('`', '', $tab_raw);

$tab_all[$tab] = $tab;

$col_raw = $parts[1];

$col_all[] = $tab . '.' . str_replace('`', '', $col_raw);

}

} // end while

// Check 'where' clauses

if ($cfgRelation['relwork'] && count($tab_all) > 0) {

// Now we need all tables that we have in the where clause

$crit_cnt = count($criteria);

for ($x = 0; $x < $crit_cnt; $x++) {

$curr_tab = explode('.', $Field[$x]);

if (!empty($curr_tab[0]) && !empty($curr_tab[1])) {

$tab_raw = $curr_tab[0];

$tab = str_replace('`', '', $tab_raw);

$col_raw = $curr_tab[1];

$col1 = str_replace('`', '', $col_raw);

$col1 = $tab . '.' . $col1;

// Now we know that our array has the same numbers as $criteria

// we can check which of our columns has a where clause

if (!empty($criteria[$x])) {

if (substr($criteria[$x], 0, 1) == '=' || stristr($criteria[$x], 'is')) {

$col_where[$col] = $col1;

$tab_wher[$tab] = $tab;

}

} // end if

} // end for

// Cleans temp vars w/o further use

unset($tab_raw);

unset($col_raw);

unset($col1);

if (count($tab_wher) == 1) {

// If there is exactly one column that has a decent where-clause

// we will just use this

$master = key($tab_wher);

} else {

// Now let's find out which of the tables has an index

// (When the control user is the same as the normal user

// because he is using one of his databases as pmadb,

// the last db selected is not always the one where we need to work)

PMA_DBI_select_db($db);

foreach ($tab_all as $tab) {

$ind_rs = PMA_DBI_query('SHOW INDEX FROM ' . PMA_backquote($tab) . ';');

while ($ind = PMA_DBI_fetch_assoc($ind_rs)) {

$col1 = $tab . '.' . $ind['Column_name'];

if (isset($col_all[$col1])) {

if ($ind['non_unique'] == 0) {

if (isset($col_where[$col1])) {

$col_unique[$col1] = 'Y';

} else {

$col_unique[$col1] = 'N';

}

} else {

if (isset($col_where[$col1])) {

$col_index[$col1] = 'Y';

} else {

$col_index[$col1] = 'N';

}

} // end while (each col of tab)

} // end while (each tab)

// now we want to find the best.

if (isset($col_unique) && count($col_unique) > 0) {

$col_cand = $col_unique;

$needsort = 1;

} elseif (isset($col_index) && count($col_index) > 0) {

$col_cand = $col_index;

$needsort = 1;

} elseif (isset($col_where) && count($col_where) > 0) {

$col_cand = $tab_wher;

$needsort = 0;

} else {

$col_cand = $tab_all;

$needsort = 0;

}

// If we came up with $col_unique (very good) or $col_index (still

// good) as $col_cand we want to check if we have any 'Y' there

// (that would mean that they were also found in the whereclauses

// which would be great). if yes, we take only those

if ($needsort == 1) {

foreach ($col_cand as $col => $is_where) {

$tab = explode('.', $col);

$tab = $tab[0];

if ($is_where == 'Y') {

$vg[$col] = $tab;

} else {

$sg[$col] = $tab;

}

if (isset($vg)) {

$col_cand = $vg;

// Candidates restricted in index+where

} else {

$col_cand = $sg;

// None of the candidates where in a where-clause

}

// If our array of candidates has more than one member we'll just

// find the smallest table.

// Of course the actual query would be faster if we check for

// the Criteria which gives the smallest result set in its table,

// but it would take too much time to check this

if (count($col_cand) > 1) {

// Of course we only want to check each table once

$checked_tables = $col_cand;

foreach ($col_cand as $tab) {

if ($checked_tables[$tab] != 1) {

$tsize[$tab] = PMA_Table::countRecords($db, $tab, false);

$checked_tables[$tab] = 1;

}

$csize[$tab] = $tsize[$tab];

}

asort($csize);

reset($csize);

$master = key($csize); // Smallest

} else {

reset($col_cand);

$master = current($col_cand); // Only one single candidate

}

} // end if (exactly one where clause)

$tab_left = $tab_all;

unset($tab_left[$master]);

$tab_know[$master] = $master;

$run = 0;

$emerg = '';

while (count($tab_left) > 0) {

if ($run % 2 == 0) {

PMA_getRelatives('master');

} else {

PMA_getRelatives('foreign');

}

$run++;

if ($run > 5) {

foreach ($tab_left as $tab) {

$emerg .= ', ' . PMA_backquote($tab);

unset($tab_left[$tab]);

}

} // end while

$qry_from = PMA_backquote($master) . $emerg . $fromclause;

} // end if ($cfgRelation['relwork'] && count($tab_all) > 0)

} // end count($Field) > 0

// In case relations are not defined, just generate the FROM clause

// from the list of tables, however we don't generate any JOIN

if (empty($qry_from) && isset($tab_all)) {

$qry_from = implode(', ', $tab_all);

}

// Now let's see what we got

if (!empty($qry_from)) {

echo 'FROM ' . htmlspecialchars($qry_from) . "\n";

}

// 3. WHERE

$qry_where = '';

$criteria_cnt = 0;

for ($x = 0; $x < $col; $x++) {

if (!empty($curField[$x]) && !empty($curCriteria[$x]) && $x && isset($last_where) && isset($curAndOrCol)) {

$qry_where .= ' ' . strtoupper($curAndOrCol[$last_where]) . ' ';

}

if (!empty($curField[$x]) && !empty($curCriteria[$x])) {

$qry_where .= '(' . $curField[$x] . ' ' . $curCriteria[$x] . ')';

$last_where = $x;

$criteria_cnt++;

}

} // end for

if ($criteria_cnt > 1) {

$qry_where = '(' . $qry_where . ')';

}

// OR rows ${'cur' . $or}[$x]

if (!isset($curAndOrRow)) {

$curAndOrRow = array();

}

for ($y = 0; $y <= $row; $y++) {

$criteria_cnt = 0;

$qry_orwhere = '';

$last_orwhere = '';

for ($x = 0; $x < $col; $x++) {

if (!empty($curField[$x]) && !empty(${'curOr' . $y}[$x]) && $x) {

$qry_orwhere .= ' ' . strtoupper($curAndOrCol[$last_orwhere]) . ' ';

}

if (!empty($curField[$x]) && !empty(${'curOr' . $y}[$x])) {

$qry_orwhere .= '(' . $curField[$x]

. ' '

. ${'curOr' . $y}[$x]

. ')';

$last_orwhere = $x;

$criteria_cnt++;

}

} // end for

if ($criteria_cnt > 1) {

$qry_orwhere = '(' . $qry_orwhere . ')';

}

if (!empty($qry_orwhere)) {

$qry_where .= "\n"

. strtoupper(isset($curAndOrRow[$y]) ? $curAndOrRow[$y] . ' ' : '')

. $qry_orwhere;

} // end if

} // end for

if (!empty($qry_where) && $qry_where != '()') {

echo 'WHERE ' . htmlspecialchars($qry_where) . "\n";

} // end if

// 4. ORDER BY

$last_orderby = 0;

if (!isset($qry_orderby)) {

$qry_orderby = '';

}

for ($x = 0; $x < $col; $x++) {

if ($last_orderby && $x && !empty($curField[$x]) && !empty($curSort[$x])) {

$qry_orderby .= ', ';

}

if (!empty($curField[$x]) && !empty($curSort[$x])) {

// if they have chosen all fields using the * selector,

// then sorting is not available

// Robbat2 - Fix for Bug #570698

if (substr($curField[$x], -2) != '.*') {

$qry_orderby .= $curField[$x] . ' ' . $curSort[$x];

$last_orderby = 1;

}

} // end for

if (!empty($qry_orderby)) {

echo 'ORDER BY ' . htmlspecialchars($qry_orderby) . "\n";

}

/**

* Displays the footer

require_once './libraries/footer.inc.php';

一键复制

编辑

Web IDE

原始数据

按行查看

历史

Alex刘DS爱好者

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫