PHP / mySQL help... queries

[Monkee of evil]

i'm writing a free-for-all style link management system for my website. What i'm working on right now is adding links to the system. I can already add Catagories and subCatagories. On the add a link page I have 2 dropdown boxes. One of them displays main catagories, the other displays subcatagories. What I want to do is either

1: only display subcatagories that go with the main catagory selected in the main catagory dropdown box

or

2: Do away with the main dropdown box and use an SQL query to determine which subCats go with which main cat. and display is something like

MainCat subCat
Bit Torrent - Trackers
Bit Torrent - Clients

the only problem is that I need to pass along several variables about which catagory you've selected and as far as I know / can remember you can only have one value="whatever" tags inside of a <option> tag. Am i wrong or how should i go about doing this?

If anyone wants to see code / db structure just leme know and i'll show it to ya...

www.napjunk.net/links/links.php

edit 2: fixed that bug...back to my main question

EDIT by greffov: removed some obscene language from the title

[greffov]

IE only supports groups from version 6 I think. Mozilla however supports it like it should (it's a standard)
Example of optgroup:

<FORM METHOD="post" ACTION="bestemming">
<SELECT NAME="browser">
<OPTION VALUE="xx">Choose a browser</OPTION>
<OPTGROUP LABEL="Microsoft Internet Explorer">
<OPTION VALUE="msie3">Microsoft Internet Explorer 3</OPTION>
...
</OPTGROUP>
<OPTGROUP LABEL="Mozilla">
<OPTION VALUE="moz1">Mozilla 1</OPTION>
</OPTGROUP>
<OPTGROUP LABEL="Netscape Navigator">
<OPTION VALUE="nn3">Netscape Navigator 3</OPTION>
...
</OPTGROUP>
<OPTGROUP LABEL="Opera">
<OPTION VALUE="op3">Opera 3</OPTION>
...
</OPTGROUP>
<OPTION VALUE="different">Different browser</OPTION>
</SELECT>
</FORM>

Note that the groups can consist of multiple options:

<OPTGROUP LABEL="Netscape Navigator">
<OPTION LABEL="3" VALUE="nn3">Netscape Navigator 3</OPTION>
<OPTION LABEL="4" VALUE="nn4">Netscape Navigator 4</OPTION>
<OPTION LABEL="6" VALUE="nn6">Netscape Navigator 6</OPTION>
<OPTION LABEL="7" VALUE="nn7">Netscape Navigator 7</OPTION>
</OPTGROUP>

By using JavaScript you can dynamically add and remove elements from a drop down menu, to achieve the effect you spoke about first.

To get all results at once from the database assuming you have a table named groups and a subgroup table, where subgroups has a primary key contraint on the groups table you can simply get a full list in a statement like this:

SELECT *
    FROM groups, subgroups
    WHERE subgroups.groupid = groups.id
    ORDER BY groups.name, subgroups.name

Really straightforward I guess...

[Monkee of evil]

this is the db structure i have right now...could you elaborate a little on how i pull items from more than one table in one query? I'm relatively new to sql.

TABLE: link_cats_main

cid - int(11) - Catagory ID, primary key
ctitle varchar(255) - Catagory Title

TABLE: link_cats_sub
scid - int(11) - subcataory ID, primary key
owner_cat int(11) - owner catagory, foriegn key to cid in link_cats_main
c_title (varchar255) - catagory title.

Table: link_links

lid - int(11) - Link ID, primary key
ltitle - varchar(255) - Title for the link
lurl - varchar(255) - url
lcount - int(11) - number of times the link has been clicked
lmcat - int(11) - link main catagory, the MAIN catagory in which this link belongs
lscat - int(11) - link subcatagory, same as above except for the subcat.

[greffov]

SELECT link_cats_main.*, link_cats_sub.scid AS subid, link_cats_sub.c_title AS subtitle, link_links.lid AS linkid, link_links.ltitle AS linktitle, link_links.lurl AS linkurl FROM link_cats_main, link_cats_sub, link_links WHERE link_cats_main.cid = link_cats_sub.owner_cat AND link_cats_sub.scid = link_links.lscat

note that lmcat is not needed, and causes overhead (it needs to be assured lscat is a child of lmcat). Once can calculate the links of a category using the subcat too.

basic idea:
create a cartesian product of the three tables (FROM link_cats_main, link_cats_sub, link_links) and remove all rows that do not match your foreign key relation (the WHERE relation)

[Monkee of evil]

Originally posted by greffov
SELECT link_cats_main.*, link_cats_sub.scid AS subid, link_cats_sub.c_title AS subtitle, link_links.lid AS linkid, link_links.ltitle AS linktitle, link_links.lurl AS linkurl FROM link_cats_main, link_cats_sub, link_links WHERE link_cats_main.cid = link_cats_sub.owner_cat AND link_cats_sub.scid = link_links.lscat

note that lmcat is not needed, and causes overhead (it needs to be assured lscat is a child of lmcat). Once can calculate the links of a category using the subcat too.

basic idea:
create a cartesian product of the three tables (FROM link_cats_main, link_cats_sub, link_links) and remove all rows that do not match your foreign key relation (the WHERE relation)

cool beans, ima go play a game for a bit to give my mind a rest from coding. Learning a lot today, thanks

[greffov]

hum, you don't start with the easiest stuff imho

[Monkee of evil]

Originally posted by greffov
hum, you don't start with the easiest stuff imho

I've worked with databases before, just not mySQL. It's been over a year since I did any database work though. I've been "learning" php for about a year...it's not hard..just a matter of correct syntax