Solved

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

Posted on 2006-07-23
15
234 Views
Last Modified: 2013-12-24
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
0
Comment
Question by:RollinNow
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
15 Comments
 
LVL 7

Expert Comment

by:aseusainc
ID: 17166651
<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.
0
 

Author Comment

by:RollinNow
ID: 17169670
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
0
 
LVL 7

Expert Comment

by:aseusainc
ID: 17170087
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%'
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

Author Comment

by:RollinNow
ID: 17170881
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
0
 
LVL 7

Expert Comment

by:aseusainc
ID: 17171112
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...
0
 

Author Comment

by:RollinNow
ID: 17171513
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
0
 
LVL 7

Expert Comment

by:aseusainc
ID: 17171811
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
0
 

Author Comment

by:RollinNow
ID: 17172110
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
0
 
LVL 7

Expert Comment

by:aseusainc
ID: 17172439
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>
0
 

Author Comment

by:RollinNow
ID: 17172669
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
0
 
LVL 7

Accepted Solution

by:
aseusainc earned 500 total points
ID: 17172944
my bad...getting late...I never sleep!
Try changing this section like so:


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

Author Comment

by:RollinNow
ID: 17173033
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
0
 

Author Comment

by:RollinNow
ID: 17173390
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!
0
 

Author Comment

by:RollinNow
ID: 17173428
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

0
 
LVL 7

Expert Comment

by:aseusainc
ID: 17174604
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>
0

Featured Post

Portable, direct connect server access

The ATEN CV211 connects a laptop directly to any server allowing you instant access to perform data maintenance and local operations, for quick troubleshooting, updating, service and repair.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
Lease-to-own eliminates the expenditure of hardware replacement and allows you to pay off the server over time. Usually, this is much cheaper than leasing servers. Think of lease-to-own as credit without interest.
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question