Something weird wrong with my db

ajking

My database seems to be defying all logic. I can do "SELECT CustID FROM customers WHERE Last_Name LIKE 'Ham%' " and get the three customers whose last name is Hamilton. But I cannot get it to return any rows when I do "SELECT CustID FROM customers where First_Name LIKE 'John' " . . .

I use PHPMyAdmin and it displays all 620 records in the customers table complete with First_Name and Last_Name and everything else. It does a "SELECT FROM customers ORDER BY customers.First_Name ASC " without any problem, returning all 620 rows neatly sorted alphabetically by first name. It can tell me with a "SELECT COUNT( ) AS Rows , First_Name FROM customers GROUP BY First_Name ORDER BY First_Name " that I have 6 Andrews and 13 customers named Bob. But it will not return a single row when I do this: "SELECT * FROM customers WHERE First_Name = 'Bob' " . . . not a single row. No SQL error is reported, I just don't get the results that I should get.

What the heck can possibly be wrong?

sneakyimp

All those bob names could have a space in them. Also, your John query has no wildcard in it (% symbol) so there's really no reason to use LIKE rather than a simple = statement.

Maybe try this

SELECT * FROM `customers` WHERE `First_Name` LIKE '%Bob%'

ajking

Thanks, and I wish your suggestion had worked. But it didn't work either: Here's what I got from PHPMyAdmin when I ran that query:

MySQL returned an empty result set (i.e. zero rows). (Query took 0.0789 sec)
SQL query:
SELECT *
FROM customers
WHERE First_Name LIKE '%Bob%'
LIMIT 0 , 30

The = doesn't work, the % doesn't work, I just can't seem to get a proper result from a query that uses a WHERE on that particular field of the table. (I can get full results from a "SELECT First_Name FROM customers " with no WHERE). There are times when you need to use a WHERE, for example in the case of looking up a customer record where more than 2 have the same last name. So I am really frustrated. . .

sneakyimp

This does sound weird. Are you trying these queries we're talking about in phpMyAdmin or are you writing some PHP pages to query them? If you are writing PHP pages, you might be connecting to a different database or something. Beyond that, I don't know what the problem could be. Maybe there's some kind of mySQL config that would make the checks case-sensitive? Sorry I can't be of more assistance. You might want to try the (somewhat lame) forums at mysql.com or perhaps the google group for mysql.

ajking

Thanks for your help, I have been pulling my hair out in bafflement over this for more than a day now. I had been coding queries on this db in PHP, encountered this problem, and then have been running them in phpMyAdmin to try to figure out what was going on. And as you can see, I still don't understand what's going on. . .