Hello Jose,

I am helping another friend with a keyword application and need to
know how to do the following:

The form is setup to allow a keyword to be entered but we need to check whether
or not the user already submitted the keyword before allowing it to be entered
into the database. I am assuming we have to put the "keyword_count" query
in a list to compare it to or something?


Here is the code:


<!---get the users keywords------------------------------------->
<cfquery name="keyword_count" datasource="#ds#">
  SELECT * 
  FROM MasKeywords AS MK
  INNER JOIN MasProducts AS MP ON MP.ProductID = MK.ProductID
  WHERE MP.ProductID = '#form.PID#'
  AND MD.UserID = #session.UserID#
  AND MK.KeyActive = 1;
</cfquery>

<cfif #IsDefined("form.keyword")#>
<!---strip illegal character and lowercase keyword before submitting-->
<cfset TrimKeyword = '#trim('#form.Keyword#')#'>
<cfset StripKeyword = "#replace(variables.TrimKeyword,Chr(39),"", 'all')#">
<cfset LowerCaseKeyword  = "#lcase(StripKeyword)#">
</cfif>

<!---check to see if keyword already exists---->
<cfif #IsDefined("form.keyword")# AND "#LowerCaseKeyword#" IS NOT "??????">

<!---if passed, then create new
keyword------------------------------------------------------>
<cfquery name="create_keyword" datasource="#ds#" maxrows="1">
  INSERT INTO MasKeywords
  (
  ProductID,
  Keyword
  )
  VALUES
  (
  '#form.PID#',
  '#LowerCaseKeyword#'
  );
</cfquery>
</cfif>

---

Request for Question Clarification by joseleon-ga on 02 Jan 2004 09:29 PST

Hello, belac:
  Thanks for trust in me again!

Regarding your question, I think you just need to search in the
database for the keyword to see whether already exists or not, or
simply create an unique index in your database, so when the same
keyword is inserted for a product, then, an exception will be raised.
Please, tell me what do you think and I will answer this question.

Regards.

---

Clarification of Question by belac-ga on 02 Jan 2004 13:25 PST

Hello Jose,

What I need to do is query the database for the keywords so I have a list
to compare it to when the new keyword form is submitted. If their is an EXACT
match, then dont run the insert SQL statement, otherwise insert the new keyword.

I do not know how to build a list or array so I can compare it.

---

Clarification of Question by belac-ga on 03 Jan 2004 22:03 PST

Will you be able to do this for me?

Hello, belac:
  I cannot fully test this answer because I don't have all your table
structures, but in any case, don't hesitate to request for any
clarification until you get it working.
  
What you have to do it's to "search" for the keywords the user wants
to insert and then, check if the query has returned any results:
  

<cfif #IsDefined("form.keyword")#>
<!---strip illegal character and lowercase keyword before submitting-->
<cfset TrimKeyword = '#trim('#form.Keyword#')#'>
<cfset StripKeyword = "#replace(variables.TrimKeyword,Chr(39),"", 'all')#">
<cfset LowerCaseKeyword  = "#lcase(StripKeyword)#">
</cfif>  
  
<!---get the users keywords------------------------------------->
<cfquery name="keyword_count" datasource="#ds#">
  SELECT * 
  FROM MasKeywords AS MK
  INNER JOIN MasProducts AS MP ON MP.ProductID = MK.ProductID
  WHERE MP.ProductID = '#form.PID#'
  AND MK.keywords = '#LowerCaseKeyword#'
  AND MD.UserID = #session.UserID#
  AND MK.KeyActive = 1;
</cfquery>


<!---check to see if keyword already exists---->
<cfif #IsDefined("form.keyword")# AND "#LowerCaseKeyword#" AND
"#keyword_count.Recordcount# EQ 0">

<!---if passed, then create new
keyword------------------------------------------------------>
<cfquery name="create_keyword" datasource="#ds#" maxrows="1">
  INSERT INTO MasKeywords
  (
  ProductID,
  Keyword
  )
  VALUES
  (
  '#form.PID#',
  '#LowerCaseKeyword#'
  );
</cfquery>
</cfif>
  
That is, this is a three step process:
1. Strip any strange character from the keyword and make it lowercase
2. Perform a query to check if the keyword is in the database
3. Check if that query returned any results, and insert the new record
if the keyword don't exists

Don't copy and paste this code as is, be aware of the query, because
there is a line you may need to change:

  AND MK.keywords = '#LowerCaseKeyword#'
  
I assume that there is a field on the MasKeywords database, called
keywords, change it to fit your table structure. Please, don't
hesitate to request for any clarification.

Regards.

---

Request for Answer Clarification by belac-ga on 04 Jan 2004 09:17 PST

Hello Jose,

I can't run this part of the code, because you can't compare a sting
to a recordcount;
"<cfif "#LowerCaseKeyword#" AND "#keyword_count.Recordcount# EQ 0">

Example: 

If you search for a word like "red":

Error:
cannot convert the value "red" to a boolean

---

Clarification of Answer by joseleon-ga on 04 Jan 2004 09:32 PST

Hello, belac:
  Sorry, but I copied your original code and I have not been able to
test it, just leave that line this way:

<cfif "#keyword_count.Recordcount# EQ 0">

Regards.

---

Request for Answer Clarification by belac-ga on 04 Jan 2004 10:55 PST

Hello Jose,

Nevermind. I was being stupid and just had to move a query up before 
checking the recordcount =)

Thanks for your help. I might need something else but need to know when
you will be available because my questions are usually time critical. When
is the best time to post questions in order to receive a quick response?

Thanks

---

Clarification of Answer by joseleon-ga on 04 Jan 2004 10:58 PST

Hello, belac:
  Nice to hear it works. Regarding to post questions to me, I'm in the
GMT+1 time zone, so you can post questions in any daylight time on
that time zone. I will try to solve them as fast as possible.

Regards.