Can anyone help a newbie figure out MySQL?

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
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,

Re: Can anyone help a newbie figure out MySQL?

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

Quoted text here. Click to load it

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 <pre> tags):

<!-- This is the HTML Form Code -->
<form name="form1" method="POST" action="process.php">
<input name="title" type="text" id="title">
<input name="submit" type="submit" value="Submit">

<!-- This is the PHP Process Code -->
$db = mysql_connect("servername", "username", "password");
if (!$db){echo "error!";}
$query = "insert into tbl_data values
$result = mysql_query($query);

Quoted text here. Click to load it

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.

Quoted text here. Click to load it

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

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
weight: 55

item_id: 10725
order_id: 1019 //See how this relates to Data Table 1?
product_number: ASDF-12345
unit_cost: 7.50
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

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.

Quoted text here. Click to load it

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.

Quoted text here. Click to load it

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.


Quoted text here. Click to load it

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.

Quoted text here. Click to load it


Quoted text here. Click to load it

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.

Quoted text here. Click to load it

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

Quoted text here. Click to load it

Yep, fine! Again, now you're relating the Product Table to the Order
Table using the Catalog Number as a Foreign Key. Perfect!

Quoted text here. Click to load it

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:


Quoted text here. Click to load it

Re: Can anyone help a newbie figure out MySQL?

Dano wrote:
Quoted text here. Click to load it

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:

Bill K.

Re: Can anyone help a newbie figure out MySQL?

Hi Dano,

Thanks for your enthusiastic response!

Quoted text here. Click to load it

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?

Quoted text here. Click to load it

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.

Quoted text here. Click to load it

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

Quoted text here. Click to load it

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

Quoted text here. Click to load it

Its the relating I think I am having problems with.

Quoted text here. Click to load it

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?

Quoted text here. Click to load it

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.


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:

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


Site Timeline