First database advice needed

Report · Feb 20, 2008

I have a php/mysql site that has been built using the basics that I have picked up from David Powers excellent PHP Web Development DWMX04 & PHP5 for Flash books. I have now tied to adapt what I have learnt & need help with my first steps...

The site was built so school kids can create weekly lunch menus, which their parents pay for by buying blocks of credits.

This is my first database driven site & as such, at the moment there is a lot of the information that is collected that has to be entered manually into spreadsheets - due to my inexperience in setting all this up! The site has secured & working Admin & Parents sections; all the pages within these are filled from my database.

Presently, the meal credits are purchased through PayPal (with static button options) & credits are manually reduced every time a meal order is placed. We can live with this at the moment, but the real issue is the menu itself. This is split over the days of the week first of all & repeated for each day. I have currently chosen to create tables in the database for each type of meal option - salads, drinks, fruit, snacks etc. Each of these tables has an id & a name column. The weekly menu is made up of drop menus that are populated by the database. There are 7 recordsets that fill this & I'm starting to realise that I may not have done this in the most efficient way.

This is where I would really appreciate some advice or pointers from someone who has a much better idea than I do on how to make this work better.

I realise that having only 2 tables, one for the food categories (with cat_id & catname) & one for the foods (food_id, cat_id & foodname) is a more efficient way of doing things. What I don't know though, is if I can use this in a single recordset to fill my dynamic menu? The reason I would like to have a single recordset is because ultimately, I would like to be able to store the menu itself in the database (no table ready for this yet) & also produce an xml file from the menu orders so they can be imported into Excel. Again, I don't yet know how to do this.

When I bought cs3 I went for the lynda.com trial as my free gift & know that there was an xml extension option. At the time I didn't look at this so don't know whether this will be of use now...

If anyone can give me some clues or thoughts on the best way forward I will be very grateful.

I'm using php/mysql/phpMyAdmin with DWCS3 on Mac 10.5.2.

Report · Feb 28, 2008

Apologies for bumping this thread - & for the length of the message, but I would really appreciate some advice from someone.

Report · Feb 28, 2008

I am not tring to be nasty but your post was to me very long rambling
and difficult trying to figure out what the question or questions are.
I have found asking the questions very close to the top with ?'s help
readers like me figure out what you want. Then you can add more
qualifying comments later.

Just a suggestion.

Report · Feb 28, 2008

Fair enough & thanks for your honesty!

Ok, Is there a way to get details from 2 database tables into a single query, so I can:

a. Fill dynamic menus?
b. Export the selections to an xml file?
c. Eventually add these selections as an order in a new table.

I have 2 tables, one for food categories (with cat_id & catname) & one for the foods themselves (food_id, cat_id & foodname). What I am stuck on, is how I can join them in a single recordset to fill my dynamic menu? The reason I would like to have a single recordset is because ultimately, I would like to be able to store the menu itself in the database (no table ready for this yet) & also produce an xml file from the menu orders so they can be imported into Excel. Again, I don't yet know how to do this.

I'm using php/mysql/phpMyAdmin with DWCS3 on Mac 10.5.2.

Report · Feb 28, 2008

Try this:

SELECT catname, foodname, food_id, categories.cat_id
FROM categories, foods
WHERE foods.cat_id=categories.cat_id

to organize by category name, add
ORDER BY catname

Paul Davis
http://www.kaosweaver.com/
Visit us for dozens of useful Dreamweaver Extensions.

http://www.communitymx.com/
Partner at Community MX - Extend your knowledge

Siriunson wrote:
> Fair enough & thanks for your honesty!
>
> Ok, Is there a way to get details from 2 database tables into a single query,
> so I can:
>
> a. Fill dynamic menus?
> b. Export the selections to an xml file?
> c. Eventually add these selections as an order in a new table.
>
> I have 2 tables, one for food categories (with cat_id & catname) & one for the
> foods themselves (food_id, cat_id & foodname). What I am stuck on, is how I can
> join them in a single recordset to fill my dynamic menu? The reason I would
> like to have a single recordset is because ultimately, I would like to be able
> to store the menu itself in the database (no table ready for this yet) & also
> produce an xml file from the menu orders so they can be imported into Excel.
> Again, I don't yet know how to do this.
>
>
> I'm using php/mysql/phpMyAdmin with DWCS3 on Mac 10.5.2.
>
>

Report · Feb 28, 2008

Thanks Paul

I got a sql error with this & had to add the table names before the field names:
SELECT foodcat.catname, foods.name, foods.food_id, foodcat.cat_id
FROM foodcat, foods
WHERE foods.cat_id=foodcat.cat_id
ORDER BY foodcat.cat_id

This works for creating a single recordset, but when I try to use it with my dynamic menus, every food item from all categories is listed. See this page for a screengrab.

Is there a way to filter the results so each drop menu displays only the relevant category? I previously used individual recordsets to do this.