sql question

ChicoMendez

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

and not like '%female%'

seamus

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

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

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

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

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

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

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

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

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

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#?