#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2000
    Posts
    6
    Rep Power
    0
    I am extremely new to SQL. I have been reading the info all weekend, and feel I
    have sort of grasped the concept.
    I am trying to set up a database, it will
    be pretty mundane stuff, name, address, city
    state. I am catagorizing artists, and need
    to list their mediums. Now do I have to create several columns for this? for example,
    medium_1 medium_2 medium_3
    or is their a way to have all the different mediums in one column together?
  2. #2
  3. No Profile Picture
    silfreed
    Guest
    Devshed Newbie (0 - 499 posts)
    You have just entered the realm of relational databases.
    Things like this are difficult to grasp at first, but i'll try to give you a good explanation.

    Probably the best way would be to setup three tables. One of your user, like you have, another for the mediums the user creates art in, and another for the available mediums the artist can select.
    In the user table, you don't even need the anything for medium. In the second table, you need 2 fields. User id (or username, if you keep them unique), and medium id (or name). For the medium table, you need two (or three fields). One for the Medium id, the medium name, and the medium description (if wanted). Here's some sample tables:
    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">code:</font><HR><pre>
    create table users (
    uid INT DEFAULT '0' NOT NULL AUTO_INCREMENT,
    username VARCHAR(20) NOT NULL,
    password VARCHAR(32) NOT NULL,
    #other misc columns
    PRIMARY KEY(uid)
    );

    create table users_mediums (
    uid INT DEFAULT '0' NOT NULL,
    medium_id INT DEFAULT '0' NOT NULL
    );

    create table mediums (
    medium_id INT DEFAULT '0' NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    desc TINYBLOB,
    PRIMARY KEY(medium_id)
    );
    [/code]
    Now, insert all available medium types into the mediums table, using something like <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">code:</font><HR><pre>INSERT INTO mediums VALUES (null, 'mymedium', 'mydesc');[/code].
    Then, you insert the users id in to the users_medium table along with the id of the medium he belongs to. This allows you to store as many mediums per user as you want, while being able to expand easliy.

    I hope this helps. This is a fairly long post. I've found that the articles here are very useful in PHP/MySQL teaching. For more help, you might want to try www.phpbuilder.com , or just post here.

    -silfreed
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2000
    Posts
    6
    Rep Power
    0
    Ok.. feel free to give long posts because I need detail here.

    I think I need to go back and read. I am
    assuming the
    username VARCHAR(20) NOT NULL,
    password VARCHAR(32) NOT NULL,
    are so they can access their records and
    update the info? See I told you I am very very new. I have not even crossed into how
    I will set this up on the site so they can
    access the data. Have not even thougtht of
    that yet. I need to go back and
    read some more. I am probably putting the cart before the horse.

    I guess I am one of those who just jumps in the pool and hopes I learn to swim before I drown.

    What it appears to me is each column is a table on its own? Let me give you a better explanation of what I "HOPE" to do.
    I will be listing Artists, their state, city,
    Galleries where they exhibit, their mediums
    and what type of art they do, ie, landscapes, still lifes and so on.

    I also am going to have a listing for Galleries, their addresses, city state
    urls, and artists they represent. Hopefully
    this is all going to be cross referernced.
    I want these searchable, so that if someone
    wants to find a landscape artist in Louisiana, they can do a keyword search.
    Am I dreaming here? Is this going to be doable, because if this wont' do it then I
    will throw up my hands. This is my personal site and I certainly can't afford to hire a programmer. So I decided to learn on my own.
    What do you think? Have I taken on something that is beyond a beginner?
  6. #4
  7. No Profile Picture
    silfreed
    Guest
    Devshed Newbie (0 - 499 posts)
    Yeah, username and password would be for the user to make their own changes, but it doesn't sound like you'll need that.
    Each column isn't a table. A table can have multiple rows, and data has to be in one of the colums. something like this:

    | col1 | col2 | col3 |
    ----------------------------
    row1 |'data'|'data'|'data'|
    row2 |'data'|'data'|'data'|

    Everything you want is doable, although, I could spend my entire workday explaining everything. I would recommend reading some articles on this site (devshed), and refering to the mysql manual when needed. It isn't very good reading if you want general help or tutorials, but is decent if you want help on a specific topic. Feel free to keep asking, though, and don't get discouraged. It took me about 6 months to really grasp everything when I started, but you can learn to swim by jumping in. I think that's the best way.

    -silfreed
  8. #5
  9. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2000
    Posts
    6
    Rep Power
    0
    Thank You so much.
  10. #6
  11. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2000
    Posts
    6
    Rep Power
    0
    I went by your example,
    and was playing around with creating
    tables.

    On the third table I get this error:
    MySQL said: You have an error in your SQL syntax near 'desc TINYBLOB , PRIMARY KEY (medium_id))'
    at line 1

    What does this mean I have examined my books,
    can't find what it means.
  12. #7
  13. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    DESC is a reserved word (used with order by to indicated a descending sort) so you can't use it as a field name. Change it to descript or something.
  14. #8
  15. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2000
    Posts
    6
    Rep Power
    0
    Thanks that worked. What I would like to know now, is once the tables are established
    and the data is in, what do I use to access this from my website? Is this done with simple html forms, perl or what?
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 1999
    Location
    SLC, UT, USA
    Posts
    47
    Rep Power
    16
    Just for arguments sake, why wouldn't you use an enumerated type for the medium?

    Robman
  18. #10
  19. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2000
    Posts
    6
    Rep Power
    0
    oh you better explain that for me.
    I am open to all suggestions, I have only created the tables, I am not entering data as of yet.

    I am looking for the best structure, and since I really don't know what I am going any and all suggestions (with examples) would
    be appreciated.

    I have ordered a book on mySQL so it is on its way, but until then I am reading what I have found online. I am someone who knows practacally nothing about this stuff and wants it to work tomorrow :-). Of course that
    isn't the way it is, but when I put my mind to it I learn very fast.

    What I want is for artists to submit their info:

    Jane Doe
    123 Maple St
    Anytown, ST 12345
    Phone: 555-555-5555
    Email: Jane@whatever.com
    url: http://www.janedoessiteaddy.com
    medium: oils, watercolor, pastels
    Type: impressionist, landscapes, still lifes
    Galleries: This Gallery, Anytown, St
    That Gallery, ThatTwon, St.

    Now, I will have galleries list:

    So-and-So Gallery
    1234 Oak St
    Anytown, ST 12345
    Phone: 555-555-5555
    Fax: 555-555-5555
    Email: gallery@galleriessite.com
    URL: http://www.galleriessite.com
    Artist: Jane Doe
    Joe Bob Clampet
    John Smith

    ok so I want a visitor to the site to be able
    to search for a landscape artist in Anytown, St. When it comes up it will list all artists that do landscapes in that city. And
    then I want them to be able to search for galleries that have this work by so and so artist. So with that said, have I lost my mind?

    I understand setting up tables, and I am familiarizing myself with the terms, but I need a connection. I need to list things in seperate tables but connect somehow.
    So what do you think?

Similar Threads

  1. Returning a tree structure from table
    By larre in forum MS SQL Development
    Replies: 5
    Last Post: October 24th, 2003, 01:09 AM
  2. A newbie question....Thanks a lot!
    By matureking in forum MySQL Help
    Replies: 2
    Last Post: August 14th, 2003, 12:43 AM
  3. table structure
    By bradbrevet in forum MySQL Help
    Replies: 5
    Last Post: July 24th, 2003, 01:31 PM
  4. Optimization question: one table or many?
    By lgolden in forum MySQL Help
    Replies: 5
    Last Post: August 20th, 2001, 01:44 PM
  5. table question....????
    By trops in forum MySQL Help
    Replies: 4
    Last Post: June 8th, 2001, 08:10 PM

IMN logo majestic logo threadwatch logo seochat tools logo