Queries That Return No Resultset

So far you've learned how to run a query and check whether it ran successfully. You've also seen how to fetch the resultset, and, in the case of a NULL result, how to check whether this was because of an error or because the query wasn't meant to produce a result, such as with an INSERT or DELETE.

As a result, you've built some resilient code that can run a query of either type. You can modify the SQL query in any of the preceding listings to make it an INSERT, DELETE, and so on, and the code still runs. If there's no resultset from the query, the code produces a message telling you whether this is okay, or whether an error occurred.

However, if you do run a query that is meant to modify your data, how can you tell what effect it had?

You can use two important functions: mysql_affected_rows() and mysql_insert_id(). Their syntax is as follows:

mysql_affected_rows (*mysql)

and

mysql_insert_id (*mysql)

mysql_affected_rows() returns an integer of datatype my_ulonglong, which contains the number of rows affected from the query.

If you are performing an INSERT into a table with an AUTO_INCREMENT column, mysql_insert_id() returns an integer of datatype my_ulonglong, which is the ID generated in that column as a result.

Here's a code example that you could modify and use to handle an INSERT query (or an UPDATE, DELETE, and so on, although in these cases the mysql_insert_id() function would not be used).

It's much the same as the previous code you have seen, but a little trimmed down for simplicity (it assumes that you're doing a query that returns no resultset). If the query works without error, it prints out how many rows were affected by the INSERT, and the ID created in an AUTO_INCREMENT column as a result:

if (mysql_query (mysql,
    "INSERT INTO some_table VALUES (...)"  /* put an INSERT query here */
    ) == 0) {
    printf ("Query succeeded.
");
    if ((res_set = mysql_store_result (mysql)) == NULL) {
        /* No result set (should be the case with INSERT) */
        if (mysql_field_count (mysql) == 0) {
            /* Field count is 0 (should be the case with INSEERT) */
            fprintf (stdout, "Rows affected: %lu
",
                (unsigned long) mysql_affected_rows (mysql));
            fprintf (stdout, "Insert ID: %lu
",
                (unsigned long) mysql_insert_id (mysql));
        }
    }
} else {
    fprintf (stderr,
        "Query failed: %s
", mysql_error (mysql));
}

You can try this yourself, performing an INSERT into a table of your choice. It should give an output something like this:

$ ./myapp
Enter password:
Connected.
Query succeeded.
Rows affected: 1
Insert ID: 24