MySQL and phpMyAdmin

1
MySQL and phpMyAdmin
2
Navigate to http//webapptst.lasalle.edu/pma and
log on (username pmadmin)
3
The pma (phpMyAdmin) interface
4
Use the drop-down list to select the test database
The mysql database is administrative, and we
dont have permissions for it. There are even
more databases here that are not shown to this
user.
The (3) after test indicates that currently the
test database contains three tables.
5
The test database in the pma interface
We see in the first column above a list of the
tables in the test database, in the second column
one finds buttons allowing particular actions
on those tables, in the third column one can see
the number of records in the table. The
remaining columns will be of less interest to us
as we begin.
6
Table Actions

1. The browse button will show that all of the data
   in the table. (One can also update and delete the
   data here.)
2. The search button will set up a query based on
   that table.
3. The insert button allows one to add new records
   to the table.
4. The properties button allows one to the tables
   structure (the metadata), i.e. what are the
   fields, their types, how long can they be, etc.
5. The empty button allows one to delete all of the
   records in the table. Be careful it will give a
   little warning. (The table structure remains.)
6. The drop button allows one drop the table
   eliminating both data and metadata.

7
Browse Browse shows the data in the ArtWork table
The arrows show a few ways to get back to the
test database page.
8
Search Search gives one a Query-By-Example
interface to search the ArtWork table
9
Insert Insert provides a place for a user to
enter data into the ArtWork table.
10
Properties Properties displays the
design/structure of the ArtWork table.
11
Allows one to write SQL queries for the test
database.
12
(No Transcript)
13
Export ArtWork as CSV (comma-separated varaiables)
While the name comma separated variables
suggested the fields should be separated
(terminated) by a comma, there will be a problem
if the data itself contains commas. Try to
choose a delimiter that would not appear in the
data.
14
Export ArtWork as CSV (Cont.)
15
CSV (actually semicolon-separated) file in Notepad
16
Export ArtWork As XML
17
It does not open the XML file, but to save it you
can right click, choose View Source which will
show the XML file in Notepad and it can be saved
from there.
18
(No Transcript)
19
The result of the search is a table which you
then chose to browse. The search is
accomplished by a SQL statement, which can be
edited or turned into PHP code.
20
This view provides a Query-By-Example interface.
It is like the interface we encountered when we
clicked on the search button associated with a
table. But this interface is better suited for
doing queries that involve multiple table
(joins).
21
Return to the test database main page, click on
the search button next to the Artist table
Accepting the default settings yields a simple
query that obtains all of the fields of all of
the records.
22
To choose only a few fields, hold down the
control key while clicking on the field names in
the list.
One can project out only the fields one wants
to see.
23
One can select out records that satisfy a
particular condition, choose a comparison
operator and enter a value.
24
The result page shows both the results of and the
SQL for the query.
SQL for query
Result of query
25
1.
2.
3.
We can click the Edit link and change aspects of
the query.
26
When we start using PHP pages to interact with
the database, we will need PHP variables that
correspond to SQL queries. PMA provides this
for us.
27
We can produce a quick report on the results of
the query by clicking on the Print view link.
The Export link leads to an interface like that
for exporting a table.
28
Ascending and Descending
We can put the artists in their birth order by
selecting that field and choosing ascending (in
this case).
29
Greater than operator
Internally dates correspond to numbers (not text)
and operators like greater than or equal to
make sense. The difficulty is in knowing how the
particular interface likes to format dates.
Here we used a year-month-day format.
30
Return to the test database main page and then
click on the Query button.
31
Use the drop-down list to select Artwork. and
Artist. which means all of the fields from both
tables. Check that they should be displayed.
32
Cartesian Product The result lists every
possible pair of artwork and artist regardless of
whether the artwork was by the artist.
33
We create a join by selecting from the
Cartesian product records in which the ArtistID
(primary key) from Artist and ArtistID from
ArtWork (foreign key) match.
34
Result of the join.
35
We can refine this query by choosing only the
fields we want to see (projection).
36
Result of join with projection.
37
We can additional selection conditions.
38
Even though this is a valid query, there is a
limited amount of data and the query produces
zero records. (The interface could be a little
nicer at letting you know.)
39
Pre-existing data file

• Suppose we already have a data file, and we do
  not want to enter the data using the Insert
  feature (which will be shown later) which allows
  us to enter data one record at a time.
• Then we can import data.
• The first step is to examine the data file and
  known its format.

40
A file containing data on members of the House of
Representatives in a csv file
CSV files can be opened in Excel, though they are
just text files (and can be open in Notepad as
well)
41
Create a table (give it a unique name).
42
Enter fields use order seen in data file.
43
Table created
44
Scroll to bottom of page to find Insert link.
45
Click Browse button and find data file.
46
Select delimiter information.
File was comma delimited. Fields were not
enclosed.
47
Scroll down to submit button
48
Result page of import.
49
Click on table and on Browse button. Slightly
problem with first record.
50
Select offending record and click Delete button.
51
Warning message about Delete, also shows SQL for
delete.
52
Result of Delete