• Getting Started
• Basics
• Arrays
• Loop/Branch
• Functions
• OOP
• Libraries
• Files
• MySQL
• SQL
• PHP/MySQL
• Regex
• Email
• Date/Time
• Web
• Forms
•  
•  
•  

Managing a MySQL Database

• Understanding RDBMS Terminology

• Command Line Access

• Using phpMyAdmin

• Configuration

• Creating a database

• Managing tables - Part 1

• Managing tables - Part 2

• Understanding Indexes and Relationships

• Establishing Relationships and Constraints

• LAB: Defining Tables and Relationships for a Website

Understanding RDBMS Terminology

• What are some common RDBMS (Relational DataBase Management System) terms?

  
  

  table, row, column, key, primary key, foreign key

• What is the point in learning these terms?

  
  

  Understanding RDBMS terms will help you design functional databases which are highly efficient and flexible

• What is an RDBMS "database"?

  
  

  A database is like a spreadsheet with multiple tabs

• What is an RDBMS "table"?

  
  

  An RDBMS table is like a single spreadsheet on a single tab

• What is an RDBMS "row"?

  
  

  An RDBMS row is like a single row in a spreadsheet

• What is an RDBMS "column"?

  
  

  An RDBMS column is like a column in a spreadsheet

• What is a "key" and what are the different types?

  
  

  A key the one or more columns which uniquely identify a row,
  a Primary Key is a key which used to organize the table,
  a Foreign Key is one or more columns which identify rows in another table

Command Line Access

• How do you access a MySQL database from the command line?

  
  

  Use the "mysql" command

• How do you get help?

  
  

  Type "?"

• How do you access a database?

  
  

  Type "show databases;" and then "use name_of_database;"

• How do you get information on tables in a database?

  
  

  Type "show tables;"

• How do you get information on columns in a table?

  
  

  Type "show columns from name_of_table;"

• How do you get out of MySQL command line?

  
  

  Type "exit"

Using phpMyAdmin

• What is phpMyAdmin?

  
  

  phpMyAdmin is an open source administration interface for MySQL written in PHP

• How do you access phpMyAdmin?

  
  

  From the XAMPP console start MySQL and select "Admin",
  or from a browser locate "phpMyAdmin" on your web server

• What is the benefit to using phpMyAdmin?

  
  

  phpMyAdmin presents an easily understood graphical interface which is used to manage MySQL databases

• How can you use phpMyAdmin to import data from a spreadsheet?

  
  

  Click on "Import", choose the file, and select "CSV" under "Format"

• How can you use phpMyAdmin to perform direct SQL statements?

  
  

  Click on "SQL" and start typing

Configuration

• How do you configure phpMyAdmin?

  
  

  Create a directory "phpMyAdmin/config", copy config.inc.php into it,
  then run "phpMyAdmin/setup/" from the browser

• How do you make configuration changes permanent?

  
  

  When using "setup", save the changes, and then move config.inc.php
  from the phpMyAdmin/config/ folder back to the phpMyAdmin/ folder.
  Warning for Mac users: PHP does not seem to like Mac end of lines character ("\r"). So ensure you choose the option that allows to use the *nix end of line character ("\n")
  in your text editor before saving a script you have modified.

• Why would you want to configure phpMyAdmin?

  
  

  For example, you might want to switch to the "mysqli" extension, which gives you added features when using phpMyAdmin. You might also consider changing the "collation" settings to "utf-8"

• How do you configure MySQL?

  
  

  Use phpMyAdmin "status" to get advice on current settings.
  Click on the links for the documentation associated with settings you are considering changing.
  Modify the "my.ini" file (use the XAMPP panel). Restart MySQL.

• Why would you want to change the MySQL configuration?

  
  

  For the most part the settings are installed to be system specific and usually do not need to be changed. You may notice sluggish performance, however, and find that based on the usage on your server you need to make minor changes to improve performance.

Creating a database

• What exactly is a database?

  
  

  A database is a collection of tables, indexes and links (relations)
  stored on the server in a manner dictated by the storage engine

• What is meant by "collation"?

  
  

  "collation" refers to how text data is physically stored. Originally ASCII was used, however ASCII due to its single byte nature, is unable to cope with international character sets.

• What is the best "collation" to use?

  
  

  You should use the language version which matches what your website users will be using. utf8_* is recommended for a best match with PHP, however there are caveats.
  See: http://stackoverflow.com/questions/367711/what-is-the-best-collation-to-use-for-mysql-with-php.
  See also: http://code.openark.org/blog/mysql/mysqls-character-sets-and-collations-demystified

• What is meant by privileges?

  
  

  Database privileges involve creating users and assigning rights to one or more databases and/or tables

• How do you create a database and privileges using phpMyAdmin?

  
  

  Select "Database" and then "Create", then select "Privileges" and then "Add User".
  You can also add a user and create a database of the same name at the same time.

Managing tables - Part 1

• How can you create a table using phpMyAdmin?

  
  

  Click on "Create Table" and enter the name and data types of the desired columns

• What are some of the commonly used data types for columns?

  
  

  Common types include "int", "varchar", "text" and "date".
  The primary key is often "int" with an attribute of A_I (auto-increment)

• What are the column attributes?

  
  

  binary, unsigned, zero fill and update timestamp

• Is there a limit on the number of columns in a table?

  
  

  Yes: MySQL sets a hard limit of 4,096

• What are "storage engines"?

  
  

  A "storage engine" is the means by which the database is physically stored.
  Storage engines in MySQL include "InnoDB", "MyISAM", "MEMORY", etc.

Managing tables - Part 2

• How do you add new columns later?

  
  

  Select "Structure" and then "At End of Table", "At Beginning of Table", or "After" and then click "Go"

• What if you need to alter the structure?

  
  

  Select "Structure" and then in the row for that column "Change" or "Drop"

• How can you review data in the table?

  
  

  Select "Browse"

• How can you add data to the table?

  
  

  Select "Import" and choose a source file (e.g. CSV)

• How can you edit or remove data from a table?

  
  

  Select "Edit" or "Delete" when using "Browse",
  or select "SQL" and issue an SQL command for UPDATE or DELETE

• What if you need to rename the table?

  
  

  Select "Operations" and then either "Table Options - Rename Table to" or "Move table" or "Copy table" etc.

Understanding Indexes and Relationships

• What is a "relationship" in SQL terms?

  
  

  A "relationship" is a link between tables using sets of keys

• What types of relationships are possible?

  
  

  One-to-one, one-to-many, and many-to-many

• What is meant by "parent" and "child" in this context?

  
  

  Parent is the "one" table, and "child" is the table where "one" or "many" related rows are found

• What is an "index"?

  
  

  An index is a separate lookup file which allows MySQL to
  quickly access data instead of having to sequentially scan the table

• What are the most common types of indexes?

  
  

  Primary, Unique, and Index

• Why would you want to establish an index on a column?

  
  

  Adding an index on one or more columns is required when establishing a relationship,
  and also is useful when you have a large amount of information in the table

Establishing Relationships and Constraints

• Why would you want to establish a relationship in MySQL?

  
  

  Constraints are a built-in means of maintaining database integrity.
  In order to enforce a constraint, you would need to establish a relationship.

• What is an "orphaned" row and what is the danger?

  
  

  An "orphaned row" is where the parent table row has been deleted,
  but the child table rows are still in existence

• What should you do before setting relationships?

  
  

  1. The database storage engine must be set to "InnoDB"
  2. Create either a Primary or Unique index on the column(s) in the parent table.
  3. Both sets of keys must have the same data types and length.

• What are the options for a relationship?

  
  

  On update or on delete: cascade, set null, no action, and restrict

• How do you go about setting up a relationship using phpMyAdmin?

  
  

  Select the database (use breadcrumbs at top), then "Designer", then click on the icon for "Create Relation", select the parent table, and then the "foreign key" in the child table.
  You can also select the child table, "Structure", "Relation View", and create a link back to the parent

• How do you change or delete a relationship?

  
  

  Select the database, then "Designer". Locate relationship link on the child table and click.
  You can delete and then re-add the relationship. Alternatively, select the child table, "Structure", and then click on "Relation View", which lets you edit directly.

LAB: Defining Tables and Relationships for a Website

• What is the purpose of the lab?

  
  

  In this lab you will be defining a series of tables and relationships
  which will be used in later labs

• Create a database "sweetscomplete"

  
  

  • From the browser go to http://localhost/phpmyadmin
  • Select "users" - "Add User"
  • Create a user "sweetscomplete", use "localhost" and enter a password
  • Select "Create database with same name ... "
  • Click on "Add User"
  • Select the database and set the collation to utf8_general_ci
  • Make sure the "Engine" is set to "Innodb"

• Create a table "products" ...

  
  

  Name	Type	Size	Null	And ...
  product_id	int	8	N	Primary Key, Auto Increment
  sku	char	16	Y
  title	varchar	128	N	Index
  description	varchar	4096	Y
  price	decimal	6,2	N
  special	int	1	N	default: as defined: 0
  link	varchar	128	Y

• Create a table "purchases" ...

  
  

  Name	Type	Size	Null	And ...
  purchase_id	int	8	N	Primary Key, Auto Increment
  transaction	char	8	N
  product_id	int	8	N	Index
  user_id	int	8	Y
  date	timestamp		N
  quantity	int	6	N
  sale_price	decimal	8,2	N

• Import data ....

  
  

  • Select the "products" table - "Import" - "Choose File"
  • From the WorkingFiles folder for this chapter select mysql_project_products.csv
  • Make sure "Format" indicates "CSV" and click "Go"
  • Select the "purchases" table - "Import" - "Choose File"
  • From the WorkingFiles folder for this chapter select mysql_project_purchases.csv
  • Make sure "Format" indicates "CSV" and click "Go"

• Set up a relation ...

  
  

  • Select "Purchases" - "Structure" and click on "Relation View"
  • Next to "product_id" click "Foreign Key Constraint"
  • Select sweetscomplete.products.product_id
  • ON DELETE: CASCADE | ON UPDATE: CASCADE
  • Click "Save"
  • Test by inserting a couple of rows and then trying to delete

• Verify results ...

  
  

  Browse the "products" and "purchases" tables.
  If you do not see a list of products, review the preceding procedures