Posted by Cerebral Believer on 09/13/06 20:19

"strawberry" <zac.carey@gmail.com> wrote in message
news:1158176001.391549.157380@i42g2000cwa.googlegroups.com...
>
> 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.
>
> There's too much to address here. I'd recommend you buy/borrow a couple
> of books. Irritating title and format (and non-comprehensive index)
> aside, I think Janet Valade's 'PHP & MySQL For Dummies' is a good
> primer for the mechanics of this kind of thing. Any remaining gaps can
> be plugged by online tutorials and NGs like this.
>
> Here are a couple of links to online tools/tutorials that I've stumbled
> across recently which I think you might find useful:
>
> http://www.devarticles.com/c/a/MySQL/Building-A-Persistent-Shopping-Cart-With-PHP-and-MySQL/2/
>
> http://www.evolt.org/article/PHP_Login_System_with_Admin_Features/17/60384/index.html

Thanks for the info,

Yes I agree that was a lot to ask. I already answered some of the questions
I had, as I found an e-book on phpMyAdmin 2.7.0-pl2 which has helped me
considerably. I will also look into those links and chase up the book.

OK, just two questions are important to me now.

Can a user on my website submit a form that will automatically be written
into my MySQL database? Just a yes or no would suffice at this stage.

Can MySQL keep an up to date inventory, by say summing the total contents of
field "a" in table "one", also summing the total contents of field "b" in
the same table, and allow field "z" in table "two" to perform the
calculation such as "a"-"b"="z"?

Regards,
CB.

[Back to original message]