SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Dec 2000
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    I've just finished reading Kevin Yank's excellent article "Building a Database-Driven Web Site Using PHP and MySQL" (http://www.webmasterbase.com/article.php?aid=228) and I have a question.

    I'm a beginner when it comes to both PHP and MySQL, so this may be a very simple question, but I'd appreciate any help you could offer (especially code snippets ).

    To pick up the article's example, I have a jokes table and an author table. I'd like for it to be possible for a joke to be associated with one or more authors. So what I need is a lookup table. So far, so good.

    What causes me problems is when it comes to listing the jokes contained in the database with their associated authors. Like this:

    "How many lawyers..." by MarkT and DavidM

    "Why the chicken..." by DavidM, Erik999 and MariaQ

    How would I go about generating the above result? I'd like help with both the SQL and the PHP side of things.

    Thanks!

  2. #2
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    I helped Salamander with this over email. For the benefit of the community, here was the solution I proposed:

    What you need to do is to list all combinations of matching jokes and authors, then ORDER BY the jokes' ID's. Thus, if a joke has two authors, your result set will have two rows for that joke -- one with the first author, and one with the second. The ORDER BY clause will ensure that all entries for a particular joke appear together. You can then have your PHP script loop through the result set listing a new joke whenever the joke ID changes, and adding authors to the end of the current entry when the joke ID is the same.
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference

  3. #3
    SitePoint Enthusiast
    Join Date
    Oct 2000
    Posts
    89
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What I would do is to create a new table between jokes and authors like this

    id primary key
    jokeid integer
    authorid integer

    Then for each different author I will make an entry to this table that connects author and joke table.
    I have not read the tutorial so I do not know if there is such a table already suggested.
    Great Dane
    Gokhan ARLI

  4. #4
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    gokhana,

    Salamander stated in his/her original message that he/she was already doing that. What was missing was the code to display the jokes in the desired format.

    To pick up the article's example, I have a jokes table and an author table. I'd like for it to be possible for a joke to be associated with one or more authors. So what I need is a lookup table. So far, so good.
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference

  5. #5
    SitePoint Enthusiast
    Join Date
    Oct 2000
    Posts
    89
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yep misread his question
    Great Dane
    Gokhan ARLI


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
  •