Posted by George on 09/14/06 18:20

On Tue, 12 Sep 2006 16:06:52 GMT, Cerebral Believer wrote...
>
>Hi everybody,
>
>I am creating a website to sell music I write, and I want to make use of
>MySQL to do things like:
>
>Record Customer Infomation (including logins and passwords)
>Record Product Information
>Record Sales Informaton
>Link all those information types together so I can find current totals
>relating to how many products I have sold, who has bought what etc...
>
>I have created a database using MySQL 5.0.15, and phpMyAdmin 2.7.0-pl2, so I
>am not using command line interface but a web-browser based GUI. Is anyone
>familiar with this interface? Basically I have created three tables in my
>database (please bear in mind this is my first MySQL database).
>
>
>1) Customer Information:
>
>This holds the usual info, names and addresses etc. I am hoping that there
>is a way to create a "form" (including e-mail address & password
>verification) which users will fill in which will automatically be written
>to the database? _I am not at that stage yet_, but will probably need some
>advice when I get there as I don't have a clue how to interface my html
>pages with the databse, although I am imagining it is through php. The
>primary key for this table is "Member Number", this is auto incremented.
>
>I also have a few fields I need advice on.
>
>I have a membership field, type "set", values "Yes" or "No". Is that the
>best data-type to match up with a radio button or check box offering Yes/No
>options on a form?
>
>How can I automatically calculate the total number of products a user has
>ordered from me and have this figure automatically written into my database?
>I think I just need this field to mirror a value that could be calculated
>from my "Sales Information" table described below.
>
>Is there a way to keep tabs of the total number of visits (by IP address, or
>login), that a user has made to my site and record this in the database?
>
>I may also need to know and be able to limit how many times a user can
>download a particular file.
>
>
>2) Product Information:
>
>This holds information about specific products (eg an album, ep, single) and
>each product will have its own unique record according to format (CD, or
>.mp3, .ra downloads). The primary key in this table is "Catalogue Number".
>
>I also have a few fields I need advice on here too.
>
>I have a field "Quantity Manuafactured", basically I manufacture the
>products and want to keep a running total of how many products have been
>made. Can I just enter the running total into the database manually every
>time more copies are made, and will any calculations that depend on this
>figure always be recalculated? Or should I do another table? Anyway, I ask
>this because the fields, "Quantity Sold" and "Quantity in Stock" relate to
>the first field "Quantity Manuafactured". I need to know how to get the
>database to perform a simple calculation, i.e. "Quantity Manuafactured" -
>"Quantity Sold" = "Quantity in Stock"; and, if there is no stock I have
>another field "Available" using the "set" data-type with the values "Yes",
>"No" at present (I may change them to "Not Yet Released", "In Stock", "Out
>Of Stock", and "Deleted/Unavailable"), can this be linked to the "Quantity
>in Stock" field and automatically register "No" when the database detects
>the stock level has dropped to "0"?
>
>
>3) Sales Information:
>
>This keeps an inventory of sales, every item that is sold on every format
>should be recorded here. The primary key in this table is "Purchase
>Number", this is auto incremented.
>
>Questions I have on this are:
>
>I have a date field, will this automaticallty write the purchase date to the
>database? If not, how is that done?
>
>The field "Member Number" exists both in this table and the "Customer
>Information" table, is this allowable - or should both tables somehow
>"share" a common field? I am using the "Member Number" in this table so
>that I can calculate the number of purchases a member has made for the
>"Customer Information" table.
>
>The "Catalogue Number" and "Price" fields are also duplicated here because
>they are in the "Product Information" table. Again I am not sure if this is
>wise or allowable, but I want to be able to find out exactly what products
>have sold, and to be able to automatically create a sales revenue over
>specific time periods.
>
>Well I think I have asked an awful lot already, so I'll stop there. If
>anyone has any advice, good tutorial links, that would be great.
>
>Thanks in advance,
>C.B.
>
>

If you've already gotten the basic down for PHP, you might want to check out a
"PHP Cookbook" type book. I think O'Reilly makes one and the book also offers an
example for building an on-line store using PHP and MySQL and takes into account
some of the SQL basics you would need. There's an awful lot of material to cover
and would probably get more out of a good book you could pace yourself through.

George
--
Help a Community by Participating in Ours
We donate your subscription fees to the charity you choose
100% of your first month, 10% thereafter.
http://newsguy.com/charity.asp

[Back to original message]