SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member adamdaughterson's Avatar
    Join Date
    Jan 2004
    Location
    Denver
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL Newbie Question Regarding Lookup Table...

    I've been reading Kevin Yank's Build Your Own ...
    and on page 90 (second ed.) he walks the reader through creating a Lookup Table. No problem. I understand how this could make the potential gain in performance by having the Categories and actual ID's of the Jokes, or whatever you want in the database. What I haven't grasped yet is the SQL statement that would match all existing JID's to a particular CID. There are really detailed and discriptive SQL statements for every other new concept in the book so far, but this part seems to have been glossed over. I assume there will be something like:
    Code:
    update set (JID, CID) = (Jokes.ID where...
    ...and that's where I realize that this won't work at all! There isn't anything in either CID or JID column's yet, so "WHERE CID = X" won't work untill something is in there. Do I have to manually enter JID/CID whenever I add a new joke? I would think that none of the guru's would use SQL if that were the case.
    Any help?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    "Do I have to manually enter JID/CID whenever I add a new joke?"

    i don't have the book, so it's hard for me to be sure what it actually says

    but from your description, i will assume that a joke can be in multiple categories, and therefore there's a table which contains JID and CID to link that joke to that category

    yes, you do have to enter pairs of key values into that table

    that structure is one of the fundamental structures in all databases -- the many-to-many relationship

    each joke can be in many categories

    each category can have many jokes

    bazillions of databases around the world have structures like that

    and yes, those values have to be entered into that relationship table

    yes, manually
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Jun 2004
    Location
    Calgary
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Same MySQL Lookup Table Issue

    I am at the exact same point as you and can't seem to wrap my head around it. I am a newbie to MySQL but I always thought there would be a way you could create a dynamic link between tables some how.

    There is just no sense in having to manually populate the JID and CID for each new item. I can see the CID possibly needing feedback from the user on which category the joke falls into but the JID should automatically populate when a new joke is created.
    I even tried putting the same values into the JokeLookup table and executed the queries that were given and they still return 0 rows.

    If you have managed to find any information on this subject it would be GREATLY appreciated!!!
    The only way I can see this being done is with PHP. Somehow getting the current Joke.ID and putting it into the JokeLookup.JID column if thats possible?
    Thanks,
    John.


    Quote Originally Posted by adamdaughterson
    I've been reading Kevin Yank's Build Your Own ...
    and on page 90 (second ed.) he walks the reader through creating a Lookup Table. No problem. I understand how this could make the potential gain in performance by having the Categories and actual ID's of the Jokes, or whatever you want in the database. What I haven't grasped yet is the SQL statement that would match all existing JID's to a particular CID. There are really detailed and discriptive SQL statements for every other new concept in the book so far, but this part seems to have been glossed over. I assume there will be something like:
    Code:
    update set (JID, CID) = (Jokes.ID where...
    ...and that's where I realize that this won't work at all! There isn't anything in either CID or JID column's yet, so "WHERE CID = X" won't work untill something is in there. Do I have to manually enter JID/CID whenever I add a new joke? I would think that none of the guru's would use SQL if that were the case.
    Any help?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by johnpilon
    The only way I can see this being done is with PHP. Somehow getting the current Joke.ID and putting it into the JokeLookup.JID column if thats possible?
    yes, that's right, you do it with php

    that's what i meant by "manually"

    you have to do it, the database can't do it for you

    the database can assign a new auto_increment value when you add a new joke, but in order to link it to a category, the value has to be entered into the JID column, and you have to do that
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •