Problem: Karen and her husband have a joint checking account. She records the checks she writes in her checkbook, and he records his. Neither really knows what the current balance is.
Solution: Karen will create a MySQL table where they can each record the checks they have written and check the current balance.
Data: Karen looks at a check and identifies the following fields that need to be recorded: check number, date, payee, amount, purpose. In addition there will be deposits and withdrawals. Karen decides that she can use one table for all transactions. Since deposits and withdrawals don't have a check number to use as the primary key, she decides to use an auto number.
CREATE TABLE Checking(
TransID INT NOT NULL AUTO_INCREMENT,
CheckNumber INT,
TransDate DATETIME,
Amount DOUBLE,
Payee VARCHAR(20),
Purpose VARCHAR(50), Type VARCHAR(20),
PRIMARY KEY(TransID)
)
She has a web page where she can manage her database and she pastes this command directly into the query form.
If she prefers, she can create this table by creating a page called createChecking.php, (click link to see the text version) then open that page in the browser to execute the code.
She can also execute the command SHOW TABLES to see if the table has been created, and the command SHOW COLUMNS FROM Checking to see what fields are in the table.
Note: In these examples MySQL command words are shown in upper case. The command show tables would also work. However, the names of the tables are case sensitive.