First database advice needed
Stuart Haiz Feb 20, 2008 9:28 AMI 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.
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.


