Query from database - is this setup right?

[max2me99]

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

[darkagn]

Your DB structure looks ok, except that I would probably just sort by name and remove the sort_name field. Your SQL query will look something like:

SELECT * FROM table_ads
INNER JOIN table_categories
ON table_ads.category_id = table_categories.id
WHERE table_ads.active = true
ORDER BY name

Might need a bit of tweaking to get exactly the result you're after, but should point you in the right direction.

[max2me99]

Thanks - that gets me in the right direction.

I thought some join would be necessary, but was getting confused with the types.

Donna