Solved

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

Posted on 2012-03-09
12
3,620 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
12 Comments
 
LVL 83

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 83

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
Are You Using the Best Web Development Editor?

The worlds of web hosting and web development are constantly evolving. Every year we see design trends change, coding standards adapt and new frameworks/CMS created. With such a quick pace of change it’s easy to get lost trying to keep up.

See if your editor made the list.

 
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 83

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
 
LVL 83

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 83

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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

630 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