Title: MySQL and phpMyAdmin
1MySQL and phpMyAdmin
2Navigate to http//webapptst.lasalle.edu/pma and
log on (username pmadmin)
3The pma (phpMyAdmin) interface
4Use 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.
5The 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.
6Table Actions
- The browse button will show that all of the data
in the table. (One can also update and delete the
data here.) - The search button will set up a query based on
that table. - The insert button allows one to add new records
to the table. - 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. - 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.) - The drop button allows one drop the table
eliminating both data and metadata.
7Browse Browse shows the data in the ArtWork table
The arrows show a few ways to get back to the
test database page.
8Search Search gives one a Query-By-Example
interface to search the ArtWork table
9Insert Insert provides a place for a user to
enter data into the ArtWork table.
10Properties Properties displays the
design/structure of the ArtWork table.
11Allows one to write SQL queries for the test
database.
12(No Transcript)
13Export 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.
14Export ArtWork as CSV (Cont.)
15CSV (actually semicolon-separated) file in Notepad
16Export ArtWork As XML
17It 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)
19The 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.
20This 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).
21Return 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.
22To 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.
23One can select out records that satisfy a
particular condition, choose a comparison
operator and enter a value.
24The result page shows both the results of and the
SQL for the query.
SQL for query
Result of query
251.
2.
3.
We can click the Edit link and change aspects of
the query.
26When 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.
27We 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.
28Ascending and Descending
We can put the artists in their birth order by
selecting that field and choosing ascending (in
this case).
29Greater 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.
30Return to the test database main page and then
click on the Query button.
31Use the drop-down list to select Artwork. and
Artist. which means all of the fields from both
tables. Check that they should be displayed.
32Cartesian Product The result lists every
possible pair of artwork and artist regardless of
whether the artwork was by the artist.
33We 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.
34Result of the join.
35We can refine this query by choosing only the
fields we want to see (projection).
36Result of join with projection.
37We can additional selection conditions.
38Even 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.)
39Pre-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.
40A 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)
41Create a table (give it a unique name).
42Enter fields use order seen in data file.
43Table created
44Scroll to bottom of page to find Insert link.
45Click Browse button and find data file.
46Select delimiter information.
File was comma delimited. Fields were not
enclosed.
47Scroll down to submit button
48Result page of import.
49Click on table and on Browse button. Slightly
problem with first record.
50Select offending record and click Delete button.
51Warning message about Delete, also shows SQL for
delete.
52Result of Delete