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

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
LVL 1
Robert DavisAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dave BaldwinFixer of ProblemsCommented:
Do any rows return if you do a "SELECT *"?
0
Robert DavisAuthor Commented:
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
Dave BaldwinFixer of ProblemsCommented:
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Robert DavisAuthor Commented:
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
Dave BaldwinFixer of ProblemsCommented:
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
Robert DavisAuthor Commented:
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
Dave BaldwinFixer of ProblemsCommented:
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
Robert DavisAuthor Commented:
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
Dave BaldwinFixer of ProblemsCommented:
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
Robert DavisAuthor Commented:
Yes, under browse every record has an edit button.

This is getting to be a real problem.  Anyone have an idea?
0
Robert DavisAuthor Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Robert DavisAuthor Commented:
No other solutions or support was given. This was the only solution I found.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.