Solved

Creating a watch list with ASP and SQL

Posted on 2010-08-30
4
410 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
[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
  • 2
  • 2
4 Comments
 
LVL 143

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 143

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

756 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