Menu
▾
▴
#2767 (ok 3.0.1) SQL error after sorting a subset
Hello
starting with this simple table ...
CREATE TABLE \`users\` \(
\`name\` varchar\(16\) collate latin1\_german2\_ci NOT NULL,
\`id\_for\` int\(11\) NOT NULL,
\`id\` int\(10\) unsigned NOT NULL auto\_increment,
PRIMARY KEY \(\`id\`\)
\) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1\_german2\_ci;
INSERT INTO \`users\` VALUES\('alpha', 17, 1\);
INSERT INTO \`users\` VALUES\('beta', 17, 2\);
INSERT INTO \`users\` VALUES\('gamma', 18, 3\);
INSERT INTO \`users\` VALUES\('delta', 19, 4\);
I dipslayed a subset by searching (via "Search" button) for rows with `id_for` = 17:
SELECT \*
FROM \`users\`
WHERE \`id\_for\` =17
LIMIT 0 , 50
Now every attempt to sort resultant subset by clicking on column names throws a SQL-error:
Error
SQL query: Documentation
SELECT \*
FROM \`users\`
WHERE \`id\_for\` = 17ORDER
BY \`users\`.\`name\` ASC
LIMIT 0 , 50
MySQL said: Documentation
\#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BY \`users\`.\`name\` ASC
LIMIT 0, 50' at line 1
Cheers,
Peter
Discussion
Oops sorry:
* phpMyAdmin Version information: 3.0.0
* MySQL Server 5.0.51
* MySQL charset: UTF-8 Unicode (utf8)
* Apache 2.2.9
* PHP Version 5.2.6-3
Peter
This also happens to me on 3.0.0 and I tested it on http://pma.cihar.com/TESTING/ SVN version 11626 and it still happens.
Cheers,
Chris
This also happens to me on 3.0.0 and I tested it on http://pma.cihar.com/TESTING/ SVN version 11626 and it still happens.
Cheers,
Chris
Modified: branches/QA_3_0/phpMyAdmin/libraries/display_tbl.lib.php
--- branches/QA_3_0/phpMyAdmin/libraries/display_tbl.lib.php 2008-10-01 20:48:40 UTC (rev 11626)
+++ branches/QA_3_0/phpMyAdmin/libraries/display_tbl.lib.php 2008-10-02 16:55:44 UTC (rev 11627)
@@ -466,6 +466,10 @@
if (isset($analyzed_sql[0]['unsorted_query'])) {
$unsorted_sql_query = $analyzed_sql[0]['unsorted_query'];
}
+ // Handles the case of multiple clicks on a column's header
+ // which would add many spaces before "ORDER BY" in the
+ // generated query.
+ $unsorted_sql_query = trim($unsorted_sql_query);
// sorting by indexes, only if it makes sense (only one table ref)
if (isset($analyzed_sql) && isset($analyzed_sql[0]) &&
@@ -765,9 +769,9 @@
// If it contains one, it's probably a function column
// like 'COUNT(`field`)'
if (strpos($name_to_use_in_sort, '(') !== false) {
- $sort_order = 'ORDER BY ' . $name_to_use_in_sort . ' ';
+ $sort_order = ' ORDER BY ' . $name_to_use_in_sort . ' ';
} else {
- $sort_order = 'ORDER BY ' . $sort_tbl . PMA_backquote($name_to_use_in_sort) . ' ';
+ $sort_order = ' ORDER BY ' . $sort_tbl . PMA_backquote($name_to_use_in_sort) . ' ';
}
unset($name_to_use_in_sort);