0

I am trying to run a query that has a where clause with a string from a column of type VARCHAR(50) through PHP, yet for some reason it does not work in either PHP or MySQLWorkbench. My database looks like:

Database Picture:

database picture

The table title is 'paranoia' where the column 'codename' is VARCHAR(50) and 'target' is VARCHAR(50). The query I am trying to run takes the form, when searching for a codename entry clearly named '13Brownie' with no spaces, as follows:

UPDATE paranoia SET target='sd' WHERE codename='13Brownie'

Yet for some reason passing a string to the argument for codename is ineffective. The WHERE clause works when I do codename=7 or codename=3 and returns those respective integer codenames, and I can do codename=0 to get all the other lettered codenames. The string input works in neither MySQLWorkbench or the PHP script I will be using to update such selected rows, but again the integer input does.

It seems like the WHERE clause is only taking the integer values of my string input or the column is actually made up of the integer values of each entry, but the column codename is clearly defined as VARCHAR(50). I have been searching for hours but to no avail.

Improve this question

2 Answers 2

Reset to default
1

It is likely that there are white-space characters in the data. Things to try:

  1. SELECT * FROM paranoia WHERE codename like '13%'
  2. SELECT * FROM paranoia WHERE codename = '13Brownie '
  3. SELECT codename, LEN(codename) FROM paranoia
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Option 1 works, but my only concern is when one entry has the same starting characters used in the LIKE statement (for example entries where codename=3 and codename=315). When I do Option 3, it shows all codename entries being 2 characters longer than how they appear (LENGTH(7) = 3 for example) yet when I use TRIM(codename)='7' or codename='7 ' it still does not work.
After a little fiddling I found a way I can use Option 1 in PHP. I just have to get all codenames where the first characters are the desired codename to find and then the row I want will be the first one returned in the fetch_all() array. Thanks for the help.
No worries. I should have clarified: the purpose of all three options was to diagnose the problem, not serve as a solution. So there's definitely some sort of whitespace causing problems there. Given that every string is two characters longer than expected, perhaps it is a newline marker. (i.e. carriage return, line feed).
Next suggestion: inspect some of the individual characters. Something like: SELECT ASCII(RIGHT(codename,1)) FROM paranoia Then after that try some techniques to ignore the carriage returns (if thats what they are), such as: lists.mysql.com/mysql/182689 . Or better yet, try to clean up the data before it is inserted into the table if possible.
0

VARCHAR(10) is a valid type to accept a string of at most 10 characters. I think this can possibly happen because of a foreign key constraint enforced with another table. check if you have this constraint using the "relation view" if you are on phpmyadmin.

Improve this answer

1 Comment

I changed it to VARCHAR(50), but I have no other tables and am not on PHPMyAdmin so I'm pretty certain it is not due to a foreign key constraint.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.