Solved

Creating a watch list with ASP and SQL

Posted on 2010-08-30
4
408 Views
Last Modified: 2012-05-10
I'm trying to create a "watch" list where members of my site can add articles/jobs/etc. to their short-list. However, I'm struggling which approach to take on this.

I could create a watch-list table with a user_id and article_id and create a new record for each article a user ads to his/her watch list. Then loop through the table and include all the article_id's in an SQL IN statement executed on the article table.

So something like this:

Step 1:
set rs = ObjCn.excute("SELECT * FROM watch_list WHERE user_id='123'")
Do while not rs.eof
article_ids = article_ids & rs("article_id") & ","
rs.movenext : LOOP
rs.close : set rs = nothing

Step 2:
set rs = ObjCn.execute("SELECT * FROM article_table WHERE SQL IN ("&article_ids&")")
do while not rs.eof
rs("article_name") & "<br />"
rs.movenext : loop
rs.close : set rs = nothing

The problem I have with this method though is that I have to execute the SQL database twice for each list I create.

Does anybody have a more efficient method of creating a subscriber list that I can use?

Any help would be greatly appreciated!

PS: the code above is for illustration of method only so it would trow up errors if there were no records in any of the tables etc... just mentioning it before someone starts correcting the code itself.

0
Comment
Question by:BestAviation
  • 2
  • 2
4 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 33557429
you could do this:


set rs = ObjCn.execute("SELECT * FROM article_table WHERE SQL IN (select article_id  FROM watch_list WHERE user_id='123')")

Open in new window

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33557435
0
 

Author Comment

by:BestAviation
ID: 33581695
Hmm....

set rs = ObjCn.execute("SELECT * FROM article_table WHERE SQL IN (select article_id  FROM watch_list WHERE user_id='123')")

doesn't work. You have to include a WHERE EXISTS to use a sub query I found out.

Problem solved, and just in case someone else will use this question for their own research, the answer would be like this

set rs = ObjCn.execute("SELECT * FROM article_table WHERE EXISTS (SELECT article_id  FROM watch_list WHERE watch_list.article_id=article_table.id AND watch_list.user_id='123')")

Thanks for your help.
0
 

Author Closing Comment

by:BestAviation
ID: 33581709
Was missing the vital WHERE EXISTS but it got me on the right idea of using a sub-query.
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

914 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now