Working with a Database in SQLyog

Working with a Database in SQLyog

It's critical to understand the SQL language, but sometimes you may want a more visual way to build and view your databases. If you are running Windows, you can use an excellent front end called SQLyog. This freeware program makes it much easier for you to create, modify, and manipulate databases.

Start example

SQLyog is so cool that you'll be tempted to use it all the time. That's fine, but be sure you understand the underlying SQL code, because your PHP programs will have to work with plain text SQL commands. It's fine to use SQLyog while you are building and manipulating your data, but your users won't be using this program. Your application will be the user's interface to your database, so you need to be able to do all commands in plain text from within PHP. I use SQLyog, but I also make sure I always look at the code it produces, so I can write it myself.

End example

SQLyog basically adds the visual editing tools of a program like Microsoft Access to the MySQL environment. It also adds some wonderful tools for adding records, viewing your data structure, and exporting data to a number of useful formats.

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.

Click To expand
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.

Click To expand
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.


Special thanks to Lee Seitz and his hysterical Super-Hero generator at

Check this site out sometime when you're bored.

Editing Table Data

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

Click To expand
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.

Click To expand
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.

Click To expand
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.

Click To expand
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.

Click To expand
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.

Click To expand
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.

Click To expand
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.


The ability to automatically generate SQL scripts is incredibly powerful. It can be a great time-saver and you can learn a lot by examining the scripts written with such a feature. However, you are still the programmer, and you are responsible for code in your projects, even if you didn't write it directly. You must still understand what the code being generated does. Most of the code you'll see so far is stuff I've already described, but you may have to look up advanced features. As I've said before, you still have to know how to do this stuff by hand.