Am I setting up db for correct queries

[dmac68]

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

[dmac68]

Turns out it's an okay setup.

[bazz]

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

[dmac68]

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.

[bazz]

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

[dmac68]

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

[bazz]

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.

|field_id | image 1 | image2| image3 |

better like,

| 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

| image_id | image1 |
| image_id | image2 |

bazz

[dmac68]

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

[bazz]

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

[dmac68]

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

[oesxyl]

Originally Posted by dmac68

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

[dmac68]

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

[bazz]

Originally Posted by dmac68

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

[dmac68]

I meant I will start out this way on the next project. I am changing the current project setup per your advice.


Donna

[bazz]

Ah; OK. don't forget to post back if/when you need more help.

bazz