Link to home
Start Free TrialLog in
Avatar of RollinNow
RollinNow

asked on

How do I copy the first five elements of a list to a table?

How can I get the WebsiteInfo query below to select only the first five elements from the meta_keywords list? I get the error, "Incorrect syntax near '='."  But I'll bet my approach is flawed.  So, how do I write this to copy only the first five elements in the meta_keywords list?   I'm trying "cfloop index" but it's not working.

The list is formatted like this:  'apple,big apple,large yellow orange', etc.

<cfset area_id = '#url.area_id#'> <!--- Set from another page. Not important here --->

<!--- Get a list of websites using the same area_id and therefore the same pop_word and meta_keywords.
<cfquery name="WebsiteInfo" datasource="config">
    select website_id
    from website_info
    where website_id like 'a%' <!--- I just  narrowed the website list to speed up my tests --->
</cfquery>


<!--- Get comma delimited list from another table --->
<cfquery name="FileAreasInfo" datasource="Addfiles">
    select meta_keywords
    from areas
    where area_id = '#area_id#'
</cfquery>

<cfset meta_keywords = FileAreasInfo.meta_keywords>
<cfset number_of_keywords = ListLen(meta_keywords)> <!--- Not used in the query as yet --->

<!--- The following query is where I have the problem --->

<cfset pop_word_list = "">
<cfloop query="WebsiteInfo">
   <cfloop list="#meta_keywords#" index="word" delimiters=" ">
        <cfset pop_word_list = #meta_keywords#>
       <cfquery name="Update" datasource="Addfiles">
          update area_lookup
        <cfloop index="i" from="1" to="5"> <!--- This is where I attempted to get only the first 5 words but it's not working. --->
           set pop_word = '#ListGetAt(pop_word_list, i)#'
        </cfloop>
           where area_id = '#area_id#'
          and website_id like 'a%' <!--- I just  narrowed the website list to speed up my tests --->
       </cfquery>
   </cfloop>
</cfloop>

AREA_ID = Int Data Type, META_KEYWORDS and WEBSITE_ID = VarChar Data Type. Run on Win2000 server and CF5.

 - Georgia
Avatar of aseusainc
aseusainc

<cfset pop_word_list = #meta_keywords#>
  <cfloop index="i" from="1" to="5">
    <cfquery name="Update" datasource="Addfiles">
        UPDATE area_lookup
        SET pop_word = '#ListGetAt(pop_word_list, i)#'
        WHERE area_id = '#area_id#' AND website_id like 'a%'
     </cfquery>
  </cfloop>

The way you had it was setting the value of pop_word in a loop 5 times, so you'd only get the 5th value.
Avatar of RollinNow

ASKER

Aseusainc,

No, that didn't help. I get the same error:

ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 5: Incorrect syntax near '='.
SQL = "update area_lookup set pop_word = 'rare movie titles' set pop_word = 'dvd title' set pop_word = 'buy dvd' set pop_word = 'online dvd store' where area_id = '24062' and website_id like 'a%'"
Data Source = "ADDFILES"

You'll notice I also set delimiters=",".

It appears to me that set pop_word is being set as part of the string. Is that the problem?

I'm still open to ideas from anyone who can see what I'm doing wrong.

   Thanks,

   - Georgia
Looks like you didn't change the code...look at your error(reformatted to make it easier):

UPDATE area_lookup
SET pop_word = 'rare movie titles' set pop_word = 'dvd title' set pop_word = 'buy dvd' set pop_word = 'online dvd store'
WHERE area_id = '24062' AND website_id like 'a%'

The code I gave you should run 5 individual queries that will end up something like:

UPDATE area_lookup
SET pop_word = 'rare movie titles'
WHERE area_id = '24062' AND website_id like 'a%'

UPDATE area_lookup
SET pop_word = 'dvd title'
WHERE area_id = '24062' AND website_id like 'a%'

UPDATE area_lookup
SET pop_word = 'buy dvd'
WHERE area_id = '24062' AND website_id like 'a%'

UPDATE area_lookup
SET pop_word = 'online dvd store'
WHERE area_id = '24062' AND website_id like 'a%'
aseusainc,

You're right about my not changing the code correctly. At least I don't get the error so it must have been sloppy work on my part.

But only element 5 is being written to pop_word, nothing more. I checked the database directly. The query should save elements 1 through 5. I double checked that I was using the code you gave me, removing the apparently unnecessary <cfloop query="WebsiteInfo">. But I also tried it both ways, with and without, and check the database for changes, and each time only the element 5 was recorded into the pop_word column, and for all the associated website_id's beginning with "a".

Here's the code I used. I'm am certain it is now being run:

<cfset pop_word_list = #meta_keywords#>
  <cfloop index="i" from="1" to="5">
    <cfquery name="Update" datasource="Addfiles">
        UPDATE area_lookup
        SET pop_word = '#ListGetAt(pop_word_list, i)#'
        WHERE area_id = '#area_id#' AND website_id like 'a%'
     </cfquery>
  </cfloop>

Thanks for getting back to me quickly, for being patient, and for your help.

What do you suggest I do now?

  -  Georgia
The issue is that we're just updating the ame recordset over and over based on the where clause.

I'm kind of at a loss as to WHAT exactly you are trying to do, and what the recordsets of the other queries are...
Meta_keywords is a list of popular keywords stored in every area_id. There are thousands of area_id's. An example meta_keywords list looks like this:

retail dvd,dvd rental stores,online dvd stores,used dvd stores,store dvds,used dvd store,dvd rental store,direct dvd store,dvd retail stores,dvd player retail,dvd store new york,best dvd store,dvd movie stores,dvd retail sales,dvd video stores,dvd retail store,dvd video store,dvd movie store,dvd stores nyc,cd and dvd store,dvds in stores,dvd movies stores

I want to select only the first five elements from the meta_keywords list, not just element 5, and ignore the rest of the list. But the new list of the first 5 elements (pop_word_list) must loop through each website_id. The query is saving to each website_id, but only element 5.

In my example, the net result should be that all website domains which begin with the letter "a", where area_id=24062 will have the column pop_word contain this list:

retail dvd,dvd rental stores,online dvd stores,used dvd stores,store dvds

I have other plans down the road but for this test example, each pop_word column contains only the fifth element, 'store dvds'.

  - Georgia
SO you want all 5 words in 1 record row?  IE:

area_id    website_id    websitedomain     keywords
---------   -------------   ------------------    -----------
24062          213123          aaa.com         retail dvd,dvd rental stores,online dvd stores,used dvd stores,store dvds
24063          234234          abc.net           retail dvd,dvd rental stores,online dvd stores,used dvd stores,store dvds
That's close. The only difference is that the area_id will be the same like this:

area_id    website_id    websitedomain     keywords
---------   -------------   ------------------    -----------
24062          213123          aaa.com         retail dvd,dvd rental stores,online dvd stores,used dvd stores,store dvds
24062          234234          abc.net           retail dvd,dvd rental stores,online dvd stores,used dvd stores,store dvds

Each area_id will be passed manually from a calling page. In my example, the area_id is always the same.

In a separate question and another 500 points, I may be asking help on how to save the first 5 elements to the first website_id, the next 5 elements to the next website_id, until all keywords are used, then start over again. I  mention this only if it may be important.

But I'd like to get this one fixed and working first so I don't confuse myself, or anyone else.

  - Georgia
See if this flies with no errors.  Without having some tables to play with, I cant really test it.

<cfquery name="WebsiteInfo" datasource="config">
    select website_id
    from website_info
    where website_id like 'a%'
</cfquery>

<cfquery name="FileAreasInfo" datasource="Addfiles">
    select meta_keywords
    from areas
    where area_id = '#area_id#'
</cfquery>

<cfset fivekeywords="">
<cfloop from="1" to="5" index="i">
  <cfset fivekeywords=listappend(fivekeyword,ListGetAt(FileAreasInfo.meta_keywords,i)>
</cfloop>  
 
<cfoutput query="Websiteinfo">
UPDATE      area_loopkup
SET      pop_word = '#fivekeywords#'
WHERE      area_id = '#area_id#' AND website_id like 'a%'
</cfoutput>
aseusainc:

Getting closer!

The new code now outputs the correct elements but the update query is not saving that data to the database. I can't see where it's hanging. I checked the database pop_word and this code will not update it at all.

The result display page shows the following text repeated for each website_id:

UPDATE area_loopkup SET pop_word = 'retail dvd,dvd rental stores,online dvd stores,used dvd stores,store dvds' WHERE area_id = '24057' AND website_id = 'DOMAIN45' UPDATE area_loopkup SET pop_word = 'retail dvd,dvd rental stores,online dvd stores,used dvd stores,store dvds' WHERE area_id = '24057' AND website_id = 'DOMAIN46'  (etc, etc).

Here the entire code on that page, your version with a couple minor typos fixed. This does not update the column pop_word.

<cfset area_id = '#url.area_id#'>

<cfquery name="WebsiteInfo" datasource="config">
    select website_id
    from website_info
    where website_id like 'a%'
</cfquery>

<cfquery name="FileAreasInfo" datasource="Addfiles">
    select meta_keywords
    from areas
    where area_id = '#area_id#'
</cfquery>

<cfset fivekeywords="">
<cfloop from="1" to="5" index="i">
  <cfset fivekeywords=listappend(fivekeywords,ListGetAt(FileAreasInfo.meta_keywords,i))>
</cfloop>  

<cfoutput query="Websiteinfo">
      UPDATE  area_loopkup
      SET     pop_word = '#fivekeywords#'
      WHERE   area_id = '#area_id#'
      AND     website_id like 'a%'
</cfoutput>

<!--- Display results --->
<cfquery name="AreaInfo" datasource="Addfiles">
      select area_id, website_id, pop_word
      from area_lookup
      where area_id = '#area_id#'
      and website_id like 'a%'
</cfquery>
<cfoutput query="AreaInfo">
  <table width="100%" border="0" cellspacing="1" cellpadding="1">
    <tr #IIf(AreaInfo.CurrentRow MOD 2, DE('bgcolor="CCCCCC"'), DE('bgcolor="EEEEEE"'))#>
      <td width="40"><font size="1" face="Verdana, Arial, Helvetica, sans-serif">#area_id#</font></td>
      <td width="150"><font size="1" face="Verdana, Arial, Helvetica, sans-serif">#website_id#</font></td>
      <td><font size="1" face="Verdana, Arial, Helvetica, sans-serif">#pop_word#</font></td>
    </tr>
  </table>
</cfoutput>

  - Georgia
ASKER CERTIFIED SOLUTION
Avatar of aseusainc
aseusainc

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Here's the latest after executing your updated code:

Attribute set validation error in tag CFQUERY.
Required attributes: 'DBTYPE'. Optional attributes: 'BLOCKFACTOR,CACHEDWITHIN,CONNECTSTRING,DATASOURCE,DBNAME,DBPOOL,DBSERVER,DEBUG,MAXROWS,NAME,PASSWORD,PROVIDER,PROVIDERDSN,SQL,TIMEOUT,USERNAME'

I hope you get some sleep. But honestly, I'm happy you're here!

 - Georgia
After fixing the 'DBTYPE' error by adding the datasource:
<cfquery name="update_data" datasource="Addfiles">
it appears to be working. I'll double-check and get back to you. But it looks VERY promising!
aseusainc,

Hey, it works perfectly! Great job in wondering through my thoughts. I had written an earlier question about the same problem with much more detail but got no replies, thinking too much detail was the reason. So, I wrote this one and supplied more of the detail when requested.

I want to let you know that I sincerely appreciate your help.

I'm closing this with an A for your effort and urging you to look for a new question from me on this.

I will now need to divy up those keywords and distribute different sets of keywords per website until we reach the end of the meta_keywords list and start over until all the websites are filled, all from the same list but distributed in some kind of logical order so the words are more or less evenly distributed.

My task will be to see if I can do it myself and if not, find a way to make it clear how I need help.

- Georgia

To give you a head start on placement, you'll probably want to build the fivekeywords INSIDE that query output.  But instead of looping from1 to 5, you'll need to build up some logic, probably based on the currentrow or something like that...Let us know if you get stuck.



<cfoutput query="Websiteinfo">
  <cfset fivekeywords="">
  <cfloop from="1" to="5" index="i">
    <cfset fivekeywords=listappend(fivekeywords,ListGetAt(FileAreasInfo.meta_keywords,i))>
  </cfloop>

  <cfquery name="update_data">
     UPDATE  area_loopkup
     SET     pop_word = '#fivekeywords#'
     WHERE   area_id = '#area_id#'
     AND     website_id like 'a%'
  </cfquery>
</cfoutput>