5 Replies Latest reply: Feb 28, 2008 8:35 AM by Stuart Haiz RSS

    First database advice needed

    Stuart Haiz Community Member
      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.
        • 1. Re: First database advice needed
          Stuart Haiz Community Member
          Apologies for bumping this thread - & for the length of the message, but I would really appreciate some advice from someone.
          • 2. Re: First database advice needed
            Newsgroup_User Community Member
            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.
            • 3. Re: First database advice needed
              Stuart Haiz Community Member
              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.
              • 4. Re: First database advice needed
                Newsgroup_User Community Member
                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.
                >
                >
                • 5. Re: First database advice needed
                  Stuart Haiz Community Member
                  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.