searching partial strings...

jkatcher

In my database, I'm storing 'favPlay' as a LONGTEXT structure.

In most cases, its somewhere between 1 and 200 words. I want to be able to search for any one of the words. here's my basic format for search : (w/ 'favPlay' as the variable name)

$query = "SELECT * from bands WHERE favPlay = '$favPlay' OR ('.+',$favPlay)";

if for example stored in bandA's favPlay column was ,"band1, band2, band3", and someone typed in "band1", I'd want bandA's name to come up....can you guide me?

njm

$query = "SELECT * from bands WHERE favPlay LIKE '%band1%'";

although it sounds like you could do with normalising your database structure. 😃

hth, njm

jkatcher

what does that mean? do tell, i want to learn!! and thank you...

LordShryku

LIKE is for comparison used with wildcards. The % operator is the wildcard. So what he post said
"Look for all fields in the table 'bands' where the field favPlay is like band1 with anything before or after it"

The operator can be controlled to look for things before or after it. If you wanted to look for anything with 'band1' as the first word of the field, you could use
"LIKE 'band1%'"
and for anything with 'band1' as the last word in the field
"LIKE '%band1'

So for comparisons with wildcards, you should use LIKE instead of =

jkatcher

i got that part...but what does normalise mean in terms of my database?

LordShryku

No way I can really tell what he's getting at without knowing more about your database, but I assume he's hinting to the possibilty of you not utilizing the full potential of a RDMS. There was a small discussion about that here

cwissy

Normalising in a nutshell: it means to only store 1 piece of information in 1 column in 1 table.

e.g. a band can be categorised into any of a number of different forms of music. To represent this in a db you could have 1 table with a column called 'category' that had a delimeted list of categories in it for each band. This would be against the rules of normalisation.

To normalise your data you would split the above example into 3 tables.

table1 "band"
col1 "band_id"
col2 "band_name"

table2 "category"
col1 "cat_id"
col2 "category_name"

table3 "band_category_map"
col1 "map_id"
col2 "band_id"
col3 "cat_id"

Then you can find all the categories that a band is known for like so:

"Select category_name from category,band_category_map where band_id=$band_id"

and you could similarly select all bands in a certain category like:

"Select band_id,band_name from bands, band_category_map where cat_id=$cat_id"

et voila! you have normalised data that is easily and quickly accesable (and is easily indexed by your database using the unique "id" column.