 So welcome to PHP My Admin tutorial by Dieter, Isaac, Marc, and Urs-Lan Hi, and welcome to the PHP My Admin tutorial talk. Thanks for introducing us So basically we'll We'll give a talk tutorial about how PHP My Admin works And we'll also explain what PHP My Admin is So my first question to the audience, who knows PHP My Admin? Right, that's quite a few people, almost everybody who has ever used it Right, that's all the majority, but I'm going to the introduction anyway, so So what is PHP My Admin? Basically, it's a web interface to manage MySQL database MySQL database, you can think of this as in a broad way So it's actually the entire ecosystem So also not only the MySQL, original MySQL database, but also the MariaDB and the Drizzle Drizzle database, which are derived from the original MySQL It is a web interface So it means that it's the software is running on a web server and communicates with a MySQL database either on the same server or on a separate server, it doesn't matter and You interact with the interface using your web browser on your own PC It's certainly PHP And more and more JavaScript is being added over the years because we're relying more and more on JavaScript to make the interface more dynamically with AJAX and forums and dynamically updating of data You can use it to Browse the information that's on the server to access tables and See what's in it and update data and change data But you can also use it to manage the server for instance to manipulate or to change the user Access levels for the users So basically that's about it We exist for 15 years now in September Our last major release was in March of this year. So currently we're at version 4 And version 4.1 is in development. It will probably be released somewhere in the spring of next year The package we get is ships with about 20 languages and several of them are being translated, but we only add the languages that are Translated enough so that they can be added. So how to get it? Of course on our website, you can download the package. It's also available for a lot of distributions It's available in Debian for instance, but also in InfeDora and in other distributions It's also part of a lot of packages like some Wamping C-Panel and of course you can get the latest version on our kit repository So that's for the introduction I will now show a few basic features of of PHP MyAdmin So if you go to the login screen, you see something like this. You just log in with your username This is actually the username That's defined in the MySQL server. So PHP MyAdmin does not Have a separate list of user names and user privileges It uses the credentials you have and the writes you have That are configured in the MySQL database So you just log in and then get something like this. On the left side, you can see your navigation panel listing all the All the databases and you can click on them to show the tables and on the right side You see the information you are actually looking for. So I will first show you How to create a database. So you go to databases. That makes sense, I think And then in the create database field, you just type a name. So we'll add this tutorial and then create And as you can see in the list here The test tutorial database was added and also on the left side in the navigation panel You don't see it yet because there's a feature that collapses Database that I have the same prefix You can configure how this works, but I'm not going into detail there, but you can see that the database is there So now we'll go into that into that database And to create a table, I will minimize the navigation panel to have a bit more room We'll create a table test And add two columns Go should be busy There it is. So now you can define the type of table, the type of columns. So I will create an index And a value I'll make a var here of 8 Here you can with storage engine you can choose which storage engine to use. For this case I will use EnoDB, but you can use anyone you like And then For index I will define the primary and add the automatic indexing And then save Now the table is created. So if you go back You'll see here that there is this table and if you go to struct If you go to structure, you can see that we have an ID and a value And if you go to indexes You'll see that a primary index has been created for the ID for the ID column So currently there's nothing. If you click browse, there's nothing there. So you can with insert you can add data Because the index auto is automatically Created. I'm not going to fill anything in just I'll take foo and bar You see that the ignore Thing was I automatically disappeared once you start typing in the second fields. So I'm going to insert two records at once So you can see now that they have been inserted and if you go to the browse You can see that there are two records now in the table Another feature I would like like to show is how to browse data. I'm going to take a different database Because there are more tables there So I'll go to the employees database tables PC is a bit slow. So so here you see a lot of quite a lot of tables. I Imagine you have a database. You have a table with 15 or 20 fields, but you're only interested in three But they're all that they're at the back of the of the of the list then they're not in the screen and then sweet It's difficult. So there's a possibility of dragging by changing the order so you can Move over it and drag it to another place and then The column moves. There's also a possibility of Hiding tables present this arrow and you get a list of all the tables and just Unmark the ones that you don't want and then they disappear Or you can just click on show all and then they appear again another nice feature That was added in version 4 Is that you can edit that record like you would edit spreadsheet so it does just double-click on a field and then you can Change the data change the records with enter and then that field will be will be updated You can also use the edit button But then you will get a big big form with all the with all the entries and then this is much more easier much faster than Using the edit form if we just want to change one one record. So that was about had adding and changing data Now I'll go to show you bits About user management. You have to go back to the server level So there's a tab called users used to be called privileges in the previous version So there you see a list of all the users that are defined for this database server So without users you can create a new user. You'll get a form. So I type a name this tutorial For any host will take local hosts And then generates you can you can see that gender a password is generated You should supposed to copy this and store it somewhere to be able to log in later, but I will not do these because It's not necessary for it for the tutorial you can define the rights that the user has on on the database that The entire database server so all the tables but you can also and I'm going to show you that's after adding the user So now the user is added so you can see in at least this tutorial this user has been created and if you go to edit privileges And scroll down a bit then you can see database specific privileges and Here you can select a database for instance a test tutorial database and then you can grant all the All the privileges on that only on that database and then that user will only have access to that database, but not to the the rest of the server So that's about it for me. Now Isaac will come up and we'll show you some More advanced features. Thank you. Hi, I'm Isaac and I'm going to show you a couple quick ways that we can deal with foreign key relations within my sequel sir. Yes within my sequel there are There's a way to create foreign key relations if you're using the N. ODB table type And that's one way that we work towards getting a normalized database as You can see here in our example employees database. We've got a list of departments and also employees both of which are simply the name and the corresponding ID number and then we've got a series of other tables which Actually relate the data it takes the employee ID and the department ID and then in this particular case it has excuse me the from and to date for when the employee was associated with that department and This is one of the ways that we Normalize the data so that if you have an employee who changes departments You don't have to enter them twice into the employee database. You just create another entry here So one of the things that we have that enables you to work with relations is the relation view and if you go under the structure tab you can see the relation view link right here and The neat thing that PHP my admin allows you to do is to create a relation even if you're not using the N. ODB table through the Configuration storage feature which is something that we set up to store Additional features for bookmarking or user-level preferences or in this case relations You can go through and you can define relations even if the MySQL table doesn't support it by default, but anyway here in relations. You can see that we've already got two defined and setting one is as easy as selecting it from the drop-down and Just selecting go and in this case. We're going to turn on. Oh, no, we're not gonna do that there. I apologize we're Going to go to departments And I'm going to turn on the show column one more button click away The column to display is another feature that Mark's going to get into a little bit And I will show you also as well that allows us to Make inserting and displaying data much easier because when you go to when you go to the related data Back in the department and employee database Or I'm sorry the table that relates those together if we go to insert a row You'll see that there's a drop-down provided where you can pick from if you know the text Representation or the value you can simply select customer service Or if you know that it's ID number d005 you can select that from the list So we give it to you both ways as the foreign key and the related key and that makes it much easier for you to insert data on the fly Now there's another way of working with relations that we've got and that's called the designer feature Which is a graphical way and in this case It's stuck under the more tab if you've got your resolution set higher if you're not doing a presentation For instance this more tab actually expands out and that's dynamically based on the size of your Your display so in this case, it's under the more tab, but it's down on the end for a designer And when we open that up that's going to give us a graphical representation of all of our relations in this database And what you can do is move those tables around to a way that suits the way your data is displayed For instance, I'm not interested in this log, so I'm going to minimize that and drag that out of the way And now I've got a suitable representation of all of the relations between my database and within this designer If I want to save this later all I have to do is hit save and it saves the layout on that page And so the next time I go into designer it'll save that specific layout. So anyway when you want to create or remove a relation It's quite easy. All you've got to do is in this case will remove this Simply by clicking on the relation and it pops up the little prompt if I wanted to delete a relation and then to add a relation This is easiest selecting the add button and then clicking between the two keys you want to relate and in this case It's asking me what I want to do if the foreign key is manipulated and it didn't like that for some reason So that's how you create the relation within the designer Anyway, so you get the picture of that Then the final thing that I'm going to show you about is the import and export coordinates what we can do is take the coordinates directly from this designer view and move them to a page for export as a PDF or SVG or a DIA graphic file and we're going to Create a new tutorial page here and then from the That was selected There we go, and then when we go back to the operations tab for that database We're going to be able to export the entire database schema and the relation view as a PDF file So here we're going to select the page that we just created the tutorial and Once we select that it's going to automatically fill in With all the coordinates in this text field now I don't like typing the text field and I have trouble visualizing those so all I'm going to do is toggle the scratch board and it's going to bring up a Graphical representation again very similar to the designer and you can drag those around to match Your page layout and how you wish to export the page and as you drag those around It's going to automatically update the an X and Y coordinates in these text boxes So let's say we're happy with that and we're going to come down here and select the export type As I said you can export as several different vector graphics formats But I find PDF the easiest for my purposes and then if you're in a country that requires you to use letter or legal paper You could select that there. We're just going to go PDF a4 and This is going to export for us and I'll show you in just a moment that it gives you a very nice view of all of your tables and all of the Excuse me one second It's going to give you an overview of each Type of data that's represented in each table and then it's also going to show you all the relations So I'm going to switch back to This and zoom in a little bit so you can see There is the table of contents which shows you each table in the Export here and if you click on one it scrolls you directly there So when I click What I'm sorry when I zoom this in for you you can see that the column Shows each column has the type If there's a default any extras the comments every every bit of information you need to recreate this or to document your procedures This makes it very easy to create a database and then see For documentation purposes what exists in the database and then down at the very bottom we've got the list of Relations between each field and so this creates a very nice display that you can Document for future generations and see what your database looked like and now for a little bit more I'm going to turn it over to mark who's going to Do some more on the advanced features of php my admin Well, thank you My name is mark and I will I'm happy to show you four features today the photo handling how to Insert or how to display a photo from the database Also, how to do a zoom search to to have a big picture of your table How to display relational data that Isaac just show showed us how to To relate them, but how we can benefit from this and how to display geographic data From GIS support in my SQL Okay, so let's start with the photo handling. We have our employees table right here and We have inside it a column called photo and the data type is Long blob which means that a my SQL database can hold also non-textual Things like in the like binary things like image or audio in the Full structure of this column We see that there is There are two extra pieces of information one is the mime type So if we want to be able to display this data, we will select image JPEG because we plan to put JPEG images into this column and We also want to transform the data when we are browsing the table. So we want to apply an inline Display so we want to see the JPEGs when we browse the table But now we don't have any pictures into this table When we browse the table, we only see that the photo column is Empty it could contain blobs, but there are nothing yet. So we edit this employee and What I'm going to do is to pick a picture that is on my workstation I will upload it into the row. So I will send it to the to the web server which Will send it to the my SQL server. So I browse my file system locally. I Will choose This picture here So now it is set to be uploaded when I click go it goes to the web server And by the way, the web server could be could not be not local so it generated an update statement and Then when I Make a little room here when I browse the page I See a thumbnail that is generated. It's not stored. It's generated on the fly and I can access the full picture too Okay, now we'll go back to our list of tables and In the employees database, we have a salaries table Which shows for each? employee the The time when he changed When the salary changed, okay But looking like that, I don't see much information I got the raw data, but I don't see the big picture of the trend of the salaries So to be able to see that I will go to search And I'll choose zoom search So this will enable me to generate a plot of two columns. So I'll pick the from date column and the salary column and On the plot I want to label each data point. So let's pick Employee number as the label for each point Now if I have a huge table Generating this plot could could use a lot of memory in my browser. So I should really Restrain myself to a Little number of rows, but it depends on your memory on your system New workstation. So now I see that I Have about a hundred data points. I see the trend of my salaries They are going up. It's very interesting to see but It's called zoom search because you can pick a region of the graph For this year for example, and I have more detailed information about the dots. I Have the employee number that appears like that and I click I can click on adopt to edit The this row of data and I could increase my salary and change it But I won't do that Next I would like to show you the how you we can benefit from the relational Association we did between the employees and the department's tables So in the department employees table now we see only the employee numbers and department numbers only the keys and What if I don't remember what is department five? One thing I can do if I just want to to look at one department is to click and follow the link which brings me To the department table for this department and I can see here the name, but it could be telephone number or something But it's not very convenient if I need to do many many times the same operation so Let's use another way To to be able to see all this related data at a glance I will use a display option here, which is hidden by default So by by default we see the relational keys If I click on the relational display column This is a column that describes a department or that describes an employee so in this case a department name and The employee name last name okay My last feature is to talk about GIS so GIS means Geographic information system so it's a system to to manage Data that relates to geography like coordinates on a map and My SQL supports this kind of data as well as normal text or Numeric data So let's open this other database. I have a table called Capitals that contains the coordinates for each capital on of the world or many capitals at least and Here we only see that it's Contains spatial data. It's only It's 25 bytes, but I don't see exactly what it contains So again, we go to the display options and we will use a format called well-known text which is Which is the markup language for Geographic data So with that we see that it contains point point data with coordinates and Let's try to see what all these coordinates mean so we can go down there and click visualize GIS data So we see more or less that can't the form of the continents With the cities coordinates of the capitals display there to be able to better see the Where they are located we will call open street maps and now we get a layer of the map We have this kind of layer or a satellite base layer like that and we can we can zoom in if we want Down to the street level of each city Thank you and invite Ruslan, okay, thank you mark and I will briefly talk to you about Routines and triggers and events in PHP Madman as well as the server status monitor Okay, no, so who knows what a stored procedure is raise your hand Okay, everybody knows so maybe I don't even need to explain but well anyway It's a feature of a relational database that allows you to store a bunch of SQL code on the server and then just call that code to Executed okay, so you know employees database We can navigate to the routines tab Which encompasses both stored procedure and stored functions. There's few subtle difference, but I won't get into that Now to create a new routine you can click on this link over here And it brings up a dialogue Where we can fill in all the necessary data But it's a risk of this sounding like a cooking show. Here's one that I prepared earlier Here we go. It's a very simple one. It's called tutorial and we have two parameters We have an input parameter and an odd parameter and basically what this does is take the input parameter incremented by one and then Put it into the output parameter so that's a very simple one but Store procedures can be very complex and they could even be hundreds of lanes of code Okay, next we have triggers Triggers Again a feature of my SQL that allows us to execute a bunch of SQL code whenever certain event happens in the database a Very common use case for this is logging so for example, I will show you that We have a trigger again in the employees database in The department stable now again, this can be found here in the triggers tab and If we have a quick look at it What we can see is that after every insert in the department stable it will execute the code that you can see in the definition text area and What it will do is it will insert a new row of data into the log table that will Tell us when a particular department has been added to the system so if I go ahead and insert a new row into the department's and We put in as a number one two three four and We call it computing so a new row has been inserted into the department stable and We can go and quickly check if we have a new row in the log table and As you can see here it is and in the time stamp we can see that it's been inserted well roughly about now and That's all about triggers and moving on we have events events Well, that's something that allows us to execute again a bunch of a scale code either at some point in time or we can set up recurrent events that a triggered at Selected intervals of time No, you can access them directly here, or we can also go to the database level and We will have a tab for events There it is right there, and we can see that there is an event already there However, it will not be executed because in order for the events to work the event scheduler must be enabled Now if you don't own the actual massql server that might be a problem because enabling it requires the super user privilege So here we have it. It's been enabled and If we have a quick look at this Event here, we can see that it's an event that will clear the log table at the end of this month for some reason And we can also have recurrent events like I mentioned earlier and so if we go here into the add event and we change the event type from one time to recurring we can see that the dialogue changes slightly and We can configure when we want the event to be running from to and We can also specify how often we would like to execute it Now that would be all about events and again moving on I will just briefly show you the our service status monitor Now it can be reached from the status tab on the home page And it will show on the on the first page Some statistics about the traffic and the connections and the processes of the server Now there are also sub tabs here that show other information For example, the query statistics allows you to see what type of query is actually being run on your server So here we can see that the most often executed query is of type select But if we scroll down here, we can also see a pie chart which visualizes that data nicely Another thing that we can see here is the status variables and We can use the the information that is shown on this page to perhaps diagnose any performance issues that we might be having on the server Also, we have a monitor tab now this is a fun feature Because we get to see some charts that show us in real time. What is happening to our server? Now in the settings we can change the refresh rate and the number of columns here I have to change it because three don't actually fit on this tiny screen. So we're just going to go for two Now we can see what is happening at the moment here We can see the amount of queries that are being run the amount of connections and the processes that are running the Traffic system CPU usage swap and system memory, but you can also add your own charts and to make this more useful we can go into the set up page and Change a few settings on the server now this will slightly so down the server So as soon as you're finished analyzing what is happening on the server, you might want to turn this back off Okay, excellent. No, what I'll do is I will just open up Another table somewhere and that should trigger a select statement as we can see There's been a spike Right here both in the traffic tab and in the questions tab And what we can do at this point we can get actually more information about what has been happening here If we just click and drag and we can select a particular time span Okay, that didn't work out so well straight this again Here we go now we can pull from the log what kind of queries have been running at that particular time We can see there's been 87 select queries And if we jump to the log table it'll show us the information about the queries Now if you actually click on the individual queries you can run an analysis that will tell you if it's possible to improve the performance and last but not least we have the advisor tab Which runs a set of predefined rules against the server to diagnose If there might be any kind of problems on the server Okay, and this actually concludes our talk on the PHP my admin and Thank you very much for listening and I believe we don't actually have any time for questions But if you're interested we're gonna be outside and we'll we can take them there Oh, we have seven minutes. Oh, that's brilliant. We're okay. Okay. Any questions? Okay, that didn't really change anything. Okay. Thank you very much for coming and listening to us. Thank you