Solved

phpMyAdmin says MySQL returned an empty result set (i.e. zero rows).

Posted on 2012-03-09
12
3,176 Views
Last Modified: 2012-03-17
I am unable to edit or update rows in a certain table due to this error.  It appears to happen on columns that have been changed to unicode and contain special characters such as & and \.  I know this because if I edit the SQL query and remove these columns I return the record.  I do see escape \ but I don't think a php mysql escape string was used.  I suppose the data charset could be wrong as well.  I don't really know.  The default for the table and database now are unicode.

How can I fix this?  Any suggestions would be greatly appreciated.

Regards,
Robert
0
Comment
Question by:Robert Davis
  • 7
  • 5
12 Comments
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 37703760
Do any rows return if you do a "SELECT *"?
0
 
LVL 1

Author Comment

by:Robert Davis
ID: 37703769
Yes, which is why I'm able to see the edit button in phpMyAdmin in the first place.  And what I was trying to say in my original post is that if I strip the "Where `column_full_of_junk` = "junk", this also works just fine.  It's when column_full_of_junk is included, which it is by default when using phpmyadmin and clicking edit, that this error occurs.

Thanks,
Robert
0
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 37703804
So far you are telling me that "Where `column_full_of_junk` = 'junk' " (note the single quotes) is not returning anything.  So the question is why do you think it should and what is 'junk' not matching up to?  Remember that Unicode and UTF-8 include ASCII for the first 127 characters.  Edit or add a row that has something ordinary in that column.  Use a simple word like 'junk' and your "Where `column_full_of_junk` = 'junk' " should match that.
0
 
LVL 1

Author Comment

by:Robert Davis
ID: 37703819
I'm not really sure what you're saying.  Take a look at phpMyAdmin and hit edit on a row.  It auto populates the SELECT statement including all the columns.  If the column that accepts input from the user contains a bunch of special characters, phpMyAdmin errors saying nothing returned, if it has regular characters, it pulls up the edit form.

Regards,
Robert
0
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 37703840
So you want me to Browse a table and click on the Edit icon for one of the rows?  What version of phpmyAdmin are you using?  I have at least 4 here.
0
 
LVL 1

Author Comment

by:Robert Davis
ID: 37703854
Version information: 3.4.9 w/ MySQL 5.1.56 but they all operate the same since 2.0, you hit edit and it automatically builds a select statement.  In my case, if that select statement includes special characters, it returns 0 rows instead of showing me the edit form.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 37703896
In phpmyAdmin versions 2.9 and 3.2, when I am 'Browsing' a table, the code behind the 'Edit' icon for the rows does not include a 'Where'.  Maybe it's because all my tables that I can find at the moment have int(11) auto-increment primary keys.  In addition, all 'special' characters are URL-encoded.  And phpMyAdmin pages are UTF-8 so it should be handling Unicode/UTF-8 data ok.

Is this column you're having trouble with the primary key for that table?  I'm trying to understand why phpMyAdmin would be putting it in a 'Where'.

What browser are you using?  I almost always use Firefox unless I have a reason or need for a different one.
0
 
LVL 1

Author Comment

by:Robert Davis
ID: 37704224
Removed the primary key, still a problem, and no the column with trouble isn't a PK column.  I'm on FF 10, my browser would not change server side code anyway...

How could you possibly know that your phpMyAdmin doesn't do a select with a WHERE clause? phpMyAdmin doesn't show you the query unless it fails, it just shows you the edit form.
0
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 37704384
Actually I'm sure it uses a WHERE clause because the 'Edit' link is passing the primary key along with 'SELECT * FROM ...'.   I'm still trying to figure out how and where you are finding an 'Edit' link that would cause the problem you describe.  Are you seeing it in the view created by clicking on Browse?
0
 
LVL 1

Author Comment

by:Robert Davis
ID: 37706008
Yes, under browse every record has an edit button.

This is getting to be a real problem.  Anyone have an idea?
0
 
LVL 1

Accepted Solution

by:
Robert Davis earned 0 total points
ID: 37711329
Two things, first, to edit the records I hit the checkbox instead of the edit button, and hit the change button at the bottom of the checkbox list options.  I made my changes and let the update query fail.  I then edited the query and removed all the columns from the default built WHERE clause aside from the record ID.  This then changed the record for me despite phpMyAdmin building a query it couldn't execute.

Second, what I edited out, which caused these issues, was a byte stream in a varchar column.  Rather than changing the varchar, I 64encoded the var in the application, and then compressed the encoded string.
0
 
LVL 1

Author Closing Comment

by:Robert Davis
ID: 37732259
No other solutions or support was given. This was the only solution I found.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
MSSQL 2008 with mySQL webservers 7 28
Instering to MySQL table 5 22
showing numeric numbers 2 11
PHP JSON Clean up 5 10
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
This article discusses four methods for overlaying images in a container on a web page
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now