SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Guru
    Join Date
    Jan 2005
    0 Post(s)
    0 Thread(s)

    Mysql and PHP help

    Hi all

    I am doing my college project and I am creating something online for a clothing store.

    I have the following problem:-

    I have an array of lets garment colors like the one shown below in a form

    <form method="post" action="path to script">
    <input type="checkbox" id="colors[]" value="red" /> Red
    <input type="checkbox" id="colors[]" value="blue" /> Blue
    <input type="checkbox" id="colors[]" value="green" /> Green
    <input type="checkbox" id="colors[]" value="yellow" /> Yellow

    My problem is that I firstly want to insert the garment colors into the database, together with other information.So a table might have
    fields like garment id, garment name, garment type and color with the garment color being an array cause a visitor may but more that one colotr garment etc.

    I also want that information to be extracted at a later stage to display in an HTML form, so lets say Mr Jones wants to check his order on Januray 31,we will then output the colors of the garment that was purchased,(also taking into account that a buyer can buy a red garment only or he can but all the colours)

    My last problem is that I want to be able to compare records,so I want to know how many reds garments were sold on January 31 or how many of a particular garment in certain color was returned.

    I think that if I can get an idea of how to insert and then manipulate that information from an array I can achieve alot.

    Sorry if this is a mouthful,please assist me

    Now I posted this under the PHP section as well and a comment was made for me to use implode,so that will create CSV now if I do that how do I retrieve the data from the database and compare values to make reports like how mant reds and how many yellows were sold etc..

    Please help


  2. #2
    SitePoint Zealot CdeMky's Avatar
    Join Date
    Sep 2004
    United States
    0 Post(s)
    0 Thread(s)
    This sounds like a many to many relationship. For each garment, there can be one or more colors. There are many ways to handle this in a real life situation, but in your case it sounds like a many-to-many situation.

    What you may need is a garment table, a garment color table, and another table to store the colors for each garment.

    Table1: garment
    garment id
    garment name
    garment type
    garment color id

    Table2: garment color
    garment color id
    garment color name

    Table 3: garment color intermediary table
    garment id
    garment color id

    Table 4: garment purchase
    garment purchase id
    garment id
    garment purchase date
    garment return date

    Now, in table 3, for each garment you have, you have a color associated with it. Therefore, to find all the colors for garment 2,324 for example, you would look in table 3, find all rows for garment id = 2,324, and it will return all the colors associated with that garment.

    Now, for simplicity, i made the garment purchase table as bare as possible and I assume one purchase at a time. To find all red garments purchased on or between a date, just do a search on all purchases on those dates, and join that with the intermediate table. Same with returns, there is a lot of ways to model this particular situation but basically I think you are looking at a couple of many-to-many relationships.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts