Week 6

MySQL Notes:

• MySQL is an open source database the is probably the most commonly used database for the web. If you make commercial software where MySQL is required, then you need a commercial license.  (but the cost is reasonable).  If you are making open source software where you are freely distributing your software, then a license is not required.
• Get MySQL http://www.mysql.com
• Install it to C:\mysql
• Go to the C:\mysql\bin folder and make a shortcut of winmysqladmin.exe and place it on your desktop.
• Under windows, MySQL runs as a service called "MySql".  It is set normally to autostart, so it runs in the background as soon as you boot up your computer.
• To use MySQL with PHP, go into C:\php.ini and uncomment extension=php_mysql.dll
  Also change the variable extension_dir to read extension_dir = "C:\php\ext"

MySQL Administrator Notes:

• MySQL Administrator is a graphical windows interface to see the status of the MySQL server, add users and set permissions, and create databases.
• Get MySQL Administrator http://www.mysql.com
• Your instructor will walk you through the steps of configuring MySQL Administrator to connect up to MySQL databases.

phpMyAdmin Notes:

• phpMyAdmin is a web based management tool to administer and create MySQL databases.  From phpMyAdmin you can even create tables, put in data, and perform queries.
• Get phpMyAdmin http://www.phpmyadmin.net
• Install it into your website using the default folder name.
• Make a link to index.php within your site.
• Modify config.inc.php to look similar
  $cfg['PmaAbsoluteUri'] = 'http://localhost/phpMyAdmin/';  <-- wherever your phpMyAdmin is.
  $cfg['Servers'][$i]['user'] = 'username'; // MySQL user name <-- whatever it happens to be.
  $cfg['Servers'][$i]['password'] = '******'; // MySQL password <-- whatever it happens to be.

General:

• Keep in mind that if you install MySQL on your local laptop AND you use a MySQL database on a web hosting server, that you have TWO distinct databases.

PHP My Admin video:

Tech Learning Archive
PHPMyAdmin Tutorial For Beginners 2020 | Creating a MySQL Database | Code Lab 001 (27:37)
PHPMyAdmin Tutorial For Beginners 2020 | Creating a MySQL Database | Code Lab 001 (no ads)

Sample zip file. Unzip to use. 1000 records compliments of Ryan Olsen.

Create sample data www.generatedata.com.

Government data sets www.data.gov.

This article taken from Builder.com.

Data stored in a database comes in all shapes and sizes. Some fields store only numbers, some only text, and others a combination of the two. Some databases also support specialized types: dates and times, binary strings and Booleans.

Selecting from available data types to find the best match for your data is an important part of database design, because such type selection affects the efficiency and performance of your RDBMS. Therefore, it's extremely important to be fully aware of the options available to you in your RDBMS, and to select the most appropriate data type for your storage needs at the time of design itself.

That's where this document comes in. It outlines the most important data types supported by MySQL, one of the most popular free RDBMS currently available, and describes when and how each should be used. It thus serves as a ready resource to help you in creating an optimal design for your databases.

For a complete list and detailed descriptions, see the MySQL manual. You should also read the article entitled Choosing the Right Type for a Column.

Lab 6

This lab is to test your knowledge of being able to create a table in a MySQL database and populate it with data.  You can do this by using phpMyAdmin that is available in cPanel for your website. Alternately you can use MySQL administrator running locally on your laptop and connecting to your remote database on your web server.  http://tools.ridgewater.net.  The name of your database will be in the format of "lastnfir_" which is the first 5 characters of your last name followed by the first 3 characters of your first name.  MySQL users that you create will also be the same format.  Example: database and usernames for Greg Smith would be "smithgre_".  

Create a database named "lastnfir_contactlist"

Create a MySQL user named "lastnfir_cluser1"

Create a table named "myContacts"

Create these fields:
ID - Unique ID number for each entry.  This field is the primary key.
firstName- The person's first name.
lastName - The person's last name.
addrLine1 - The first line of the address.
addrLine2 - The second line of the address.
city - The city the person lives in.
state - The state or province the person lives in.
country - The country the person lives in.
homePhone - The person's home telephone number.
mobilePhone - The person's mobile phone number
email - The person's email address.

Import the sample file so that you have at least 1,000 records. 

Add at least 5 more records by hand entry to your MyContacts table. Populate all the fields.

Assignment is due 1 week from when it was assigned.  50% penalty if late.