Transcript Installing and Using MySQL and phpMyAdmin

Installing and Using MySQL and
phpMyAdmin
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!
Today...
• Installing MySQL server on your computer
(easy!)
• Installing phpMyAdmin (not quite as easy,
but still not too bad)
• All about MySQL queries
Download and Install MySQL
Get an msi installer from this page:
http://dev.mysql.com/downloads/mysql/
“Standard installation” with default settings should work just
fine.
Be sure to remember your password!
Command Line Client
Open the Command Line Client.
Try typing the command “show databases.” This is what you’ll
see:
Creating a Database
Make up a name for your first database and try the command
“create database databasename.”
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 you’d like).
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).
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.
Using “where”
(as well as “and”, “or”, “not”)
Delete
“Delete” works just like “select.”
Use with caution.
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.
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.
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.
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?
Installing phpMyAdmin
• phpMyAdmin provides a useful GUI so we
don’t have to use the command line all the
time
• Download phpMyAdmin at
http://www.phpmyadmin.net/home_page/do
wnloads.php
• Extract the files into a folder named
“phpMyAdmin” in your Apache server
folder
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.
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’
Some Housekeeping
• Add the Multi-Byte String and Mcrypt
extensions to PHP if you haven’t already.
– To do this, go to Control Panel -> 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
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/InstallphpMyAdmin-on-Your-Windows-PC
phpMyAdmin Features
Create a Table
Add data
Browse
Search
Any Query
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.
• 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
Useful Links
•
•
•
•
MySQL download: http://dev.mysql.com/downloads/mysql/
phpMyAdmin download:
http://www.phpmyadmin.net/home_page/downloads.php
mySQL command line commands:
http://www.analysisandsolutions.com/code/mysql-tutorial.htm#creating
Getting phpMyAdmin to work on Windows: http://www.wikihow.com/InstallphpMyAdmin-on-Your-Windows-PC