Query isn't working

[Squiggy2]

I haven't used the LIKE expression before, so I'm sure that's part of the issue, but anyway - Here's my query. I'm trying to get any row from the TBL_advedrtisers table where the customername is contained in the keywords column of the editorials table. The keywords column contains a string of customernames.

<cfquery name="CurrentArticle" datasource="mydb">

SELECT *

FROM editorials

WHERE editorials.thekey=#URL.ArticleID#

</cfquery>

<cfset thekeywords = #CurrentArticle.keywords#>

<cfquery name="SpecificAds" datasource="mydb">

SELECT * FROM TBL_advertisers

WHERE %TBL_advertisers.customername% LIKE '#thekeywords#'

</cfquery>

[ilssac]

Squiggy2 wrote:

The keywords column contains a string of customernames.

IF This means that you have a column with values like "customer1, customer2, customer3" then you are going to have to work pretty hard.

First you have to test if the customer name you are searching for is at the beginning of the list:

LIKE '#customerName#,%'

OR

The customer name is at the end of the list:

LIKE '%,#customerName#'

OR

The customer name is in the middle of the list:

LIKE '%,#customerName#,%'

OR

The customer is the only name in the list.

= '#customerName#'

OR

You could properly normalize your database design so that there is only one customer name in each row of a related "editorials-customers" table.

Then your query would simply be

SELECT e.aField, e.bField, c.cField
FROM editorials e INNER JOIN editorials-customers c ON (e.aKey = c.aKey)
WHERE c.customerName = '#customerName#'

[talofer99]

I'm not sure I undersyand what you are askeing since you didn't post any data or what you mean by "not working" ... but I will try to explain the LIKE.

if CurrentArticle.keywords was "Joe"

<cfset thekeywords = CurrentArticle.keywords>

and you had a row where TBL_advertisers.customername = "Joe, David, Rose" (im guessing how u enter the data .)

SELECT * FROM TBL_advertisers

WHERE TBL_advertisers.customername LIKE '%#thekeywords#%' - which is '%Joe%'

you would have got the row above in the query output...

BUT

if CurrentArticle.keywords was "Joe, Rose"

then

SELECT * FROM TBL_advertisers

WHERE TBL_advertisers.customername LIKE '%#thekeywords#%' - which is '%Joe,Rose%'

would have not match the row above ....

So if the Keywords are comma (or any other delemiter) seperated list you would like to do somthign like this :

SELECT * FROM TBL_advertisers

WHERE 1=1 (or any other always true ..)

And (

<cfloop list ="#thekeywords#" index="theKeyWord">

OR TBL_advertisers.customername LIKE '%#theKeyWord#%'

</cfloop>

)

[Squiggy2]

Thanks, I finally got it working.

[ilssac]

talofer99 wrote:

WHERE TBL_advertisers.customername LIKE '%#thekeywords#%' - which is '%Joe%'

you would have got the row above in the query output...

But it would ALSO get any row that might contain a names like "Joel" or any other string that contains the three characters "Joe".