Introduction to MySQL program design

In this part of the book, we'll discuss what you need to know to write your own program to access the MySQL database. MySQL has a set of utilities. For example, mysqldump exports the context and structure definitions of a table, Mysqlimport loads the data file into a table, Mysqladmin implements the management W operation, and MySQL allows users to interact with the server to execute arbitrary queries. Each standard MySQL utility tends to be compact, with the emphasis on programs that perform specific, limited functions. Even MySQL, in the sense that MySQL is more flexible than other utilities, can be used to execute any number of queries, that is, designed to allow direct SQL queries to the server and to view the single purpose of the query results.
The limited nature of the MySQL client is not a disadvantage, but is deliberately designed. Programs are utilities that have common purpose; they don't try to anticipate all the possible needs that you want to do. MySQL developers are not in favor of writing large, bloated programs to try to do everything they might want to do (and the results will include a large number of
You don't really care about the code of Things). However, sometimes some applications do have requirements that the conventional client's capabilities cannot handle. To handle these situations, MySQL provides a client programming library. This allows you to write your own programs that meet any specific requirements that your application might have. By allowing you access to the MySQL server, the client's open
The extent is limited by your own imagination.
How can you write your own program to gain special abilities? Let's compare the MySQL client's access to the MySQL server with its interface with no additional code:
You can customize input processing. Use MySQL to enter the original SQL statement. Using your own program, you can provide users with a more intuitive, easier input methods. Using a program makes it unnecessary for a user to know that sql-does not even have to know the role that the database assumes in the completed task.
The input may be collected in a basic way, such as command-line-style hints and value reads, or it might be a complex way to use screen-management packages (such as curses or s-l a n g), implemented in TCL/TK x windows, or Web browser formats, based on screen input.
For most people, it is easier to specify the form of a search parameter by filling in a certain format than by releasing a SELECT statement. For example, a real estate broker, looking for a certain price range, style or location of the house, as long as the search parameters into the table, you can minimize the cost of the eligible content. Entering new records or updating existing records also considers this application similarly. Keyboard operators in the data entry Department should not be aware of SQL syntax such as INSERT, REPLACE, or update.
Another reason for the input acquisition layer between the end user and the MySQL server is that it can validate user-supplied input. For example, you can check the data to make sure that they are in a format that meets MySQL requirements, or that you can require a specific area to be filled in.
Can customize the output. MySQL's output is basically unformatted; you can choose to have tabs as delimiters or as a tabular output. If you want to make the output look better, you must format it yourself. These requirements may be as simple or more complex as printing "Missing" rather than null. Consider the following report:

This report includes a few specific elements:
Custom headers.
The suppression of duplicate values in the State column so that the values are printed only when changes are made.
Calculations of subtotals and totals.
Number formats, such as 9 4 3 8 4. 2 4, Printed for $9 4, 3 8 4. 2 4. For some tasks, you may not even need any output. You may be simply retrieving information about the results of the calculation being inserted backwards into another database table. In addition to the user running the query, you may even want to export the result to another location. For example, if you are extracting names and e-mail addresses to automatically fill in the process of generating a letter format for bulk e-mail, the program produces output. However, the output consists of the message recipient's information and does not have information to run the program's personnel.
Can work in the context of constraints imposed by the SQL itself. SQL is not a process language with a process control structure with conditional selection, loops, and subroutines. SQL scripts include a set of executable statements from start to finish, with a minimum of error checking.
If you use MySQL in batch mode to execute a file of SQL queries, MySQL exits after the first error, or if you specify the--force option, all queries are executed without any choice, regardless of the number of errors. The program can provide process control around the statement so that it can be selectively adapted to the success or failure of the query. You can execute a query based on the success or failure of another query, or decide what to do next, based on the results of the previous query. SQL has very limited continuity between statements, which is also taken to MySQL. It is difficult to use the results of one query and apply them to another query, or to link multiple query results together. The L-a S T _ INSERT _id () can be used to get the most recent auto_increment value generated by the previous statement, just about it.
More generally, it is difficult to retrieve a set of records and then use each record as a basis for a complex series of further operations. For example, retrieving a consumer list and then querying each consumer's detailed credit history may include several queries for each customer. In some cases, you may want to open an invoice, write the customer information you want to contact on the invoice header, and then list each entry in order. MySQL is not suitable for these types of tasks because it may require several queries that depend on the results of the previous query, and these tasks are beyond the ability of the MySQL layout design. In general, in addition to MySQL, tools are needed to perform tasks that include master-detail relationships and requirements for complex output formats. The program provides "glue" to connect the query together and can use the output of one query as input to another query.
MySQL can be integrated into any application. Many programs use the capabilities of the database to provide information. By publishing a quick query, the application can verify the consumer number or check whether an entry is in the Product list. Suppose a customer is looking for all the books of some authors, the Web application can look them up in the database,
The results are then displayed on the customer's browser.
You can implement a preliminary "integration" by using a MySQL shell script that calls with input files containing SQL statements, and then process these outputs using other UNIX utilities. But this can become difficult to look at, especially when tasks become more complex. As applications grow into messy patches, it can also create a sense of "working, but feeling wrong". Additionally, the creation of shell scripts that run other commands may cost more than you might expect. But it may be more efficient to interact directly with the MySQL server, and you can extract exactly the information you want when you need it at every stage of your application's execution. For the sample database we installed in Chapter 1th, "MySQL and SQL introduction," We have enumerated a number of samp_db programs that need to be programmed to interact with the MySQL server. Some of these goals are shown in the following list:
Format the historical League directory for printing.
Consider looking for skins and online catalogs.
Send a supplemental notification to members by e-mail.
Using a Web browser, you can easily enter fractions into a credit book.
One aspect that we will consider in some detail is the combination of MySQL's capabilities with the Web environment. MySQL does not directly provide support for Web applications, but by combining MySQL with the right tools, we can easily access the database through we b. You can use a Web server to specify the query and report the results to the customer's browser. Combining MySQL with the web can have two ideas:
The main interest is in the database, just want to use the web as a tool to get easier access to the data. In such an idea, the location of the database is clear and obvious, because it is the focus of interest. For example, you can write a Web page that allows you to view the tables, table structures, and contents of the table that the database contains. You intend to use a Web server to improve access to MySQL. This may also be the view of the MySQL manager.
The main interest may be the Web site, and you might want to use MySQL as a tool to make the site's content more valuable to visitors. For example, if you run an information board or discussion list for visitors to your site, you can use MySQL to keep track of your information. In this case, the role of the database is more subtle, and the visitor may not even care what you have to offer him to perform on the server. You intend to use MySQL to improve your Web server's capabilities. This may also be a point of view for Web site developers.
These ideas are not contradictory. For example, in the case of historical League, we want to use we as a member to get access to the contents of the Member Directory by allowing online input. Providing access to the database is a use of the web. At the same time, the League Web site is somewhat incomplete in some ways, so add directory content to the site to increase the value of the site for members. Enhancing the services provided by the site is a use of the database.
No matter how you look at the combination of MySQL and the web, the implementation is similar, the front web site and the background of the MySQL connection, using the Web server as a medium. The WEB server sends queries from the user to the MySQL server, retrieves the results of the query, and then delivers them to the client, which is displayed on the browser.
Of course, it is not necessarily necessary to process data online, but this is often beneficial, especially when comparing access to data by a standard MySQL client program:
With Web Access to data, people can use their favorite browsers to run on their favorite platforms. They do not limit the system that the MySQL client program runs. This is even more true for Web browsers, regardless of how widely the MySQL client is distributed.
The use of the Web interface is simpler than the use of the MySQL client program on the standalone command line.
The Web interface can be tailored to the requirements of a particular application. And the MySQL client program is a fixed interface to complete the basic functions of the tool.
The Dynamic Web page expands MySQL's ability to do things that are difficult or impossible to do with MySQL clients. For example, using a MySQL client program alone is not a real way to combine applications that integrate the purchase of vehicles into a whole.
Any programming language can be used to write web-based applications, but some languages are more appropriate than other languages. Please refer to 5. Section 2, "Select a P I", we will see this.