SQL command not filtering records although value is there in field

MySQL database
#1

I am working in cPanel > phpmyadmin

I just added one new column name “host_name” in table and there are hundreds records with value “mojotype.net” in host_name field. I am using following command to filter records:

SELECT * FROM visitors where host_name=‘mojotype.net’

Note: There are 60,000+ records in table.

I am NOT new to phpmyadmin and I work in routine but can’t figure out why sql command is not working for this field and showing records. Same command is working perfect with old fields like country,browser, etc. But not working with newly added field.

I also created INDEX for this field and I also Optimize table.

Any idea please?

#2

I’m confused. If there are hundreds of records with that value, why wouldn’t they be returned? Maybe narrow your search down by using other fields as well?

#3

That’s the reason I posted question here. I am not new to phpmyadmin. I run query with other field name and value and it worked as usual.

I am thinking to remove new field and then add again, might be there is an unknown glitch or reason.

Is “host_name” any system variable?

#4

Is the DB column name all lowercase like you show here?

#5

Yes, all lowercase. If there is any column name match problem then SQL should return error. Here in this case SQL just return with zero record.

sql-error
sql-error760×259 14.2 KB

#6

How did you place the values into that column? You likely have non-printing/white-space characters (new-line, tab, space) as part of the data, so that the value you are using in the query doesn’t match what’s stored in the column.

#7

First you showed the table name as visitors, now it shows as clicks. Which is it? In any case, it is most likely what @phdr said. There is nothing wrong with the query.

1 Like
#8

Thanks for the solution. It worked. :slight_smile:

Can you let me know the command to remove spaces from both sides of field so I may update this field again. Like

UPDATE ‘clicks’ SET host_name= ???

I mean update field after removing extra spaces…

#9

I tried following command to remove extra spaces in value of host_name field:

UPDATE clicks SET host_name=trim(host_name);

but this did not work…

Any idea please

#10

Then you have tabs or something other than regular white space. You need to figure out what it is so you know what to remove. You will also want to fix however the problem got there in the first place.

First you say the solution worked and then you say it doesn’t.

#11

Actually here is flow of the process.

  1. When I read your first reply then I immediately edited host_name field of one record and removed spaces. Then I accessed this record by sql command and it worked. Then I replied to your comment and Thanked You. SUCCESS.

  2. Then I tried to remove extra spaces of host_name field by using trim(host_name) but failed and I posted. FAILED

  3. Then I emptied all data in host_name field of 60,000+ records and used same php script by which earlier I inserted data. But this time I used trim(space, null, tab) to remove extra spaces before inserting data in host_name field and it worked. SUCCESS

Thanks for your help. :slight_smile:

1 Like
Sponsor our Newsletter | Privacy Policy | Terms of Service