Page 1 of 1

MYSQL problem

Posted: Wed Feb 26, 2003 1:45 pm
by kendall
Hi guys,

Here's wat's up.

what causes a select where statement to return 0 results when the where statement clearly states the correct information to yield a 1> result?

ok so i have a statement in which i have

SELECT * FROM products WHERE Code LIKE '% SRCHENGSUB %';

whose results return 0 which is wrong
yet the code is correct

I thought maybe i have an accidental space before or after the table's information but in double, triple checking i do not have a space the information is there i saw it with my own eyes but i keep getting 0 when i should get a result

what else should i be checking for

Kendall

Posted: Wed Feb 26, 2003 2:05 pm
by McGruff
In the code you posted you've got spaces before and after the search term. I think they are ignored, but you could double check just to be sure that's not the problem.

Also, you could maybe try RLIKE:

Exact match for $var:

RLIKE '[[:<:]]$var[[:>:]]'

Posted: Wed Feb 26, 2003 3:40 pm
by Rob the R
MySql shouldn't have any problem with the spaces, although the spaces will mean that your search term cannot be the very first or very last text in the CODE column. McGruff's suggestion should resolve this.

MYSQL queries using php

Posted: Thu Mar 06, 2003 11:35 am
by kendall
Hey guys,

Me again since we last spoke i was some how able to fix my little problem.

the information in the mysql tables we uploaded from a csv file and for some reason i dont know how to put it but it had a character or something that prevented queries from recognising the information correctly

it was working fine but now im using another part of the information and it is doing it again

example

in a column Category i have values called 'HOSTING'

on doing a query with Category = 'HOSTING'

i get 0 results

there seems to be something wrong with how the data was entered into the tables

can someone tell me some guidelines in uploading information from a csv file or anything that can help me find and eliminate this problem?

Kendall

Posted: Thu Mar 06, 2003 6:22 pm
by McGruff
You may already have done this but if you've got a mysql manager like phpMyadmin you can browse tables to check over what data has been entered where - and see if HOSTING really is in there.

Also, I'm guessing that you used a LOAD DATA INFILE query on the csv file: did you specify a FIELDS ENCLOSED BY clause? IF not, try FIELDS ENCLOSED BY '"' (see mysql manual for syntax). Again, phpMyadmin or similar lets you choose settings like this easily.

PS: I didn't explain too well before but LIKE %term% will find midterm, terminology etc as well as term; the RLIKE[] above will find only term.

Also. Fulltext indexes might be worth looking at - see mysql manual.