MySQL SELECT Query question

[stevet70]

So far all the MySQL queries I've used that include WHERE have been nice and simple, such as:

SELECT * FROM subs WHERE exhibitions='yes'

But what happens if the field you're dealing with is populated by a series of keywords and you want to make a selection based on just one from that series?

I've started looking at the use of IN, eg:

SELECT * FROM subs WHERE keywords IN ('invite')

Is that the right approach? So far no results have been produced, though I'm not getting any error messages.

Also, how should the keywords be set up in the MySQL table, in terms of if they need to be separated by a comma and any specific settings for the field?


many thanks
Edit:
Also tried using LIKE within the SELECT statement but this only appeared to return results where the keyword was the only word in the field rather than being one of several.


Have also started to look at using MATCH() and AGAINST(), like this:

SELECT * FROM subs WHERE MATCH (notes) AGAINST ('invite')

but get "Can't find FULLTEXT index matching the column list"

??

[freecrm]

You're still asking for a complete match

Try using SQL wildcards

SELECT * FROM subs WHERE keywords LIKE '%$_Posted_Value%' ... BLAH DE BLAH

This will find the value submitted anywhere in the field.

Regarding your MySQL field structure, it depends on how you're going to be using it in the future.

If you are purely doing searches (as above) than commas are not required; however, if you want to echo the entire contents as a meta-tag for instance, it would be good practice to enter them with commas, as this makes life easier in the future.

In addition, commas will help if you want to use the EXPLODE php function in the future (for exporting for instance)

If you're going to be entering large amounts of keywords, you need a longtext type field, which is an infinately expanding field.

BTW - if you want to experiment with various SQL statements, and you have MS Access, you can create simple searches and see what the SQL query would be...

Or - you could use phpmyadmin to filter and view the SQL from there.

[stevet70]

I managed to get the select statement with "MATCH (notes) AGAINST ('invite')" in to work, after adjusting the notes field to be FULLTEXT with a little playing in phpMyAdmin, even though there are several words in each column it picks up those that contain it without any problems.

Will certainly have more of an explore though, thanks for your help