Dreamweaver - Help for Normalization and complex query!

Author

Help for Normalization and complex query!

Jamal Dayes

2006-02-12, 6:42 pm

Hi, I have a query and a results page that i want to use to allow users to
search for CD's by subject type. The Relationship is M:N because One CD can
have many different subject types and one subject can below to many different
CD's

I have 3 tables set up as follows:

CDtable:
cdName
cdId

SubjectTable:
subName
subId

Relationship
subId
cdId

The query page dynamically produces the checkboxes in a list as pulled
directly from the subjectTable. this is so that the end user can add more
subject types to the table and they will automatically update the checkbox
list.
What I need to do is produce a query that will take the selected checkboxes
and match them against the relationship table to produce a list of all the CD's
that match "ANY" of the subject types that were searched.

This query has been racking my brains. Firstly because I assume I have to take
the checkbox data and covert it in a string as the field type is the same for
each subject type.

if(is_array($_POST))
$_POST=implode(",",$_POST);

something like this would do the trick.

I'm just finding it impossible to get the query right?

SELECT CDtable.cdName, SubjectTable.subjectName, Relationship * FROM CDtable,
SubjectTable, Relationship WHERE IN ('%%%s%%') ORDER BY CdName

%%%%s%% - being my form posted variable.

This is doing my head in just thinking about. Can anyone point me in the right
direction or tell me where i'm going wrong.

Kind Regards

Josie1one

2006-02-12, 6:43 pm

Post again if you're still stuck, my husband says he'll help you.
Jo

"Jamal Dayes" <webforumsuser@macromedia.com> wrote in message
news:dsktes$4jb$1@forums.macromedia.com...
> Hi, I have a query and a results page that i want to use to allow users to
> search for CD's by subject type. The Relationship is M:N because One CD
> can
> have many different subject types and one subject can below to many
> different
> CD's
>
> I have 3 tables set up as follows:
>
> CDtable:
> cdName
> cdId
>
> SubjectTable:
> subName
> subId
>
> Relationship
> subId
> cdId
>
> The query page dynamically produces the checkboxes in a list as pulled
> directly from the subjectTable. this is so that the end user can add more
> subject types to the table and they will automatically update the checkbox
> list.
> What I need to do is produce a query that will take the selected
> checkboxes
> and match them against the relationship table to produce a list of all the
> CD's
> that match "ANY" of the subject types that were searched.
>
> This query has been racking my brains. Firstly because I assume I have to
> take
> the checkbox data and covert it in a string as the field type is the same
> for
> each subject type.
>
> if(is_array($_POST))
> $_POST=implode(",",$_POST);
>
> something like this would do the trick.
>
> I'm just finding it impossible to get the query right?
>
> SELECT CDtable.cdName, SubjectTable.subjectName, Relationship * FROM
> CDtable,
> SubjectTable, Relationship WHERE IN ('%%%s%%') ORDER BY CdName
>
> %%%%s%% - being my form posted variable.
>
> This is doing my head in just thinking about. Can anyone point me in the
> right
> direction or tell me where i'm going wrong.
>
> Kind Regards
>

Michael Fesser

2006-02-12, 6:43 pm

..oO(Jamal Dayes)

Any chance to use a newsreader? The greedy webforum is eating your
posted code:

> if(is_array($_POST))
> $_POST=implode(",",$_POST);
>[...]

Micha

bregent

2006-02-12, 6:43 pm

You're missing the column name after the WHERE.

bregent

2006-02-12, 6:43 pm

You also don't have any joins. I'm not familiar with mysql syntax. Does it allow autojoins or something? Even if it does, I think you'd be better off with explicit joins.

forumnotifier

2006-02-12, 6:43 pm

You also don't have any joins. I'm not familiar with mysql syntax. Does it allow autojoins or something? Even if it does, I think you'd be better off with explicit joins.