mysql4u.com

Good programming–which is to say, programming that yields both efficient code and a profitable life for the programmer–depends on not reinventing the wheel. If someone else has solved the problem you’re facing (and someone almost always has), you’d be foolish to waste your energy figuring out your own solution. MySQL Cookbook presents solutions to scores of problems related to the MySQL database server. Readers stand a good chance of finding a ready-made solution to problems such as querying databases, validating and formatting data, importing and exporting values, and using advanced features like session tracking and transactions. Paul DuBois has done a great job assembling efficient solutions to common database programming problems, and teaches his readers a lot about MySQL and its attendant APIs in the process.

DuBois organizes his cookbook’s recipes into sections on the problem, the solution stated simply, and the solution implemented in code and discussed. The implementation and discussion sections are the most valuable, as they contain the command sequences, code listings, and design explanations that can be transferred to outside projects. The main gripe readers will have about MySQL Cookbook is that the author, in his effort to cover the range of MySQL-friendly programming languages, uses different languages in his solutions to various problems. You’ll see a Perl solution to one programming challenge (Perl, in fact, is the most frequently used language, followed by PHP), a Python fix for the next, and a Java sample after that. Readers have to hope that they find a solution in the language they’re working with, or that they’re able to transliterate the one DuBois has provided. It’s usually not a big problem. –David Wall

Topics covered: How to make MySQL databases do your bidding–in terms of queries, table manipulation, data formatting, transactions, and Web interfaces–through the database server’s command line interfaces and (more importantly) through the MySQL APIs of Perl, PHP, Java, and Python. Particularly excellent coverage deals with formatting dates and times, management of null values, string manipulation, and import/export techniques. –This text refers to an out of print or unavailable edition of this title.

Book Description
Along with MySQL’s popularity has come a flood of questions about solving specific problems, and that’s where this Cookbook is essential. Designed as a handy resource when you need quick solutions or techniques, the book offers dozens of short, focused pieces of code and hundreds of worked-out examples for programmers of all levels who don’t have the time (or expertise) to solve MySQL problems from scratch.

The unexpected pleasure of reading books about databases is that they are often written by authors with highly organized minds. Paul DuBois and his editors at New Riders have assembled MySQL with a clarity and lucidity that inspires confidence in the subject matter: a (nearly) freely redistributable SQL-interpreting database client/server primarily geared for Unix systems but maintained for Windows platforms as well. What isn’t “free” about MySQL (the application) is its server’s commercial use; all clients and noncommercial server use are free. DuBois’s tome isn’t free either, but its list price is modest in light of its value and the value of its namesake.

The volume is superbly organized into 12 chapters and 10 appendices and contains a concise table of contents and a comprehensive 50-page index. It is peppered with references to the online HTML documentation that comes with the source and binary distributions (which are available and easy to install in stable rpm and tar releases.)

The first third of MySQL is an excellent instruction tool for database newbies; the second third is a detailed reference for MySQL developers; and the last third consists of clearly annotated appendices, including C, Perl (but not Python), and PHP interfaces.

In database-driven applications, three different players produce the final output of the web page you view with your client: the web server, the scripting language (PHP), and the database back end (MySQL). When the client browser requests a page from your web site, the following steps occur:

1.

The web server receives the request via HTTP for a particular web page and resolves and retrieves the requested file.
2.

Depending on the nature of the file (i.e., if it ends in .php), it is pre-processed using, in our case, the PHP engine.
3.

The script’s application and presentation logic executes, performing database queries as necessary.
4.

The PHP engine uses the results from the database in its application logic to construct the HTML document, returning it to the web server and, finally, the client.

We will focus on steps three and four in our discussions here. Looking at those steps in more detail, we can summarize the process of accessing and working with a database connection from within a PHP script in the following steps. (The steps in parentheses are optional, depending on circumstance.)

* Establish a connection to the database server.
* (Validate any user input.)
* Select the database on the server to use.
* Execute the desired query against the database.
* (Retrieve and process the results.)
* Create HTML or perform actions based on results.
* (Close the database connection.)

Connecting to a MySQL Database

From a development standpoint, connecting and executing queries from PHP is as simple as calling the appropriate functions. Let’s look at the basic functions used in almost every database-driven application. As I have already explained, the first step is to connect to the database — in our case, this is done via the mysql_connect() function, whose syntax follows:

mysql_connect([$server [, $username [, $password [, $new_link [, $flags]]]]])

If you have worked with MySQL’s mysql client application, most of these parameters should already make sense to you. The first parameter, $server, is the address of the MySQL server to connect to using the username and password provided by the $username and $password parameters. When I say “address,” however, I am not necessarily talking about a TCP/IP address. This parameter can take multiple forms:

// Connect to the server at hostname using the default port
$server = ‘hostname’

// Connect to the server at hostname using the specified port
$server = ‘hostname:port’

// Connect to the server on the local machine using the provided local socket
$server = ‘:/path/to/socket’

Related Reading
Web Database Applications with PHP and MySQL

Web Database Applications with PHP and MySQL
By Hugh E. Williams, David Lane
Table of Contents
Index
Sample Chapter

Read Online–Safari Search this book on Safari:

Code Fragments only

Note: when specifying a server using the hostname, it is worthy to note that the MySQL extension in PHP will attempt to connect using a local socket (or named pipe in Windows) instead of via TCP/IP, if the hostname is of the form localhost, or localhost:port is used. Although this is generally desirable (and recommended), you can also force a TCP/IP connection by using the IP address 127.0.0.1 instead.

The fourth parameter, $new_link, indicates if a new link should be established, even if one already exists for this request. This only applies if you call mysql_connect() multiple times to the same server with the same authentication information. Normally, PHP will reuse an already-opened connection. This parameter will override that behavior, creating a new connection.

The fifth and final parameter is $flags. This parameter is any combination of the following constants bitwise ORd together:

MYSQL_CLIENT_COMPRESS
Establish a connection to the database using a compressed version of the protocol.

MYSQL_CLIENT_IGNORE_SPACE
Ignore white space after function names in queries.

MYSQL_CLIENT_INTERACTIVE
Use the interactive_timeout settings of the MySQL server instead of the default wait_timeout setting when determining if the connection is inactive and should be closed by the server. (See the MySQL documentation for more information on these settings.)

When executed, the mysql_connect() function will attempt to establish a connection to the database and return a resource representing that connection. If the attempt fails for any reason, mysql_connect() will return a Boolean false.
Selecting the Database to Use

Once you have a database connection, the next step is to select the database that you will be performing queries against. (Remember the SQL USE statement?) To do this, you’ll need the mysql_select_db() function which has the following syntax:

mysql_select_db($database [, $link]);

where $database is the name of the database to use, and the optional parameter $link is the database connection resource returned from the mysql_connect() function. This function will attempt to select the specified database and return a Boolean indicating success or failure.

Note: As is the case with almost all of the MySQL extension functions, the $link parameter is optional. In every case, PHP will use the last opened connection. If no connection is open, it will attempt to open one automatically. It is strongly recommended that you provide a $link explicitly to avoid problems as your applications become more advanced.
Performing a Query Against a Database

Now that you know how to connect to the MySQL database, let’s see how to perform a query against the database from within PHP. To do this, use the appropriately named mysql_query() function, whose syntax is as follows:

mysql_query($query [, $link]);

where $query is a single SQL query to execute (without the terminating semi-colon or \g) and the optional parameter $link is the value returned from mysql_connect(). As usual, PHP will use the last opened connection if you do not provide the $link parameter.

Upon successful execution, mysql_query() will return a resource representing the result set or a Boolean false if the query failed. Note that a query is considered a success even if no results are returned; mysql_query() will only fail if the query itself was malformed or otherwise unable to execute on the server. Determining if any results were actually returned from a query requires a different method.
Retrieving a Result Set

Now that you know how to perform a query, it’s time to learn how to access the data from a result set. PHP has many different methods to accomplish this task, but they all have the same general form. For our purposes, I’ll explain things in the context of the mysql_fetch_row() function. Its syntax is as follows:

mysql_fetch_row($result);

where $result is the result resource returned from a successful query executed using the mysql_query() function. This function will return a single row from the result set as an enumerated array, where element zero represents the first column, element one represents the second, and so on. Each subsequent call will return the next row in the result set until no more rows remain. Then, mysql_fetch_row() will return a Boolean false. Generally, this function is used in conjunction with a while loop to traverse the entire array as shown in the below example snippet:

/* Connection code omitted */

$result = mysql_query("SELECT * FROM books");

if(!$result) die("Query Failed.");

while($row = mysql_fetch_row($result)) {

/*
$row[0] now contains the first column of the current row,
index 1 is the second, etc.
*/

}
?>

As I stated earlier, mysql_fetch_row() is not the only function available that allows you to access rows of a result set in this fashion. Each of the following functions has an identical syntax and use as mysql_fetch_row(), but each provides the row in a different format as described:

*

Return the current row as an associative array, where the name of each column is a key in the array.

$row = mysql_fetch_assoc($result)
$row[’column_name’]

*

Return the current row with both associative and numeric indexes where each column can either be accessed by 0, 1, 2, etc., or the column name.

$row = mysql_fetch_array($result)
$row[0]

// or

$row[’column_name’]

*

Return the current row as an object with member variables for each column in the result set.

$row = mysql_fetch_object($result)

$row->column_name

Closing a Database Connection

Although it is not strictly necessary, sometimes it is advantageous to close an open connection to the database when you no longer need it, instead of waiting for the end of the request, when PHP will do so automatically. Use the mysql_close() function with the following syntax:

mysql_close($link)

where $link is the database connection resource returned from the mysql_connect() function.

Note: In order to work with the examples in this (and the next two) columns, you must already have access to a MySQL database via a MySQL client such as the mysql application. If you do not have access to MySQL, it can be downloaded, free of charge, from the MySQL home page in both Unix and Windows flavors. For more information regarding the installation and use of the MySQL server and client, please see the MySQL web site for more information.
Principles of Relational Databases

Relational databases are the cornerstones of today’s serious web applications. They provide the “back end” — efficient and, if used properly, fast methods of storing and retrieving mass quantities of data. Although many different relational databases (formally called “Relational Database Management Systems,” or RDBMS) exist, such as Oracle and PostgreSQL, all of my discussions will focus on the MySQL RDBMS. The fundamental principles, as well as much of the coming discussion of SQL, will be relevant regardless of the database package used.

Relational databases are designed to store incredible amounts of data: addresses, email addresses, images, and whatever is required. However, RDBMS packages do not get their strength from what types of data they can store, per se. Instead, the organization of that data within an RDBMS provide the benefits to its users.

Related Reading
MySQL Pocket Reference

MySQL Pocket Reference
By George Reese
Table of Contents

Read Online–Safari Search this book on Safari:

Code Fragments only

Data is organized into one or more databases. These databases are then organized into tables that actually store the data. The best analogy to RDBMS systems is this: consider your day-to-day filing cabinet that is filled with a number of folders, each of which contains a simple table of rows and columns that store the data for that folder. From this perspective, the filing cabinet itself could be considered a database, while each folder could be considered a separate table within that database. To retrieve a specific piece of data from your filing cabinet, you must choose the correct database and folder within that cabinet. Then, you search within that folder for the data you requested.

From a RDBMS standpoint, this is a fairly accurate portrayal of your digital database. Instead of folders, there are simply named tables, and instead of manually searching each table for a specific piece (or pieces) of data, you use a language called SQL (or “Structured Query Language”) to retrieve the needed data.
The Structured Query Language

Before you begin to worry about trying to learn an entirely new language on top of attempting to learn PHP, let me calm your fears. SQL is perhaps one of the easiest languages in existence. SQL is, however, absolutely indispensable because it provides the means by which you store and retrieve information in the RDBMS. Although this is not to say SQL queries cannot become incredibly complex, be assured that the underlying principles of the language itself are easy to grasp. Since the best way to understand this is to start right in, let’s introduce a few of the major statements.

Note: As I mentioned, SQL can be a very complex language, where statements can take many different forms and, to a beginner, be quite confusing. To elevate that confusion, be aware that the following statements do not reflect the magnitude of the language and have been significantly simplified to focus on practical day to day use of SQL for a beginner. For the complete syntax and use of the following SQL statements, see the MySQL documentation.

Since chances are you have never used relational databases before, your first task is to learn the CREATE statement. This statement is used for creating databases as well as tables. Let’s start by creating a new database called fundamentals:

mysql> CREATE
DATABASE fundamentals;
Query OK, 1 row affected (0.01 sec)

This statement has essentially created a filing cabinet (database) that can then be filled with folders (tables) to store our data. Obviously, an empty filing cabinet without folders cannot do us much good in storing data, so we’ll also need to create tables within this database.

Before we can create any tables within the database, you must understand a bit more about tables. As mentioned before, tables are simply collections of rows and columns. When working with or creating database tables, every column within the table must be assigned a specific data type, governing what kind of data the column represents (such as integer, floating point, or string). Column types can even take on properties, such as a default value or an auto-incrementing number. Since each column within a table must be assigned a data type, let’s look at a few of the available data types:

*

INT[(SIZE)] [UNSIGNED] [ZEROFILL]

A simple integer between -2147483648 and +2147483647 or, if the UNSIGNED attribute is provided, between zero and 4294967295. The ZEROFILL attribute indicates that the number should be prefixed with zeros until the number is SIZE digits in length.
*

VARCHAR[(SIZE)] [BINARY]

A variable-length string that is a maximum of SIZE characters in length (where SIZE cannot exceed 255). Unless the BINARY attribute is provided, this data type is considered case-insensitive and obviously cannot hold binary data.
*

TEXT

A case-sensitive string that is a maximum of 65,535 characters in length.
*

DATETIME

A date and time ranging from 1000-01-01 00:00:00 to 9999-12-31 23:59:59 (dates are in YYYY-MM-DD HH:MM:SS format).
*

DATE

Similar to the DATETIME data type, except without the time in YYYY-MM-DD format.

Although this is not a complete set of all of the data types a column within a table can have, it gives you an idea of the ways and specific types of data that can be stored. With these in mind, let’s create a couple of tables within the fundamentals database that will keep track of books. Specifically we’d like to store the title of a book, its author, the publication date, and the home state of the author. For reasons I will explain later, I will be dividing authors and books into two separate tables, books and authors, with a common author_id column between them.

Table 1. The books table
book_id author_id title pub_date
1 1 PHP Unleashed 11-01-03
2 1 PHP4 Programming 10-01-02
3 2 Cool Stuff 03-23-02
4 3 Another book 02-01-01

Table 2. The authors table
author_id name state
1 John Coggeshall MI
2 Joe Coolguy AZ
3 Jennifer Author KS

In order to create this table within our database, we again turn to the CREATE statement. However, before we can create a table in the database, we first must tell MySQL what database we are working with using the USE statement, as shown:

mysql> USE fundamentals;
Database Changed

Now we can use the CREATE statement to create the books and authors tables:

mysql> CREATE TABLE books(
book_id INT AUTO_INCREMENT PRIMARY KEY,
author_id INT,
title VARCHAR(255),
pub_date DATE);

Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE authors(
author_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255),
state VARCHAR(2));

Query OK, 0 rows affected (0.02 sec)

These two statements create the two tables we will use throughout our discussion. The books table has four columns: a book id number (book_id), an author ID number (author_id), the title of the book (title), and the publication date (pub_date). The authors table only has three columns: the author ID (author_id), the author’s name (name), and the author’s state of residence (state).

Note that the book_id and author_id columns have the AUTO_INCREMENT and PRIMARY KEY attributes set. The PRIMARY KEY attribute indicates that no two rows in the table may have the same value for that column — each must be unique. In the case of our tables, this means that the book_id column and the author_id column will each contain an integer value completely unique for each and every row in their respective tables. The AUTO_INCREMENT attribute automatically provides a value for the column. As data is inserted into the tables, the first row will have a value of zero, the second a value of one, the third a value of two, and so on.

Note: Although it is there to simplify things, the AUTO_INCREMENT attribute only takes effect if the NULL value is passed for the column in question. If you specify a value, it will be used instead. Furthermore, if the value inserted is greater than the largest value for that column, the AUTO_INCREMENT attribute will use that value plus one for its next insert value.

To see how your tables are defined in detail once they are created (very useful if you have forgotten the details of a specific table), you can use the DESC (or DESCRIBE) statement:

mysql> DESC books;

+———–+————–+——————-+——+—–+———+—————-+
| Field | Type | Collation | Null | Key | Default | Extra |
+———–+————–+——————-+——+—–+———+—————-+
| book_id | int(11) | binary | YES | PRI | NULL | auto_increment |
| author_id | int(11) | binary | YES | | NULL | |
| title | varchar(255) | latin1_swedish_ci | YES | | NULL | |
| pub_date | date | latin1_swedish_ci | YES | | NULL | |
+———–+————–+——————-+——+—–+———+—————-+

4 rows in set (0.00 sec)

Now that the tables have been created, we need to populate the tables with the data reflected in Tables 1 and 2. Use the INSERT statement to insert data into a table:

mysql> INSERT INTO books VALUES(1, 1, “PHP Unleashed”, “2003-11-01″);

Query OK, 1 row affected (0.01 sec)

Repeat for each row in the books table.

mysql> INSERT INTO authors VALUES(1, “John Coggeshall”,”MI”);

Query OK, 1 row affected (0.01 sec)

Repeat for each row in the authors table.

Note: Notice that for the first set of INSERT queries, the date column is represented in YYYY-MM-DD format. This is the standard method of storing dates.

To simplify the process of data entry, make sure you manually enter values for the book_id and author_id columns in each respective table as shown above instead of taking advantage of the AUTO_INCREMENT attribute. It is important for later discussions that the tables in the database reflect those found in Tables 1 and 2 exactly.

At this point, you should have a database named fundamentals containing two tables named books and authors. To double check, you can ask the server to show all of the tables within a database with the SHOW statement:

mysql> SHOW TABLES;

+—————————–+
| Tables in fundamentals |
+—————————–+
| books |
| authors |
+—————————–+

2 rows in set (0.03 sec)

Assuming everything has gone as expected, we are now ready to start requesting data from the database.
Retrieving Data From Tables

Now that we have data in our database, let’s retrieve it. To retrieve data from the database (called a “result set”), use the SELECT statement:

mysql> select * from books;

+———+———–+——————+————+
| book_id | author_id | title | pub_date |
+———+———–+——————+————+
| 1 | 1 | PHP Unleashed | 2003-11-01 |
| 2 | 1 | PHP4 Programming | 2002-10-01 |
| 3 | 2 | Cool Stuff | 2002-03-23 |
| 4 | 3 | Another Book | 2001-02-01 |
+———+———–+——————+————+

4 rows in set (0.00 sec)

In this case, the above query has returned a result set containing all of the data stored in the books table. How does it work exactly? Although the SELECT statement is much more complex then this, the general syntax is as follows:

SELECT FROM
… (additional optional clauses)

can be any valid column name for the tables specified in
or any valid MySQL function (more on those in the future). If you wanted only a list of all of the book titles in the database, you could use the following query:

mysql> SELECT title FROM books;

+——————+
| title |
+——————+
| PHP Unleashed |
| PHP4 Programming |
| Cool Stuff |
| Another Book |
+——————+

4 rows in set (0.00 sec)

You could also select two columns from the books table by separating them by a comma:

mysql> SELECT title, pub_date FROM books;

+——————+————+
| title | pub_date |
+——————+————+
| PHP Unleashed | 2003-11-01 |
| PHP4 Programming | 2002-10-01 |
| Cool Stuff | 2002-03-23 |
| Another Book | 2001-02-01 |
+——————+————+

4 rows in set (0.00 sec)