SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    Hi there! Owen's Avatar
    Join Date
    Jan 2000
    Location
    CA
    Posts
    1,165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello!

    Okay, here's my problem. I have lengthy, say, articles and they have one, two, or more authors. If I'm keeping everything in a mySQL db and want to be able to search by author, how would you recommend I go about this? For example:

    author = ("Owen", "John");

    another article:

    author = ("Owen");

    I want to still be able to search for articles by Owen or by John. I though about doing a set, but there's about 500 different "authors" so that wouldn't really work

    Anyone have any ideas. I'm guessing there's a datatype for something like this, though I couldn't find it on the mySQL reference website. (I really need to buy a book.)

    Thanks!
    Owen

  2. #2
    Hi there! Owen's Avatar
    Join Date
    Jan 2000
    Location
    CA
    Posts
    1,165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Anyone?

  3. #3
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You could do it a couple of ways:
    WHERE author = "Owen" OR author = "Bob";

    Or you could do:
    WHERE author IN( "Owen", "Bob" );

  4. #4
    Hi there! Owen's Avatar
    Join Date
    Jan 2000
    Location
    CA
    Posts
    1,165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That's not quite what meant. I didn't mean searching for two names, but for storing a variable number of names in a record... So maybe an article has one or maybe two authors. I want to be able to store any number of authors in each record. I don't really want to make another db because 95% would only have one.

    I was thinking about storing the names like "owen|bob"...

    Owen

  5. #5
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh well to do it the "real" way you'd want something like:
    Code:
    CREATE TABLE articles AS(
      article_id,
      ...
      ...
    );
    
    CREATE TABLE authors AS(
      author_id,
      ...
      ...
    );
    
    CREATE TABLE author_article AS(
      author_id,
      article_id
      PRIMARY KEY( author_id, article_id ) 
    );
    
    SELECT ....
      FROM article a, 
           author_article aa
     WHERE a.article_id = aa.article_id;
    You CAN stuff the names into a varchar column if you want and then use PHP to explode those out, but then you're really doing a "badthing" in table design. You're misusing the whole idea of relational db design when you stuff things like that.

    NOTE: You can replace author_id with author_name if you want to use the text name of an author instead of some ID (such as SSN or something). The only problem is that you could have "Bob" in there and you'd be forced to call the other author "Bob1" or something.
    Last edited by MattR; Apr 4, 2001 at 23:49.

  6. #6
    Grumpy Mole Man Skunk's Avatar
    Join Date
    Jan 2001
    Location
    Lawrence, Kansas
    Posts
    2,067
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    MattR's example is ideal. Read up a bit on relational database design and you should be able to avoid problems like this in the future. Here's a good tutorial:

    http://www.phpbuilder.com/columns/barry20000731.php3


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
  •