Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3882
  • Last Modified:

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
0
Robert Davis
Asked:
Robert Davis
  • 7
  • 5
1 Solution
 
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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
 
Robert DavisAuthor Commented:
No other solutions or support was given. This was the only solution I found.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now