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 don’t 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 • 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 table’s 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.

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.

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.

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.