Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 15 of 15
  1. #1
    New Coder
    Join Date
    Jul 2008
    Location
    Maryland
    Posts
    34
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Question Am I setting up db for correct queries

    Very new to this.

    I've set up a MySQL db with two tables.

    Structure is
    table_ads with fields id, category_id, name, sort_name, img1, img2, and active

    table_categories with fields id, category

    The goal is when page is loaded to have all active (true) names from ads fall below the category from table_categories, sorted by the sort_name. Categories should only be found when there is at least one active name from ads.

    A secondary page should be created from anchored links populated by category. So, if there are two active landscapers, clicking on their name on the main page will be go to an automatic anchored link on a page of landscapers.

    Is this possible? I'm not sure if I've set up the db correctly for this, and am worried about querying the info to create the first and second pages. Any point in the right direction is much appreciated.

    I went through the PHP with MySQL videos from lynda dot com, but it didn't cover anything other that I could relate to this. My main background is print, with some regular html and css, but I want and need to learn.

    Thanks,
    Donna

  • #2
    New Coder
    Join Date
    Jul 2008
    Location
    Maryland
    Posts
    34
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Turns out it's an okay setup.

  • #3
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    HI,

    You need to build your db taking account of the 'rules' of normalisation. If your db is normalised, then it will be possible/easier, to manage and maintain.

    The queries should be built once the db has sufficient data in it to test.

    bazz
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link

  • #4
    New Coder
    Join Date
    Jul 2008
    Location
    Maryland
    Posts
    34
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Thanks, bazz. I was most concerned having not really done this before. I have data in and have managed to extract. I have another post out there trying to find out how I can split across columns with what I've managed to get to date.

  • #5
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    post a link to that question here and, I'll take a look at it.

    you seem to have amde a rewaonable start - even if I do say so as much a beginner myself. in the fields shown in your op, what are the images for? might they be images that are used by more than one person/record? if so, you may like to consider putting them into a table of their own and using a foreign key to relate them to the record.

    in case that confuses a bit, you used a foreign key to relate the two tables for category and category_id.

    bazz
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link

  • #6
    New Coder
    Join Date
    Jul 2008
    Location
    Maryland
    Posts
    34
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Hi, bazz

    I've posted it here

    http://www.codingforums.com/showthread.php?t=145252

    The images are to each advertiser name - they can have up to 2. Eventually, this list of categories and active names will link to a page of the category (say, landscapers) that will show the images. A preview type page, I suppose.

    Thanks,
    Donna
    Last edited by dmac68; 07-27-2008 at 09:27 PM. Reason: Didn't answer whole post

  • #7
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    Based on this thread and the other, I think it is possible that your db is not normalised. if you are trying to store data as shown below, then really you should use a many to many table instead.

    Code:
    |field_id | image 1 | image2| image3 |
    better like,

    Code:
    | rel_id | field_id | image_id |
    where field_id is taken from the tbl in your first post and image_id is taken from a table like this

    Code:
    | image_id | image1 |
    | image_id | image2 |
    bazz
    Last edited by bazz; 07-27-2008 at 10:03 PM.
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link

  • Users who have thanked bazz for this post:

    dmac68 (07-27-2008)

  • #8
    New Coder
    Join Date
    Jul 2008
    Location
    Maryland
    Posts
    34
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Hmm, I hadn't thought of splitting the images to separate table. I only have about 30 items in the advertisers table now. I guess I can always move the images to a separate table. I am trying to keep as simple as possible. After I get everything to display for the public, I need to do some kind of CMS form for this so the client can make active, change image names, add names, etc.

    I'll try looking into that. But for now my main concern is the page that only lists the category and advertiser name below it. Do you think I need to change the db to accomplish that?

    Thanks,
    Donna

  • #9
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    Well I note you mentioned that you wanted to keep it simple as possible.

    Not meaning to become pedantic but, simple noiw means more awkward later. get the db normalised and it'll remin as simple as possible. the queries can be dealt with later. I have just built a db which is quite comprehensive in nature. the route I had to follow was:

    1. build the tables
    2. normalise them
    3. build first scripts (to see how ti all might work)
    4. tweak tables
    5. repeat 3
    6. repeat 4
    7. build more scripts (eg the cms)
    8 test all.

    I think it seems like you are trying to 1,2 and 3 all at once and this can make it more difficult just as if eating a cake in one mouthful. We are better to eat it in bite size chunks. lol

    bazz
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link

  • #10
    New Coder
    Join Date
    Jul 2008
    Location
    Maryland
    Posts
    34
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Thanks, bazz. I'm trying, and I appreciate the advice. This will be a pretty small project, but the next thing I think I'll be doing will need this kind of structure you mention.

    I'll keep trying. It's not a big deal to change the images to a separate table.

    Donna

  • #11
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by dmac68 View Post
    Thanks, bazz. I'm trying, and I appreciate the advice. This will be a pretty small project, but the next thing I think I'll be doing will need this kind of structure you mention.

    I'll keep trying. It's not a big deal to change the images to a separate table.

    Donna
    this is a little bit old and unmaintained but it could help you:

    http://www.utexas.edu/its/archive/wi...ing/index.html

    regards

  • Users who have thanked oesxyl for this post:

    dmac68 (07-28-2008)

  • #12
    New Coder
    Join Date
    Jul 2008
    Location
    Maryland
    Posts
    34
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Thanks for the link. I have a basic understanding, but will try to read through this tomorrow at work. This project is a side deal - my day job is print design, so the terminology I'm used to is quite different. My web experience has been basic maintenance of some php pages and some css/html design. Learn as you go

    Thanks,
    Donna

  • #13
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    Quote Originally Posted by dmac68 View Post
    Thanks, bazz. I'm trying, and I appreciate the advice. This will be a pretty small project, but the next thing I think I'll be doing will need this kind of structure you mention.

    Donna
    I understand but, it makes good sense to get into the way of it from the beginning so that you don't have, either, to re-learn later or, pick up bad habits as you learn.

    bazz
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link

  • #14
    New Coder
    Join Date
    Jul 2008
    Location
    Maryland
    Posts
    34
    Thanks
    6
    Thanked 0 Times in 0 Posts
    I meant I will start out this way on the next project. I am changing the current project setup per your advice.


    Donna

  • #15
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    Ah; OK. don't forget to post back if/when you need more help.

    bazz
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link


  •  

    Posting Permissions

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