MySQL Cookbook

Issuing Queries and Retrieving Results

Problem

You want your program to send a query to the MySQL server and retrieve the result.

Solution

Some statements only return a status code, others return a result set (a set of rows). Most APIs provide different functions for each type of statement; if so, use the function that’s appropriate for your query.

Discussion

This section is the longest of the chapter because there are two categories of queries you can execute. Some statements retrieve information from the database; others make changes to that information. These two types of queries are handled differently. In addition, some APIs provide several different functions for issuing queries, which complicates matters further. Before we get to the examples demonstrating how to issue queries from within each API, I’ll show the table used for examples, then discuss the general statement categories and outline a strategy for processing them.

In Chapter 1, we created a table named limbs to use for some sample queries. In this chapter, we’ll use a different table named profile. It’s based on the idea of a “buddy list,” that is, the set of people we like to keep in touch with while we’re online. To maintain a profile about each person, we can use the following table:

CREATE TABLE profile ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, name CHAR(20) NOT NULL, birth DATE, color ENUM('blue','red','green','brown','black','white'), foods SET('lutefisk','burrito','curry','eggroll','fadge','pizza'), cats INT, ...