Hi there,

I'm hoping someone can point me in the right direction. Here's what I would like to do.

I have a database of links to web pages. Each record contains the URL and an associated keyword

For example

URL : www.butter.com
KEYWORD: butter

I would like to take this database and, with some kind of text replace, go through all the content in my site, replacing the instances of the KEYWORD ( eg. butter ), with a link to the URL.

Ideally, I would want to do this LIVE, on the server - as new data is entered, it is checked against the keyword database, and the appropriate links are inserted. However, I realize that this might be unrealistic. I am thinking that there may be some add in Access module ( I maintain the database in Access ) that I could run, and then reupload the Access database to the server.

Or, maybe there some other way to do this. Just wanted to put the problem out there, to see if anyone could help.


Much appreciated,

John Silver

something like that would totally drag down any web app if done live... the only way to do something like this is to write a script, and run it from time to time...

if you want some pseudo code, just ask (sorry, don't have time to write the whole thing for ya <img src=../images/dmxzone/forum/icon_smile_wink.gif border=0 align=middle> )

Joel Martinez [ ]
----------
E-Commerce Concepts with Ultradev...pre-order yours at
www.basic-ultradev.com/ecomm_concepts/

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
if you want some pseudo code, just ask (sorry, don't have time to write the whole thing for ya <img src=../images/dmxzone/forum/icon_smile_wink.gif border=0 align=middle> )

<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

Some pseudo code would be terrific! Thanks! Should I look for it here?

well, I started to write the pseudo code, but I just couldn't resist trying to write it <img src=../images/dmxzone/forum/icon_smile_wink.gif border=0 align=middle>
it may need some tweaking of the keyword recordset because I couldn't remember if I had to do anything special to be able to move back to the beginning... so hopefully you can handle that... I made everything that you had to change into a variable at the top of the script to make it easier for you to use.
without further ado:<pre id=code><font face=courier size=2 id=code>&lt;%
dim conn, keywordSQL, itemSQL, keywordRS, itemRS, str, target
dim kw_table, kw_keyword, kw_url, i_table, i_prikey, i_article

kw_table = "KeywordTable" <font color=green>'table where the keywords reside</font id=green>
kw_keyword = "keyword" <font color=green>'name of the column holding the keywords</font id=green>
kw_url = "url" <font color=green>'name of the column holding the URL</font id=green>

i_table = "articles" <font color=green>'table where the articles are held</font id=green>
i_prikey = "articleID" <font color=green>'name of the column that holds the primary key for the article</font id=green>
i_article = "article" <font color=green>'name of the column that holds the article to be updated</font id=green>

target = "_new" <font color=green>'_new means the link will open a new window ... leave empty for the same window</font id=green>

set conn = server.createobject("adodb.connection"
<font color=green>'double check the name of the connection variable, and make sure that the include file is there (connections/yourconn.asp)</font id=green>
conn.open mm_yourConn_connection

KeywordSQL= "SELECT "& kw_keyword &", "& kw_url &" FROM "& kw_table
itemSQL = "SELECT "& i_prikey &", "& i_article &" FROM "& i_table

set kewordRS = conn.execute(KeywordSQL)
set itemRS = conn.execute(itemSQL)

<font color=green>'This will loop through all of the items (articles?)</font id=green>
do while not itemRS.eof
str = itemRS.Fields(i_article).value

do while not keywordRS.eof
str = replace(str, " " & trim(keywordRS(kw_Keyword)) & " ", "&lt;a href="""& keywordRS(kw_URL) &""" target="""& target &"""&gt;"& keywordRS(kw_Keyword) &"&lt;/a&gt;"
keywordRS.movenext
loop

keywordRS.movefirst

<font color=green>'Now update the current record with the new values</font id=green>
conn.execute "UPDATE "& i_table &" SET "& i_article &" = '"& str &"' WHERE "& i_prikey &" = "& itemRS.fields(i_prikey).value
itemRS.movenext
loop

keywordRS.close
set keywordRS = nothing
itemrs.close
set itemRS = nothing
conn.close
set conn = nothing
%&gt;</font id=code></pre id=code>Good luck, and tell me if it works

Joel Martinez [ ]
----------
E-Commerce Concepts with Ultradev...pre-order yours at
www.basic-ultradev.com/ecomm_concepts/

Wait, I fixed some of the logic... I made the keyword recordset into an array for better performance... and also changed the connection string variable to mm_theConn_<b>String</b> just remember to make sure the include file is there for the connection variable to be valid<pre id=code><font face=courier size=2 id=code>&lt;%
dim conn, keywordSQL, itemSQL, keywordRS, itemRS, str, target
dim kw_table, kw_keyword, kw_url, i_table, i_prikey, i_article
dim kw_array, max

kw_table = "KeywordTable" <font color=green>'table where the keywords reside</font id=green>
kw_keyword = "keyword" <font color=green>'name of the column holding the keywords</font id=green>
kw_url = "url" <font color=green>'name of the column holding the URL</font id=green>

i_table = "articles" <font color=green>'table where the articles are held</font id=green>
i_prikey = "articleID" <font color=green>'name of the column that holds the primary key for the article</font id=green>
i_article = "article" <font color=green>'name of the column that holds the article to be updated</font id=green>

target = "_new" <font color=green>'_new means the link will open a new window ... leave empty for the same window</font id=green>

set conn = server.createobject("adodb.connection"
<font color=green>'double check the name of the connection variable, and make sure that the include file is there (connections/yourconn.asp)</font id=green>
conn.open mm_yourConn_string

KeywordSQL= "SELECT "& kw_keyword &", "& kw_url &" FROM "& kw_table
itemSQL = "SELECT "& i_prikey &", "& i_article &" FROM "& i_table

set kewordRS = conn.execute(KeywordSQL)
kw_array = keywordRS.getRows
keywordRS.close
set keywordRS=nothing

max = ubound(kw_array,2)

set itemRS = conn.execute(itemSQL)

<font color=green>'This will loop through all of the items (articles?)</font id=green>
do while not itemRS.eof
str = itemRS.Fields(i_article).value

for i=0 to max
str = replace(str, " " & trim(kw_array(0,i)) & " ", "&lt;a href="""& kw_array(1,i) &""" target="""& target &"""&gt;"& kw_array(0,i) &"&lt;/a&gt;"
next


<font color=green>'Now update the current record with the new values</font id=green>
conn.execute "UPDATE "& i_table &" SET "& i_article &" = '"& str &"' WHERE "& i_prikey &" = "& itemRS.fields(i_prikey).value
itemRS.movenext
loop

itemrs.close
set itemRS = nothing
conn.close
set conn = nothing
%&gt;</font id=code></pre id=code>

Joel Martinez [ ]
----------
E-Commerce Concepts with Ultradev...pre-order yours at
www.basic-ultradev.com/ecomm_concepts/

Edited by - joelmartinez on 14 Dec 2001 18:41:17