SQL/DB Relationships/ASP: Listing specific features for projects.

Mikeybob

Hi again everyone.

I'm trying to list a series of projects, each with it's own list of features in an ASP page. The project div is a repeated region and the feature li is a nested repeated region.

There are two DB tables; projects and features. In the projects table there is a project_feature field. The feature table looks something like:

feature_ID
project_ID
feature_detail

The relationship between the tables is one to many tbl_project.project_ID to tbl_feature.project_ID.

The SQL is:

SELECT f.feature_ID, f.project_ID,f.feature_detail, p.project_ID
FROM tbl_feature f, tbl_project p
WHERE f.project_ID = p.project_ID

What should happen is each project should have a list of its features. What actually happens is that all the features are listed on the first project instance.

I can only assume there is a problem with the SQL.

Thanks for any advice

P.S. I realise I've been asking for a lot of help recently, so I'll pay it forward when I get a chance .

lightnin

that's actually a pretty simple sql statement. so far it looks ok...

is this problem happening in an asp page? if so, could you post your code? if not, maybe the data has a problem... you could post some of that too.

btw, if f.project_id is actually a foreign key and equal to p.project_id, there is no reason to select it from the table.

SELECT 
     f.feature_ID, 
     f.feature_detail, 
     p.project_ID 
FROM 
     tbl_feature f, 
     tbl_project p 
WHERE 
     f.project_ID = p.project_ID AND 
     p.project_id = ?

Mikeybob

Oh right. So the SELECT command is only for data that should be displayed? I thought it was for any data the query needs to handle.

What would go on the second half of the AND statement you mentioned? I'm still trying to get my head around SQL :P.

Queries:

[HTML]
<%
Dim rs_article
Dim rs_article_numRows

Set rs_article = Server.CreateObject("ADODB.Recordset")
rs_article.ActiveConnection = MM_ocvconn_STRING
rs_article.Source = "SELECT * FROM tbl_project ORDER BY project_ID DESC"
rs_article.CursorType = 0
rs_article.CursorLocation = 2
rs_article.LockType = 1
rs_article.Open()

rs_article_numRows = 0
%>
<%
Dim rs_features
Dim rs_features_numRows

Set rs_features = Server.CreateObject("ADODB.Recordset")
rs_features.ActiveConnection = MM_ocvconn_STRING
rs_features.Source = "SELECT f.feature_ID, f.project_ID,f.feature_detail, p.project_ID FROM tbl_feature f, tbl_project p WHERE f.project_ID = p.project_ID"
rs_features.CursorType = 0
rs_features.CursorLocation = 2
rs_features.LockType = 1
rs_features.Open()

rs_features_numRows = 0
%>
<%
Dim Repeat_article__numRows
Dim Repeat_article__index

Repeat_article__numRows = -1
Repeat_article__index = 0
rs_article_numRows = rs_article_numRows + Repeat_article__numRows
%>
<%
Dim Repeat_feature__numRows
Dim Repeat_feature__index

Repeat_feature__numRows = -1
Repeat_feature__index = 0
rs_features_numRows = rs_features_numRows + Repeat_features__numRows
%>
[/HTML]

HTML Structure:
[HTML]
<%
While ((Repeat_article__numRows <> 0) AND (NOT rs_article.EOF))
%>
<div class="article_set">
<div class="article_heading">
<a name="<%=(rs_article.Fields.Item("project_ID").Value)%>"><%=(rs_article.Fields.Item("project_title").Value)%></a>
</div>
<div class="article_content">
<a href="<%=(rs_article.Fields.Item("project_image_url").Value)%>" target="blank"><img src="images/thumbnails/<%=(rs_article.Fields.Item("project_thumbnail_url").Value)%>" alt=""></a>
<h1>Features:</h1>
<ul>
<%
While ((Repeat_feature__numRows <> 0) AND (NOT rs_features.EOF))
%>
<li><%=(rs_features.Fields.Item("feature_detail").Value)%></li>
<%
Repeat_feature__index=Repeat_feature__index+1
Repeat_feature__numRows=Repeat_feature__numRows-1
rs_features.MoveNext()
Wend
%>
</ul>
</div>
<div class="article_footer"></div>
</div>
<%
Repeat_article__index=Repeat_article__index+1
Repeat_article__numRows=Repeat_article__numRows-1
rs_article.MoveNext()
Wend
%>
[/HTML]

Relevant CSS:

[HTML]
.article_content {
width:565px;
height:264px;
}

.article_content img {
width:350px;
height:264px;
border-top:none;
border-right:1px solid #000;
border-bottom:none;
border-left:none;
margin: 0 15px 0 0;
float:left;
}

.article_content h1 {
font-family:Verdana, Arial, Helvetica, sans-serif;
font-size:12px;
color:#FFF;
margin:0 0 10px 0;
padding:10px 0 0 0;
}

.article_content ul {
padding:0 0 0 15px;
margin:0px;
list-style:square inside;

}

.article_content ul li {
font-family:Verdana, Arial, Helvetica, sans-serif;
font-size:12px;
color:#FFF;
}

.article_heading {
height:28px;
background-image:url(../images/navigation/background.jpg);
background-repeat:repeat-x;
padding:0px;
margin:0px;
border-bottom:1px solid #000;
}

.article_heading a {
font-family:Verdana, Arial, Helvetica, sans-serif;
font-size:15px;
font-weight:bold;
color:#FFF;
padding:6px;
margin:0px;
}

.article_footer {
width:565px;
height:28px;
background-color:#000;
clear:both;
}

.article_footer ul {
list-style:none;
padding:0px;
margin:0px;
clear:both;
}

.article_footer ul li {
display:inline;
margin:0px;
padding:0px;
}

.article_footer_header {
font-family:Verdana, Arial, Helvetica, sans-serif;
font-size:12px;
font-weight:bold;
color:#FFF;
margin:0px;
padding:0px;
}

.article_footer_subtext {
font-family:Verdana, Arial, Helvetica, sans-serif;
font-size:12px;
color:#FFF;
margin:0px;
padding:0px;
}

.article_set {
width:565px;
border: 1px solid #000;
background-color:#333;
margin-bottom:15px;
}

#article_wrapper {
width:565px;
margin-left:225px;
}
[/HTML]

Tables' Structure:

tbl_project:
project_ID (auto)
project_img (memo)
project_link (memo)
project_feature (text (50 char))

tbl_feature:
feature_ID (auto)
*project_ID (number)
feature_detail (memo)

The content is just simple text.

lightnin

the 'AND p.project_id = ?' chunk of my statement would allow you to select data for a *specific* project, where yours would get all data for all projects where the ids matched up. replace the ? with the id (i'm assuming these ids are ints) of the project you want to view. both are valid, just depends on what you want to do.

SELECT
    -- this is the select clause --
FROM
   -- this is the from clause --
WHERE
  -- this is the where clause --
GROUP BY/ORDER BY
   -- this is where aggregates go --

the fields listed in the select clause of a sql statement are the fields that will be returned in the result set. it isn't necessary to list all fields used in the where clause of the sql statement in the select clause. the where clause automatically has access to all the fields in each table listed in the from clause.

i'm guessing each project will usually have more than one feature_detail in the tbl_features table?

i'm still scanning your code for a problem....

Mikeybob

So is it either f.project_id = p.project_id, or p.project_id = ?, or is it both? I'm still a little confused.

Which ID would I use for the '?'? To be honest, I thought the f.project_id = p.project_id would work.

lightnin

here's the breakdown:

if you want a page to show something like this :

Project 1

---

1) feature of project 1
2) another feature of project 1
3) another feature of project 1

Project 2

---

1) feature of project 2
2) another feature of project 2
3) another feature of project 2

you could either adjust your query to pull back one result set with each project and their related features (best way) OR in your nested while loop there, query the database for each feature for the current project(not the best, but it'll work). in order to do this, you'll need to narrow down the result set by appending using the following statement:

SELECT 
     f.feature_ID, 
     f.feature_detail 
FROM 
     tbl_feature f 
WHERE 
     f.project_ID = 

basically you'd be saying to the db "go get me all the features that have this project id". you'd have to fill in that project id in your code. there's no reason at all to reference the project table this way.

of course, the more efficient way of doing this would be to say

SELECT  
     p.project_ID, 
     p.project_img, 
     p.project_link, 
     p.project_feature, 
     f.feature_detail 
FROM 
     tbl_project p 
RIGHT OUTER JOIN 
 tbl_feature f ON
 (p.project_id =  f.project_id)

this is saying to the db "get me all projects and each feature for each project, even if the project has no features in the table".
then you would technically only need one query and one while loop to display all the data for each project. i *think* think is what you were after correct?


of course you'll need to tweak this to make sure it's doing what you want. i dont have an mssql server at all to test this on but that last query ought to work just fine.

Mikeybob

Cool. I assume the second SQL statement deals with both with both the project details and the feature details. If so, how would I use the results of the query in ASP? I can't repeat the features region because the query is in use by the project repeated region.

lightnin

Sorry it's been a while since I replied.

If so, how would I use the results of the query in ASP?

You'd have to rewrite what you've posted before, sorry. is there any way to decouple the result set from the display? if you could do that, you wont have problems accessing data from 'live' result sets. it would also move your app a little further into the MVC pattern.