Information: Adding | Updating | Retrieving | Removing | PHP and MySQL
Creating/Adding Information - Page 73-81

Creating a Database Deleting a Database Adding Tables to a Database
A list of tables used in the two databases
PetCatalog MemberDirectory
Pet Member
PetType Login
PetColor  

With the CREATE TABLE query you should include a list of the column names with their definitions.
The information for each column is separated by a coma.
A coma is not needed for the different definitions syntax, just to separate the column names.
Look at the examples below.

Each column name is followed by its data type. and additional information.
The most commonly used is NOT NULL.
Here is a list of definitions that can be used:

Definitions (Page 075)
Definitions Descriptions
NOT NULL column must have an entry, it cannot be empty.
DEFAULT value this value is stored with the column when the row is created
if no other value is entered by the user.
AUTO_INCREMENT this creates a sequence number as each row is added.
it increases by one integer from the last row entered.
You can override this value by assigning a specific value to the column .
UNSIGNED use this definition if the numeric field will never be a negative number.

075 The last line in the CREATE TABLE query is PRIMARY KEY(columnname) which indicates the key to the table.
       If you are using two or more column names to define the Primary Key use the following format:
           PRIMARY KEY(columnname1, columnname2)

075 There are two sets of parentheses used, one for the column name of the primary key and one that surrounds all the column names.

If you are using the localhost/mysql_send.php to create the table,
     make sure you include the Databasename of petCatagory or MemberDirectory.
The five queries to create the tables are as follows:
For the PetCatalog database: 

CREATE TABLE `Pet` (
`petID`          SERIAL      NOT NULL,
`petName`        CHAR(25),
`petType`        CHAR(15)    NOT NULL,
`petDescription` VARCHAR(255),
`price`          DECIMAL(9,2),
`pix`            CHAR(20),
PRIMARY KEY  (`petID`)
)

CREATE TABLE `PetType` (
  `petType`         CHAR(15)    NOT NULL,
  `typeDescription` VARCHAR(255),
  PRIMARY KEY  (`petType`)
) 

CREATE TABLE `PetColor` (
  `petName`  CHAR(25) NOT NULL,
  `petColor` CHAR(15) NOT NULL,
  `pix`      CHAR(20),
  PRIMARY KEY  (petName,petColor)
)
For the MemberDirectory database:
CREATE TABLE `Member` (
  `loginName`  VARCHAR(20) NOT NULL,
  `password`   CHAR(255)   NOT NULL,
  `createDate` DATE        NOT NULL,
  `lastName`   VARCHAR(50),
  `firstName`  VARCHAR(40),
  `street`     VARCHAR(50),
  `city`       VARCHAR(50),
  `state`      CHAR(2),
  `zip`        CHAR(10),
  `email`      VARCHAR(50),
  `phone`      CHAR(15),
  `fax`        CHAR(15),
  PRIMARY KEY  (`loginName`)
)

CREATE TABLE `Login` (
  `loginName`  VARCHAR(20) NOT NULL,
  `loginTime`  DATETIME,
  PRIMARY KEY  (loginName,loginTime)
)
076 To see the tables that were added to a database or to get a list of tables in the entire database use the following query:
   SHOW TABLES
If you are using the localhost/mysql_send.php to list the tables,
     make sure you include the Databasename of petCatagory or MemberDirectory.

076 To see the structure of a table use a query in this format:
   DESCRIBE tablename
If you are using the localhost/mysql_send.php to list the structure of a table,
     make sure you include the Databasename of petCatagory or MemberDirectory.

076 To remove any table, use a query in this format:
   DROP TABLE tablename
If you are using the localhost/mysql_send.php to drop a table,
     make sure you include the Databasename of petCatagory or MemberDirectory.

076 CHANGING THE DATABASE STRUCTURE
By using the ALTER query, you can change the name of your table; add, drop, or rename a column; or change the data type or other attributes of the column.
076 The basic format for this query is ALTER TABLE tablename, followed by the specific changes.
The following table shows the changes you can make.

Changes you can make with the ALTER query (Page 77)
Change Description
ADD columnname definition Adds a column; definition includes the data type and optional definitions.
ALTER columnname SET DEFAULT value Changes the default value of the column.
ALTER columnname DROP DEFAULT Removes the default value for a column.
CHANGE columnname newcolumnname definition Changes the definition of a column and renames the column; definition includes the data type and optional definitions.
DROP columnname Deletes a column, including all the data in the column. The data cannot be recovered.
MODIFY columnname definition Changes the definition of a column, definition includes the data type and optional definitions.
RENAME newtablename Renames a table

077 Example: Suppose you defined lastName with VARCHAR(20) in the Member table.
       You were told to change the width to 50. Use the following query:
        ALTER TABLE Member MODIFY lastName VARCHAR(50)

077 MOVING DATA INTO AND OUT OF THE DATABASE

The MySQL database responds to four types of requests (Page 78)
Requests Description
Adding information Adding a row to a table.
Updating information Changing information in an existing row, including adding data to a blank field in an existing row.
Retrieving information Looking for data.
Removing information Deleting data from a database.


078 ADDING INFORMATION
Adding one row at a time - use the INSERT query
adding lots of rows in one shot - use the LOAD query

079 Adding one row at a time
The INSERT query tells MySQL which table to place the data in and what the values are for the columns or fields.
The format for the INSERT query follows:
INSERT INTO tablename (columnname, columnname, ... columnname)
      VALUES (values, value, ... value)
example:
INSERT INTO petColor (petName, petColor, pix)
      VALUES ("Unicorn", "white", "/pix/unicorwhite.jpg")

another example:
INSERT INTO pet (petName, petType, petDescription, price, pix)
VALUES ("Unicorn", "Horse", "Spiral horn centered in forehead", "10000", "/pix/unicorn.jpg")

If you are using the localhost/mysql_send.php to add data to a table,
     make sure you enter one query at a time, not the entire list as displayed below.
     make sure you include the Databasename of petCatagory.

To enter data for the pet table 
INSERT INTO pet (petName, petType, petDescription, price, pix)
   VALUES ("Unicorn", "Horse", "Spiral horn centered in forehead", "10000", "/pix/unicorn.jpg")
;
INSERT INTO pet (petName, petType, petDescription, price, pix)
   VALUES ("Pegasus", "Horse", "Flying; wings sprouting from back", "15000", "/pix/pegasus.jpg")
;
INSERT INTO pet (petName, petType, petDescription, price, pix)
   VALUES ("Pony", "Horse", "Very small; half the size of standard horse", "500", "/pix/pony.jpg")
;
INSERT INTO pet (petName, petType, petDescription, price, pix)
   VALUES ("Asian Dragon", "Dragon", "Serpentine body", "30000", "/pix/asiandragon.jpg")
;
INSERT INTO pet (petName, petType, petDescription, price, pix)
   VALUES ("Medieval Dragon", "Dragon", "Lizard-like body", "30000", "/pix/medievdrago.jpg")
;
INSERT INTO pet (petName, petType, petDescription, price, pix)
   VALUES ("Lion", "Cat", "Large; maned", "2000", "/pix/lion.jpg")
;
INSERT INTO pet (petName, petType, petDescription, price, pix)
   VALUES ("Gryphon", "Cat", "Lion body; eagle head; wings", "25000", "/pix/gryphodrago.jpg")
;
To enter data for the petType table 
INSERT INTO petType (petType, typeDescription)
   VALUES ("Cat", "Beautiful and dignified. Independent. Range in size from large lions to small cats. Carnivorous.")
;
INSERT INTO petType (petType, typeDescription)
   VALUES ("Dog", "Strong, Courageous. Extremely intelligent. Can be trained for very useful work, such as watch dog or seeing-eye dog. Or bringing in the newspaper. Includes wild species, such as coyotes and wolves.")
;
INSERT INTO petType (petType, typeDescription)
   VALUES ("Dragon", "Magnificent, large reptiles. Dragons fly tirelessly and many are large enough to ride. Very goog watch animals.")
;
INSERT INTO petType (petType, typeDescription)
   VALUES ("Fish", "Many colorful varieties. Relaxing and mesmerizing to watch. Size varies from great white sharkes to guppies.")
;
INSERT INTO petType (petType, typeDescription)
   VALUES ("Horse", "Beautiful four legged animals that you can ride. Includes magical varities such as Unicorn and Pegasus.")
;
INSERT INTO petType (petType, typeDescription)
   VALUES ("Lizard", "Small reptiles. Fascinating to watch. Requires warm environment. Eat vegatables and bugs.")
;
To enter data for the petColor table 
INSERT INTO petColor (petName, petColor, pix)
   VALUES ("Unicorn", "white", "/pix/unicorwhite.jpg")
;
INSERT INTO petColor (petName, petColor, pix)
   VALUES ("Unicorn", "silver", "/pix/unicorsilve.jpg")
;
INSERT INTO petColor (petName, petColor, pix)
   VALUES ("Fish", "gold", "/pix/fishgold.jpg")
;

080 ADDING A BUNCH OF DATA
Large amounts of data can be added to a table by using the LOAD query.
The default deliminator between fields is the tab character, however you can change that if you like.
The end of line character is the default for the end of a row.

An example of a tab deliminated file for the pet table follows:

Unicorn<TAB>
horse<TAB>Spiral horn centered in forehead<TAB>5000.00<TAB>/pix/unicorn.jpg
Pegasus<TAB>
horse<TAB>Winged<TAB>8000.00<TAB>/pix/pegasus.jpg
Lion<TAB>cat<TAB>Large; mane on neck<TAB>2000.00<TAB>/pix/lion.jpg
The basic form of the LOAD query follows
LOAD DATA INFILE "path/datafilename" INTO TABLE tablename
The text file must be on the server.
081 If the filename does not include the path, MySQL uses the path from the table definition file, called tablename.frm.
By default this file is located in a directory named for your database, the previous example would use a directory named PetDirectory.
The PetDirectory folder will be inside the data folder, the data folder is located in the main directory where MySQL is installed.
For example, if the text file was named data.dat, the LOAD command might look for the file at:
C:\Program Files\MySQL\MySQL Server 5.0\data\PetDirectory\data.dat

081 The LOAD query can use optional phrases to change default delimiters such as:
FIELDS TERMINATED BY 'character'
FIELDS ENCLOSED BY 'character'
LINES TERMINATED BY 'character'

081 Suppose the data.dat file as described above for the pet table was going to have it's fields separarted by comas, use the LOAD query as follows:
LOAD DATA INFILE "pets.dat" INTO TABLE pet
     FIELDS TERMINATED BY ','

081 To use the LOAD DATA INFILE query, the MySQL account must have the FILE privilege on the server host.

081 You can also load data from a text file on your local computer by using the word LOCAL as follows:
LOAD DATA LOCAL INFILE "path/datafilename"
      INTO TABLE tablename
081 You must include a path to the file. Use forward slashes for the path. If you get an error message when sending this query, LOCAL may not be enabled.

081 To look at the data after its loaded, use SELECT * FROM pet.
This site is for the private use of my students. (2008)

Information: Adding | Updating | Retrieving | Removing | PHP and MySQL