Chapter 4. Simple Queries

Simple Query

There are several steps to execute a query. If you wish to execute a query which returns data such as a SELECT query, then you must first execute the query, store the result, then retrieve the rows. Here is a short example.

#include <stdio.h>
#include <stdlib.h>
#include "mysql.h"

MYSQL mysql;
MYSQL_RES *res;
MYSQL_ROW row;

void exiterr(int exitcode)
{
	fprintf( stderr, "%s\n", mysql_error(&mysql) );
	exit( exitcode );
}

int main()
{
	uint i = 0;

	if (!(mysql_connect(&mysql,"host","username","password"))) 
		exiterr(1);

	if (mysql_select_db(&mysql,"payroll"))
		exiterr(2);

	if (mysql_query(&mysql,"SELECT name,rate FROM emp_master"))
		exiterr(3);

	if (!(res = mysql_store_result(&mysql)))
		exiterr(4);

	while((row = mysql_fetch_row(res))) {
		for (i=0 ; i < mysql_num_fields(res); i++) 
			printf("%s\n",row[i]);
	}

	mysql_free_result(res);
	mysql_close(&mysql);
}

The call mysql_query will send the query to the server. If the query succeeds, the mysql_store_result call will allocate a MYSQL_RES structure and retrieve the results from the server. Once you have a MYSQL_RES result structure, you may view the data with mysql_fetch_row. This will give you a MYSQL_ROW pointer to one row of data. The MYSQL_ROW pointer is simply an array of character strings. All data types are converted to character strings for the client.

mysql_num_fields will tell you the number of columns returned. You may continue to call mysql_fetch_row until it returns a NULL pointer to retrieve every row from the query.

Note that in this example we are not checking for columns with NULL pointers. If you use tables that do not use NOT NULL columns, then you must check if a particular row's column is NULL.

Once you are finished with a result set, you must free the result. This is accomplished with mysql_free_result.

The final call mysql_close will release your connection to the database.

If you execute a query that does not return any data such as a INSERT query, then you simply call mysql_query and you are done.

Viewing the Result Set

You may find out ahead of time how many rows are in a result set without calling mysql_fetch_row. This is accomplished with int mysql_num_rows(MYSQL_RES *result).

To change which row is returned by the next mysql_fetch_row call, you may use void mysql_data_seek(MYSQL_RES *res, uint offset) to change the position to any arbitrary row (offset).

Retrieving Additional Information

There are several additional functions at your disposal to find out more information about a query, and to retrieve information from the server.

If you execute an UPDATE, INSERT, or DELETE query you may find out how many rows were affected with int mysql_affected_rows(MYSQL *mysql). It will return an integer that specifies the number of rows affected, 0 if nothing was affected, or -1 if it failed.

If you have binary data in your database, it will be useful to know the size of the data returned. unsigned int *mysql_fetch_lengths(MYSQL_RES *mysql) will return an array of integers which specify the size of each column in the result set.

When you INSERT into a table with an AUTO_INCREMENT column, you may find out the id generated for that row with int mysql_insert_id(MYSQL *mysql).

Previous | Table of Contents | Next