Installing and Using MySQL and phpMyAdmin

1
Installing and Using MySQL and phpMyAdmin
2
Last Time...

• Installing Apache server
• Installing PHP
• Running basic PHP scripts on the server
• Not necessary to have Apache and PHP working to
  understand this webcast, but we will be putting
  everything together next week!

3
Today...

• Installing MySQL server on your computer (easy!)
• Installing phpMyAdmin (not quite as easy, but
  still not too bad)
• All about MySQL queries

4
Download and Install MySQL
Get an msi installer from this page http//dev.my
sql.com/downloads/mysql/ Standard installation
with default settings should work just fine. Be
sure to remember your password!
5
Command Line Client
Open the Command Line Client. Try typing the
command show databases. This is what youll see
6
Creating a Database
Make up a name for your first database and try
the command create database databasename.
7
Creating a Table
Type use databasename to switch to your
database. Use the command create table
tablename (fields) to make a table in your
database. To make an integer field, say
fieldname integer in the parentheses. To make a
text field, say fieldname char(20) (this makes
a field where the maximum number of characters is
20- you can make a different limit if youd like).
8
Adding Data
Use the command insert into tablename
(fieldnames) values (values) to add data to the
table. Use single quotes around text. You can
add multiple entries at a time (see the example
below).
9
Retrieving Data
Type select fieldname from tablename to see the
information in the table. Use a comma-separated
list to view multiple columns in the table. Use a
to see all columns.
10
Using where (as well as and, or, not)
11
Delete
Delete works just like select. Use with
caution.
12
Multiple Tables
You can create many tables within the same
database. A common practice is to use ID numbers
to reference information from other tables.
13
Multiple Tables
You can create many tables within the same
database. A common practice is to use ID numbers
to reference information from other tables.
14
Join Queries
Queries can use more than one table. The first
query shows which names are paired with which
books. select addresses.name, books.book from
addresses, books where addresses.ID
books.person Challenge write a query that
picks out the e-mail addresses of people who like
Lord of the Rings.
15
More Examples
Try to write queries to display the following
information - One column just all the e-mail
addresses - Two columns names and corresponding
favorite bands - One column people who liked The
Beatles also liked which books?
16
Installing phpMyAdmin

• phpMyAdmin provides a useful GUI so we dont have
  to use the command line all the time
• Download phpMyAdmin at http//www.phpmyadmin.net/h
  ome_page/downloads.php
• Extract the files into a folder named
  phpMyAdmin in your Apache server folder

17
Before editing config file...

• Go to localhost/phpmadmin/index.php
• You should see the phpMyAdmin login page, but
  with error messages. You might not be able to
  login yet.

18
Editing the Config File

• Find config.sample.inc.php in your phpMyAdmin
  folder.
• Find the line that says cfg'blowfish_secret'
  '' / YOU MUST FILL IN THIS FOR COOKIE AUTH! /
• Put any word you want into the quotes, (i.e.
  cfg'blowfish_secret' caddywhompus

19
Some Housekeeping

• Add the Multi-Byte String and Mcrypt extensions
  to PHP if you havent already.
• To do this, go to Control Panel -gt Uninstall
  Programs, select PHP, and click Change. The PHP
  installation wizard will come up. Select the
  Multi-Byte String and Mcrypt extensions this
  time.
• Copy the file libmcrypt.dll to the
  Windows\System32 directory
• Restart Windows

20
Logging in

• Now go back to localhost/phpMyAdmin/index.php
• You should be able to login using username root
  and the password you made when you installed
  MySQL.
• If you have problems on a Windows OS earlier than
  Windows 7, this site might help
  http//www.wikihow.com/Install-phpMyAdmin-on-Your-
  Windows-PC

21
phpMyAdmin Features
Create a Table
22
Add data
23
Browse
24
Search
25
Any Query
26
So Why Learn About the Command Line?

• In some cases, using the command line is faster
• We need to know how to write MySQL queries in
  order to make webpages that interact with our
  MySQL database.

27

• Suggestions for this week
• Install MySQL and phpMyAdmin
• Play around with making databases
• Next Time
• HTML and PHP code we need to make the database
  work with our website

28
Useful Links

• MySQL download http//dev.mysql.com/downloads/mys
  ql/
• phpMyAdmin download http//www.phpmyadmin.net/hom
  e_page/downloads.php
• mySQL command line commands http//www.analysisan
  dsolutions.com/code/mysql-tutorial.htmcreating
• Getting phpMyAdmin to work on Windows
  http//www.wikihow.com/Install-phpMyAdmin-on-Your-
  Windows-PC