SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Wizard nofel's Avatar
    Join Date
    Aug 2007
    Location
    Earth
    Posts
    1,766
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Question 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.
    All those who wander aren't lost.

  2. #2
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,184
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    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
      LEFT
      JOIN
          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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •