Building a Store Application With MySQL++ and C/C++ - Bringing it all together

(Page 4 of 7 )

Now that we have a basic knowledge of the MySQL++ library, we can begin working on our actual store application. What we want to do is create a basic console application that will allow us to manipulate our database, adding and deleting records as we go. Here's the main menu of our application, which we will create shortly:



As you can see, we have quite a lot of options to implement here. Create a new C++ console application called store.c. We will start where all C/C++ applications start -- including header files:

#include <stdio.h> //for functions such as printf() and sprintf()

#include <stdlib.h> //for the system() function.

#include <mysql.h> //mysql header file

#include <conio.h> //for getch()

#include <string.h> //for string functions

Next, we will list our function prototypes. We only have one function and it doesn't require much explanation. It will print our menu (as seen in the picture above) on the screen every time we call it:

void PrintSelection(void); //prints the user selection menu

And the implementation of the function is as follows:

void PrintSelection(void)

{

printf("User Menu:\n");

printf("1. Total Number of Products\n");

printf("2. Total Number of Categories\n");

printf("3. Add Category\n");

printf("4. Delete Category\n");

printf("5. Add Product\n");

printf("6. Delete Product\n");

printf("7. Exit\n");

}

A number of variables will be used throughout our store application. I've tried to give them as meaningful names as possible:

MYSQL *pConnection; //pointer the MYSQL structure

MYSQL_RES *pResult; //pointer to the result set structure

MYSQL_ROW Row; //row information

I will discuss the MYSQL_RES and MYSQL_ROW structures later in this article. The next three variables will be generic multi-purposed variables:

int i; //an index for our for loops

char Input[32]; //general input variable

char Query[256]; //used for queries

char C; //this will be used with getch()

The following group of variables will be used to creating a new category. Each variable will represent one field in our category table:

char newCategoryName[20];

char newCategoryID[5];

char newCategoryIsle[5];

And lastly, this group of variables will be used during the addition of new products. Once again, each variable represents one field in the table:

char newProductName[20];

char newProductQuantity[20];

char newProductSerial[20];

char newProductPrice[20];

char newProductParentID[5];

With all our variables declared we can begin writing our main function. The first thing we need to do is connect to our database:

pConnection = mysql_init(NULL); //init the structure

if(!pConnection) //error, quit the program

return 0;

printf("Attempting Connection to MySQL\n");

if(mysql_real_connect(pConnection,"localhost", "admin", "password", "Article1", 0, NULL, 0) == NULL) //error, quit the program

return 0;

printf("Connected to the Database. Press any key to continue\n");

getch();

system("cls"); //clear the screen

With a connection established, we can enter our main loop and provide the user with our menu. Every time the user sees our menu he will choose one of the options presented to him. We will take the user input into the variable C, which we will switch:

do

{

PrintSelection(); //show the selection

C = getch();

switch(C)

{

case '1': break;

case '2': break;

case '3': break;

case '4': break;

case '5': break;

case '6': break;

default: continue;

} //End of switch

}while( C != '7');

This is where the fun part begins: we will go through each possible case branch, creating the code as we go.

Case 1: The user wishes to see the total number of products available in the database. In order to accomplish this we must do several things. Firstly we need to query the database, receive the result and then check the number of rows (or records) present in that result.

For the query part we will use the mysql_query() function. This function requires two parameters: a handle to the database (our MYSQL structure) and a valid SQL query statement. Our statement is straightforward; simply selecting every record in the products table:

mysql_query(pConnection,"SELECT * FROM products"); //query the database

Now that we have queried the database, we need to store the result somewhere. If you come from the world of PHP then you're probably used to employing a generic variable, since PHP has no distinction between data types. In fact, the same variable can store anything you want throughout different parts of the PHP script.

Obviously this is not true for C or C++. Just as you would have a special data type to store an int or char, you must also have a special data type to store the mysql result set. This is where the MYSQL_RES structure comes in. This structure will hold the result of any query that returns rows (i.e. SELECT, SHOW, etc). To store the result from the query we use mysql_store_result(). This function (like many others) requires a handle to the database as its parameter. The last query that was made will have its result stored:

pResult = mysql_store_result(pConnection); //store the result from our previous query

It's important to note that once you store a result set in MYSQL_RES, you must free it before you can store a new one in it -- failing to do so will crash your program. Luckily, freeing the result set is as easy as calling a function. To free the result set you must call mysql_free_result() and pass your structure as the parameter:

mysql_free_result(pResult);

At this point we have our result set and we must do something with it. Since the user asked for the total number of products, we need to find out how many rows were returned from our "select *" query. Once again, the good people who developed MySQL gave us a simple a way to pull this off -- mysql_num_rows(). It's a simple yet very useful function, because all you have do is pass in the result set as its only parameter and voila... it returns the total number of rows in that result set:

mysql_num_rows(pResult)

The complete code for case 1 is presented below:

case '1':

{

mysql_query(pConnection,"SELECT * FROM products"); //query the database

pResult = mysql_store_result(pConnection);

printf("\n\nThe Number Of products in the database: %d\n\n", mysql_num_rows(pResult));

mysql_free_result(pResult);

break;

}

Next: Cases 2 and 3 >>
 

More C++ Articles
More By Igal Raizman

Comments On: Building a Store Application With MySQL++ and C/C++

>>> Be the FIRST to comment on this article!