Working with a Database in SQLyog

Connecting to a Server

MySQL is a client-server application. The MySQL server will usually be running on a Web server where your PHP programs reside. You can connect a MySQL client to any MySQL server. Figure 7.10 shows me connecting to my local MySQL server.

Figure 7.10: This screen helps you connect to a data server.

It's important to recognize that you can connect to any data server you have permission to use. This data server doesn't need to be on the same physical machine you are using. This would be useful if you wanted to use SQLyog to view data on a remote Web server you are maintaining, for example. However, many of these remote Web servers do not like this kind of access, so you should still know how to work with the plain MySQL console.

Creating and Modifying a Table

SQLyog provides visual tools to help you create and modify your tables. The phone list is way too mundane for my tastes, so I'll build a new table to illustrate the features of SQLyog. This new table contains a number of randomly generated super heroes. Figure 7.11 shows the dialog used to create a table or alter its structure.

Figure 7.11: It's easy to create a table and modify its structure with SQLyog.

With SQLyog you can choose variable types from a drop-down list, and many field properties are available as checkboxes. Most of those options are not important for now. Note that id is set up as the primary key. When you are finished creating or modifying the table, the proper SQL code to perform the transaction will be automatically generated and executed for you.

Editing Table Data

You can use SQLyog to edit your table in a format much like a spreadsheet. Figure 7.12 illustrates this capability.

Figure 7.12: You can edit a number of records easily in the edit view.

To edit a table in SQLyog, select the table in the table list on the left-hand side of the SQL screen. You can then either press F11 to edit the table, or choose Insert/Update Data from the Table menu. Once you're done editing the data, you can hit the Done button, and SQLyog automatically creates and runs the SQL code needed to modify the table data. If you type data in the last row, you will get a new record.

Exporting a Table

Some of SQLyog's most interesting features involve ways to export information about your tables. You can generate formats that show the data in a number of formats. Once you've gotten a view of a table (by selecting the table and pressing the Enter key) you can go to the tools menu and select "Export Result Set." You will see a dialog like the one featured in Figure 7.13.

Figure 7.13: The export result set dialog allows you to save table data in a number of formats.

You can easily generate an HTML summary of your data by selecting the HTML option and specifying a filename. Figure 7.14 shows the HTML output of the hero data table.

Figure 7.14: You can easily print HTML summaries of your data results.

You might prefer to have your results saved in some sort of delimited format such as those discussed in Chapter 6, "Working with Files." You can easily generate such a format by choosing the CSV (Comma-Separated Value) option, and choosing what your delimiters will be. This is a good choice if you want your data to be readable by a spreadsheet or if you are writing a program that can handle such a format but cannot do direct database access. Figure 7.15 illustrates the CSV version of the hero data set.

Figure 7.15: I set up the phone list data as a tab delimited file and read it into Excel.

You can also set up an XML file to hold the data. As you can see from the illustration in Figure 7.16, XML is much like HTML, and it describes the information in a self-documenting form.

Figure 7.16: The XML form of the data generates HTML-like tags to describe the fields in the table.

When you start to write more complex databases, you'll quickly learn the value of descriptions of each table. You can use the "Create Schema" command under the "DB" menu to generate an HTML description of your table. This schema can be an important part of your programming and documentation strategy. Figure 7.17 shows the schema of the hero table.

Figure 7.17: The schema for a table describes important information about the table's structure.

One last very useful tool is the "export as batch script" function found on the "DB" menu. You can use this tool to automatically generate an SQL script for creating and populating a table. This is very useful if you choose to use the visual tools for creating and editing a table, but then want to be able to recreate the table through a script. The dialog box shown in Figure 7.18 illustrates the various options for this tool.

Figure 7.18: From this dialog box you can generate code that will manufacture replicas of any database created or viewed with SQLyog.

You can specify whether the resulting script generates the table structure alone or adds the data. You can also specify whether the resulting script contains code to select a database, drop the specified table if it already exists, and the filename of the resulting script.