Adding data to the database

For this demo we need data in the database. There are a number of ways to accomplish this, in the real world we would write an application to do so. For the sake of simplicity we will use the command line. Open a command prompt and login to mysql:

mysql>use personnel;
mysql>insert into person values (NULL, 'Molehusband', 'Reginald', '222 safe street', 'uk',
      '1234567', '2/22/1958', '4/2/2002', '1', '', '222-44-6543', 'niggles are sappy');

The second command is all on one line. Add a few more entries, as many as you want (the demo's personnel.sql file adds a few for you).

To update an existing entry do something like:

mysql>update person set street='19000 hartford lane' where id='7';

Here we change the street of the user with an id of 7, presuming that user exists.

To see everything in the database:

mysql>select * from person;

Now to make a change to to the person class to sort the rows returned by last then firstname. Find the rows function in the person.cpp file, change the line

const string query("SELECT * from person");

const string query("SELECT * from person ORDER BY LAST,FIRST");

Now let's add a function that will return an individual's data from the database. In the public section of the class person in person.h add a function definition

// return a row in the table by last,first name value
personROW individual(const string& first, const string& last);

Now add the actual function implementation to the person.cpp file

// return a row in the table by last,first name value
person::personROW person::individual(const string& first, const string& last) {
   person::personROW row;
   MYSQL mysql = DB.dbglobal();

   // select id,first,last,notes,city  from person where last='LASTNAME' and first='FIRSTNAME';
   const string query("SELECT * from person WHERE last='" + last + "' and first='" + first + "';");
   DB.query(query);

   MYSQL_RES *result = mysql_store_result(DB.database());
   if(result) {
      MYSQL_ROW r;
      r = mysql_fetch_row(result);
      if(r == 0)
         throw DBerror(mysql_error(&mysql));

      std::istringstream VAL;
      unsigned long* lengths = mysql_fetch_lengths(result);
      if(r[0]) {
         VAL.str(r[0]);
         VAL >> row.id;
         VAL.clear();
      }
      if(r[1])
         row.first = r[1];
      if(r[2])
         row.last = r[2];
      if(r[3])
         row.street = r[3];
      if(r[4])
         row.city = r[4];
      if(r[5])
         row.state = r[5];
      if(r[6])
         row.zip = r[6];
      if(r[7])
         row.dob = DB.parseDate(r[7]);
      if(r[8])
         row.doe = DB.parseDate(r[8]);
      if(r[9])
         row.dept = r[9];
      if(r[10])
         row.social = r[10];
      if(r[11])
         row.notes = r[11];
      // cleanup
      mysql_free_result(result);
   }
   return row;
}

Get the database
Query it for the person you need
use mysql_store_result to get the data
fill out a personROW with the data and return it.

Adding data to the database

Basic SQL