SQL statements that return no data

We will look first at UPDATE, DELETE and INSERT statements. Since they return no data from the database, they are easier to use.

The other important function that we will introduce here is a function to check the number of rows affected:

my_ulonglong mysql_affected_rows(MYSQL *connection);

Probably the most obvious thing about this function is the rather unusual return result. For portability reasons, this is a special unsigned type. For use in printf, you're recommended to cast to unsigned long, with a format specification of %lu. This function returns the number of rows affected by the previous UPDATE, INSERT or DELETE query executed using mysql_query.

Unusually for mysql_ functions, a return code of zero indicates no rows affected; a positive number is the actual result, normally the number of affected rows.

As we mentioned earlier, there can be some 'unexpected' results when using mysql_affected_rows. Let's look first at the number of rows affected by INSERT statements, which do behave as expected. We add the following code to our connect2.c program, and call it insert1.c:

#include <stdlib.h>

      #include <stdio.h>
      #include "mysql.h"
      int main(int argc, char *argv[]) {
         MYSQL my_connection;
         int res;
         mysql_init(&my_connection); 
         if (mysql_real_connect(&my_connection, "localhost", 
                                "rick", "bar", "rick", 0, NULL, 0)) {
            printf("Connection success\n");
            res = mysql_query(&my_connection, "INSERT INTO children(fname, age)
                                                        VALUES('Ann', 3)");
            if (!res) {
               printf("Inserted %lu rows\n", 
                       (unsigned long)mysql_affected_rows(&my_connection));
            } else {
               fprintf(stderr, "Insert error %d: %s\n", mysql_errno(&my_connection),
                                              mysql_error(&my_connection));
            }
            mysql_close(&my_connection);
         } else {
            fprintf(stderr, "Connection failed\n");
            if (mysql_errno(&my_connection)) {
            fprintf(stderr, "Connection error %d: %s\n",
                         mysql_errno(&my_connection), mysql_error(&my_connection));
            }
         }
         return EXIT_SUCCESS;
   }

As expected, the number of rows inserted is one.

Now we change the code, so the 'insert' section is replaced with:

 mysql_errno(&my_connection), mysql_error(&my_connection));
         }
      }
      res = mysql_query(&my_connection, "UPDATE children SET AGE = 4 
      WHERE fname = 'Ann'");
      if (!res) {
         printf("Updated %lu rows\n", 
                           (unsigned long)mysql_affected_rows(&my_connection));
      } else {
         fprintf(stderr, "Update error %d: %s\n", mysql_errno(&my_connection),
                                                  mysql_error(&my_connection));
      }

Now suppose our children table has data in it, like this:

Where we execute update1, we would expect the number of rows affected to be reported as 4, but in practice the program reports 2, since it only had to change 2 rows, even though the WHERE clause identified 4 rows. If we want mysql_affected_rows to report the result as 4, which may be the result people familiar with other databases will expect, we need to remember to pass the CLIENT_FOUND_ROWS flag to mysql_real_connect , as in update2.c, like this:

if (mysql_real_connect(&my_connection, "localhost", 
                             "rick", "bar", "rick", 0, NULL, CLIENT_FOUND_ROWS)) {

If we reset the data in our database, then run the program with this modification, it reports the number of affected rows as 4.

The function mysql_affected_rows has one last oddity, which appears when we delete data from the database. If we delete data with a WHERE clause, then mysql_affected_rows returns the number of rows deleted, as we would expect. However, if there is no WHERE clause, and all rows are therefore deleted, the number of rows affected is reported as zero. This is because an optimization deletes the whole table for efficiency reasons. This behavior is not affected by the CLIENT_FOUND_ROWS option flag.