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?

Hello, CB! Hope I can help a bit...


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

It's easy to create a form and have it processed using PHP. You take
your form page values and "GET" or "POST" them to a PHP processing
page, where you then take the form values and insert them into a table.

Here's VERY simplified code (ignore the

 tags):




















$db = mysql_connect("servername", "username", "password");

if (!$db){echo "error!";}

mysql_select_db("databasename");

$query = "insert into tbl_data values

('".$HTTP_POST_VARS["title"]."')";

$result = mysql_query($query);

?>





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



It's OK. In your database table, just set up a field that has a char(1)

data type and length, and store either "Y' or "N" in that field. Since

you're using an HTML form, you can just set the value of a radio button

set to "Y" or "N" - thereby eliminating the chances of a user inputting

anything else in that field.



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



You'll probably want two tables for your orders, one to capture the

general order info (order number, customer name, shipping and billing

addresses, order total, shipping type, etc.), and a second table -

related! - that keys on the order number, and holds data about each and

every product selected. Here's an example:



Data Table 1 - Main Order Info

order_id: 1019

customer_name: Fred Flintstone

shipping_address: 123 Bedrock Way

etc.



Data Table 2 - Order Items

item_id: 10723

order_id: 1019 //See how this relates to Data Table 1?

product_number: 2893-234784-U

unit_cost: 10

quantity: 3

weight: 10



item_id: 10724

order_id: 1019 //See how this relates to Data Table 1?

product_number: GHSD-32233

unit_cost: 150

quantity:1

weight: 55



item_id: 10725

order_id: 1019 //See how this relates to Data Table 1?

product_number: ASDF-12345

unit_cost: 7.50

quantity:2

weight: 17



Then, when the order is tallied, you present the user with the Main

Order Info from Table 1, then do a query based on all the products for

that Order Number - 1019 - and it will list the items purchased. You

can also tally the total quantity (6, in this case), weight (119, if my

math is correct), and a total cost, ($195, again, if my math's

correct).



The reason for using two tables is, you have no idea how many products

a customer will order, so it's inefficient to space out predetermined

"cubbyholes" in your main order table to accomodate POSSIBLE items.

Using a second table, the customer can order one or a hundred different

items, and the database performs normally. This is part of

"normalization" of a database.



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



You'll be able to do that by parsing the info that each visitor to your

site generates. It is possible, but I'm not sure how to do it exactly.



> I may also need to know and be able to limit how many times a user can

> download a particular file.



You can set up a user account table in your database, and give hime or

her a set number of downloads associated with a specific product and a

specific purchase. Decrement that number each time the download is

accomplished. When it gets to zero, then the customer will need to

purchase another copy.







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



My advice is to use another table. MySQL is really cool, in that you

can make as many as you need, and just "relate" them to other tables.

You can then use the data from your "Manufacturing Totals" table, and

load that into your products table every time you create a new batch.

You can do it with PHP code easily enough.



> 3) Sales Information:







> I have a date field, will this automaticallty write the purchase date to the

> database?  If not, how is that done?



Yes, PHP and MySQL are easy to use when recording date/time info.

Again, the easiest way is to generate an order time, then insert it

into the MySQL order tabel in the appropriate field.



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



This is exactly what relates the tables together. In your Customer

Table, each customer is assigned a Member Number. This is a "Primary

Key" in the Customer Table. In the Order Table, the Member Number

refers back to the Customer Table, and in the Order Table, it is known

as a "Foreign Key."



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



Yep, fine! Again, now you're relating the Product Table to the Order

Table using the Catalog Number as a Foreign Key. Perfect!



> Thanks in advance,

> C.B.

Re: Can anyone help a newbie figure out MySQL?

I forgot to mention, in the "PHP Process Code," you'll have to
substitute your own server name, username, password, and database name
in the appropriate spaces. I just used generic "placeholders" where
they should go in my code example.

But you probably already knew that! ;)

Dano wrote:



>
> > $db = mysql_connect("servername", "username", "password");
> if (!$db){echo "error!";}
> mysql_select_db("databasename");
> $query = "insert into tbl_data values
> ('".$HTTP_POST_VARS["title"]."')";
> $result = mysql_query($query);
> ?>
>

Re: Can anyone help a newbie figure out MySQL?

Dano wrote:
> $query = "insert into tbl_data values
> ('".$HTTP_POST_VARS["title"]."')";
> $result = mysql_query($query);

Dano, it's very nice of you to be helpful and post sample code for the
OP, but showing this as the way to do it is dangerous. You're showing a
common security flaw, called SQL Injection.

Read the following web pages:
http://en.wikipedia.org/wiki/Sql_injection
http://www.php.net/manual/en/function.mysql-real-escape-stri ng.php

Regards,
Bill K.

Re: Can anyone help a newbie figure out MySQL?

Hi Dano,

Thanks for your enthusiastic response!

"Dano" wrote in message
news:1158238360.191443.27240@k70g2000cwa.googlegroups.com...
> Hello, CB! Hope I can help a bit...
>
> It's easy to create a form and have it processed using PHP. You take
> your form page values and "GET" or "POST" them to a PHP processing
> page, where you then take the form values and insert them into a table.
>
> Here's VERY simplified code (ignore the

 tags):

>

> 


> 

> 


> 

> 

> 


>

> 

> 
> $db = mysql_connect("servername", "username", "password");

> if (!$db){echo "error!";}

> mysql_select_db("databasename");

> $query = "insert into tbl_data values

> ('".$HTTP_POST_VARS["title"]."')";

> $result = mysql_query($query);

> ?>

> 




OK - I can understand this.  Just wondering though if this is safe security 

wise?  Especially if I have to provide a username and password?  Would this 

be the users username and password, or another password with write 

permissions to the database?  Are write permissions automatically granted to 

new users so that their data can be written into the database, or am I 

confusing some issues here?



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

>

> It's OK. In your database table, just set up a field that has a char(1)

> data type and length, and store either "Y' or "N" in that field. Since

> you're using an HTML form, you can just set the value of a radio button

> set to "Y" or "N" - thereby eliminating the chances of a user inputting

> anything else in that field.



Yes thanks for that, in the interim I changed "set" to "enum" because set 

allows you to choose more than one value, but I guess this wouldn't matter 

at the user end.



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

>

> You'll probably want two tables for your orders, one to capture the

> general order info (order number, customer name, shipping and billing

> addresses, order total, shipping type, etc.), and a second table -

> related! - that keys on the order number, and holds data about each and

> every product selected. Here's an example:

>





I understand the principles behind that, and have been working on 7 tables 

so far).  Some of my fields occur in several tables, but I have "freshly" 

created such fields in each table, rather than copied then, so at present I 

am not sure if they are reading the data from the table where a particular 

field is the Primary Key - this is important for consitency I guess.  At the 

moment I am having real trouble forming relations between tables and fields, 

and understanding how to get a particular field to automatically "sum" or 

"sum if" the contents of another field, but I am still reading and 

learning - the problem being that most directions are for CLI not 

phpmyadmin.



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

>

> You'll be able to do that by parsing the info that each visitor to your

> site generates. It is possible, but I'm not sure how to do it exactly.

>

>> I may also need to know and be able to limit how many times a user can

>> download a particular file.

>

> You can set up a user account table in your database, and give hime or

> her a set number of downloads associated with a specific product and a

> specific purchase. Decrement that number each time the download is

> accomplished. When it gets to zero, then the customer will need to

> purchase another copy.



Yes, I can see how that would work, thanks!



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

>

> My advice is to use another table. MySQL is really cool, in that you

> can make as many as you need, and just "relate" them to other tables.

> You can then use the data from your "Manufacturing Totals" table, and

> load that into your products table every time you create a new batch.

> You can do it with PHP code easily enough.



Its the relating I think I am having problems with.



>> 3) Sales Information:

>

> 

>

>> I have a date field, will this automaticallty write the purchase date to 

>> the

>> database?  If not, how is that done?

>

> Yes, PHP and MySQL are easy to use when recording date/time info.

> Again, the easiest way is to generate an order time, then insert it

> into the MySQL order tabel in the appropriate field.



OK so that's the actual purchase date, like when the record was created, 

rather than the current date, right?  Just by using the "date" field?



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

>

> This is exactly what relates the tables together. In your Customer

> Table, each customer is assigned a Member Number. This is a "Primary

> Key" in the Customer Table. In the Order Table, the Member Number

> refers back to the Customer Table, and in the Order Table, it is known

> as a "Foreign Key."

>

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

>

> Yep, fine! Again, now you're relating the Product Table to the Order

> Table using the Catalog Number as a Foreign Key. Perfect!



Thanks a lot for your help.  I just need to read up a bit more on how to 

relate the tables, and ensure when Foreign Keys are used they refer back to 

the original data in the table where they are the Primary key.



Regards,

C.B.

Re: Can anyone help a newbie figure out MySQL?

Thanks for the help on the SQL Injection security! Man, I'm learning a
lot every time I log on here!

Apologies for the "hazardous code," and hope everyone can make the
appropriate adjustments. Me - I'm doing that now to my stuff... ;)

Re: Can anyone help a newbie figure out MySQL?

I should also mention that I use a separate "connect.php" file - and
use the PHP statement:

include("connect.php");
//rest of PHP/MySQL code here...

This hides the server name, username, password and database name from
normal folks - probably not from the guys n' gals on this forum,
though! - and also makes site management a LOT easier. I just have to
change the single file if I change any of the connection parameters,
and not every file that has a connection routine.

Hope that helps!

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.