Can anyone help a newbie figure out MySQL?

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.

Re: Can anyone help a newbie figure out MySQL?

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-S hopping-Cart-With-PHP-and-MySQL/2/

http://www.evolt.org/article/PHP_Login_System_with_Admin_Fea tures/17/60384/index.html

HIH

Re: Can anyone help a newbie figure out MySQL?

"strawberry" 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-S hopping-Cart-With-PHP-and-MySQL/2/
>
> http://www.evolt.org/article/PHP_Login_System_with_Admin_Fea tures/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.

Re: Can anyone help a newbie figure out MySQL?

> 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.

Yes, but you MUST read up on MySQL injection.

> 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"?

Well, total manufactured is probably as simple as 'total sold + total
in stock' but I guess it would be fairly straightforward to hold this
info in a separate table if you really wanted to.

Re: Can anyone help a newbie figure out MySQL?

Post removed (X-No-Archive: yes)

Re: Can anyone help a newbie figure out MySQL?

For some reason, the way my hosting provider has set up phpmyadmin & mysql,
does not allow me to create relational databases. I have having real
trouble getting this point through to the support staff.

I get the following response when I click on the name of my database (left
panel) and then on the "operations" sub-tab on the right panel - "Error =
The additional features for working with linked tables have been
deactiviated. To find out why click here". When I click I am presented
with the readout below, how would I go about correcting these perameters to
enable relational features etc, and what variables should I consider?

$cfg['Servers'][$i]['pmadb'] ... OK

$cfg['Servers'][$i]['relation'] ... not OK [ Documentation ]
General relation features: Disabled

$cfg['Servers'][$i]['table_info'] ... not OK [ Documentation ]
Display Features: Disabled

$cfg['Servers'][$i]['table_coords'] ... not OK [ Documentation ]
$cfg['Servers'][$i]['pdf_pages'] ... not OK [ Documentation ]
Creation of PDFs: Disabled

$cfg['Servers'][$i]['column_info'] ... not OK [ Documentation ]
Displaying Column Comments: Disabled
Bookmarked SQL query: Disabled

MIME ... not OK [ Documentation ]
$cfg['Servers'][$i]['history'] ... not OK [ Documentation ]
SQL history: Disabled

Regards,
CB.

Re: Can anyone help a newbie figure out MySQL?

Post removed (X-No-Archive: yes)

Re: Can anyone help a newbie figure out MySQL?

"George" wrote in message
news:eeet7r0231m@drn.newsguy.com...
> On Fri, 15 Sep 2006 03:56:22 GMT, Cerebral Believer wrote...
>>
>>For some reason, the way my hosting provider has set up phpmyadmin &
>>mysql,
>>does not allow me to create relational databases. I have having real
>>trouble getting this point through to the support staff.
>>
>>I get the following response when I click on the name of my database (left
>>panel) and then on the "operations" sub-tab on the right panel - "Error =
>>The additional features for working with linked tables have been
>>deactiviated. To find out why click here". When I click I am presented
>>with the readout below, how would I go about correcting these perameters
>>to
>>enable relational features etc, and what variables should I consider?
>>
>>$cfg['Servers'][$i]['pmadb'] ... OK
>>
>>$cfg['Servers'][$i]['relation'] ... not OK [ Documentation ]
>>General relation features: Disabled
>>
>>$cfg['Servers'][$i]['table_info'] ... not OK [ Documentation ]
>>Display Features: Disabled
>>
>>$cfg['Servers'][$i]['table_coords'] ... not OK [ Documentation ]
>>$cfg['Servers'][$i]['pdf_pages'] ... not OK [ Documentation ]
>>Creation of PDFs: Disabled
>>
>>$cfg['Servers'][$i]['column_info'] ... not OK [ Documentation ]
>>Displaying Column Comments: Disabled
>>Bookmarked SQL query: Disabled
>>
>>MIME ... not OK [ Documentation ]
>>$cfg['Servers'][$i]['history'] ... not OK [ Documentation ]
>>SQL history: Disabled
>>
>>Regards,
>>CB.
>>
>>
>
> If you're site is hosted somewhere maybe their service is limiting access
> to a
> specific database. They may be restricting what their customers can create
> to
> control how many databases and how large the databases are. you might want
> to
> check with them to see if there is already a MySQL database assigned to
> you, and
> can use that to create and populate your tables.

Hi George,

Well, I know I can create a number of databases, there has been no upper
limit specified by the hosting provider, so I am not sure if space is an
issue, resources & server load, security, perhaps?

Do you know anything about these configuration settings? I have asked the
hosting provider to update my "config.inc.php" & "scripts/create_tables.sql"
files. An ebook I purchased suggests these settings in these files need to
be modified to take advantage of the relational features of phpmyadmin and
should be:

$cfg['Servers'][$i]['pmadb'] = 'phpmyadmin';
$cfg['Servers'][$i]['bookmarktable'] = 'pma_bookmark';
$cfg['Servers'][$i]['relation'] = 'pma_relation';
$cfg['Servers'][$i]['table_info'] = 'pma_table_info';
$cfg['Servers'][$i]['table_coords'] = 'pma_table_coords';
$cfg['Servers'][$i]['pdf_pages'] = 'pma_pdf_pages';
$cfg['Servers'][$i]['column_info'] = 'pma_column_info';
$cfg['Servers'][$i]['history'] = 'pma_history';

If I knew a way to change the settings I would change them myself, but it
seems like I need access to the installation directories for phpmyadmin,
and/or MySQL.

Regards,
C.B.