The SitePoint Forums have moved.

You can now find them here.
This forum is now closed to new posts, but you can browse existing content.
You can find out more information about the move and how to open a new account (if necessary) here.
If you get stuck you can get support by emailing forums@sitepoint.com

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Relationship database question

hey folks,
i m creating a database which get info into page. i m going good but now i got two tables and i studied a lot abt RDBMS. i get the theory. the theory is like this and correct me if i m wrong suppose i have 2 tables in a db, one name pages, one called menu, the menu table got fields if id which is primary and auto increment,a name which is varchar, and than there is second table called pages in which i have a id which is auto increment n primary key, and i made a name_id field for relation with the name field in menu table. now i make this a foreign key. thats all there is to RDBMS. now i have that in mind but i cant make it work as in inside php. i wanna pull whats in id 1 of menu table from menu_id which is text relating to id=1 how do i put it? so it goes on for other id's too.

The foreign key of pages should be associated to the primary key of menus. If you would like to index via a string rather then a integer then have the primary key of the menus table be a varchar. Either way though your basic table structure should be similar to the following.

menus

id

name

pages

id

menu_id (references menus(id))

Code SQL:

SELECT
t1.id AS page_id
,t2.id AS menu_id
,t2.name AS menu_name
FROM
pages AS t1
LEFTJOIN
menus AS t2
ON
t1.menu_id = t2.id
WHERE
t1.id =1

The above would retrieve the page and its associated menu where the page primary key is 1.