sql question

ChicoMendez · 12-10-2009 2:03pm #1

HI GUYS,

im doing a bit of coding here you can see im searching a table for the word 'male' however it brings back items that have female also.

what do i have to do so as it will bring back only items with 'male' ?

thanks alot

SELECT * FROM mmodels WHERE (name LIKE '%male%' OR tags LIKE '%male%' OR descr LIKE '%male%') AND published=1

Eoin · 12-10-2009 2:04pm

and not like '%female%'

seamus · 12-10-2009 2:07pm

Use AND NOT LIKE '%female%' to exclude those results.

A tad inefficient, but it depends on how big the table is and what type of field you're searching on. A fulltext index & search may be better.

Eoin · 12-10-2009 2:12pm

How is this being used? Would it make sense to have an integer column that indicates what gender the record is for (e.g. male/female clothes)?

ChicoMendez · 12-10-2009 2:19pm

hi guys,

thanks for replies

well i just use male and female as examples

it could well be a search for

man - which would bring back entries with 'woman' and 'mandatory' etc

any way to make a specifc search just to bring back exactly what im looking for ?

thks

Eoin · 12-10-2009 2:26pm

Could you search for "% man %" (note the spaces)?

What type of database are you using? You might be able to use regular expressions in the query to match an exact word.

seamus · 12-10-2009 2:26pm

Without using fulltext, yes you can, using regular expressions.

From what I can gather so, you're trying to find occurences of a word on it's own, and not as part of another word?

In that case, there are a number of possibilities:

1. The word is preceded by text and one or more whitespace characters.
2. The word appears at the start of the text.
3. The word is followed by one or more whitespace characters and other text.
4. The word appears at the end of the text.
5. The text only contains the word you're looking for (technically 2 + 4).

Don't have time myself to construct it, but someone else may be able to help:
http://dev.mysql.com/doc/refman/5.1/en/regexp.html

Eoin · 12-10-2009 2:36pm

You can try something like this, but I don't have time to test it so it will most likely need tweaking (it's a modified version of a query I found on another site).

SELECT 
     *
FROM 
     myTable
WHERE
    myCol LIKE '%[^a-z0-9]man[^a-z0-9]%' OR
    myCol LIKE 'man[^a-z0-9]%' OR
    myCol LIKE '%[^a-z0-9]man' OR
    myCol  = 'man'

SunshineCyclist · 12-10-2009 3:46pm

Soo much easier and efficient for sql engine to have a gender flag in the table. This should be stored as a bit or int;

0-Male
1-Female
2-Undecided :-)

Eoin · 12-10-2009 3:48pm

SunshineCyclist wrote: »

Soo much easier and efficient for sql engine to have a gender flag in the table. This should be stored as a bit or int;

0-Male
1-Female
2-Undecided :-)

I suggested that, but he just used male/female as an example. It sounds like he can't normalise the data that much.

Liam Byrne · 12-10-2009 6:38pm

Unfortunately for you, both examples involve the same substring, either "%male" or "%man"

How much control do you have over what's already in the fields ?

If you have control, you could have the tags as a "CSV"-style field (ensuring that you start and end it with a ",") in which case you could search for "%,male,%"

Writing to that field would then be
$fieldValue=",".implode(",",$tags).",";

amen · 12-10-2009 7:02pm

If you have control, you could have the tags as a "CSV"-style field (ensuring that you start and end it with a ",") in which case you could search for "%,male,%"

if are going to do that then you might be better creating a lookup table and storing unique codes such for each item i.e. 1 for man, 2 for male etc

Maybe a table redesign is in order#?

sql question

Comments