3

It is very strange what is happening, I have never seen this before and I am pretty familiar with mysql.

When searching a table using the phpMyAdmin table search feature, the result is empty no matter what I put. For example, searching 77 in the ID column returns empty result. However if I run an SQL query also in phpMyAdmin, and then there is the result. For example, select * from table1 where id = ‘77’;

What is even more strange is this only happens on one table, all other tables the search feature is working fine.

I tried repairing the table but empty result still occurs.

I couldn’t find this happening anywhere to any one online…..

Also restarted sql server.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
armd0202
  • 45
  • 1
  • 3
  • 7
  • Can you post your code? Obviously there is an error somewhere but finding it is difficult when we can't see the code. – Mark Byers Dec 23 '12 at 02:59
  • Make sure everything is spelled correctly and correctly capitalized. ID versus id versus Id, that sort of thing. – kermit Dec 23 '12 at 03:06
  • Make sure you are connecting to the *same* database! Often with these sorts of problems, you have different connection parameters for each case. Just because you get the same result for other tables doesn't mean anything - *they* may have the same data, but one table has different data – Bohemian Dec 23 '12 at 03:07
  • @Mark Byers - thank you however there is no code needed, I am talking about the simple search function of phpmyadmin, sql select works fine – armd0202 Dec 29 '12 at 23:24
  • @kermit - thank you, I have done this however I can rule out syntax error, I have debugged as far as I can, it seems to be a problem with phpMyAdmin – armd0202 Dec 29 '12 at 23:24
  • @Bohemian - thank you for your reply, I can confirm I am connected to the one and only correct database I have been using sql select queries to search for this one table. The problem continues. – armd0202 Dec 29 '12 at 23:25
  • Are you running mysql on Linux? If so, Linux is a case sensitive file system and if certain flags are enabled, table names must match the case if the file for the table. Check the information_schema.tables entry for the table and use exact case found there for the table name – Bohemian Dec 30 '12 at 00:50
  • @Bohemian Thank you for the reply, I am running linux. I use the exact case found for the SQL queries and it works. However the search function is still not working. – armd0202 Jan 04 '13 at 00:25

3 Answers3

0

If your table has a large number of fields an update via the phpMyAdmin interface can exceed the value for the PHP setting 'max_input_vars'. When this happens some of the internal form fields that phpMyAdmin is expecting to receive on the page your update is being posted to are truncated which causes phpMyAdmin to fail, unfortunately silently, and the page redirects with no warnings back to the blank search form. The default for 'max_input_vars' is 1000. I upped mine in my php.ini file to 5000 with no negative side affects and it solved this problem for me.

The setting 'max_input_vars' has a mode of PHP_INI_PERDIR so if you don't have access to your php.ini file then you may be able to set it in an .htaccess file, your httpd.conf file or your .user.ini (since PHP 5.3) file if you have one. I'm not sure exactly what code you would need for an htaccess file but the PHP code to do it is below.

ini_set('max_input_vars', '5000');

Hopefully that should get you started in the right direction.

Night Owl
  • 4,198
  • 4
  • 28
  • 37
  • thank you for the reply, however I also ruled this out. This table I am searching only has about 200,000 entries. Other tables with over four million perform a search fine. Furthermore, there is an error message, MySQL returned an empty result set (i.e. zero rows). ( Query took 0.0005 sec ) – armd0202 Jan 04 '13 at 00:29
  • My answer isn't related to the number of records in the table but to the number of fields / columns that the table has. What is your max_input_vars set to and how many fields are in your table? As I mentioned above if there are a lot of fields in the table and max_input_vars is too low some of your fields will get truncated. The error message you are getting is because phpMyAdmin is not receiving your search parameters and therefore MySQL is returning an empty result set because the query generated produces no results. – Night Owl Jan 12 '13 at 23:27
  • I just noticed that your problem is now solved and wasn't related to max_input_vars but to an ENUM data type issue. I had the exact symptoms you describe and mine was because of max_input_vars. I am leaving my previous comment here in case it helps someone else who had my problem and not your. – Night Owl Jan 12 '13 at 23:33
0

Are you using cPanel - if yes I just described how to fix the problem on cPanel forums:

http://forums.cpanel.net/f5/unable-use-phpmyadmin-search-users-table-313381.html

slawn
  • 16
0

Very easy. Go to the table and expose max number of rows as much as is showed in the dropdown. Then you are able to search per big pages. It doesnt fetch text through all the table. It plays only with a page of the table.

CodeToLife
  • 3,672
  • 2
  • 41
  • 29