C++

Building a Store Application With MySQL++ and C/C++

MySQL is a great cross platform database solution. One of the reasons why MySQL is so popular is because it can be accessed from so many programming languages -- including C/C++. In this article Igal shows us how to download, install and use MySQL++, which is a C library that allows us to work with MySQL databases. Igal starts with basic connection principles and works up to creating a fully interactive product store application.

Building a Store Application With MySQL++ and C/C++ - Cases 4, 5 and 6(Page 6 of 7 )

Case 4: Deleting a category. This step is slightly more complicated than it appears. We canít just create a query that deletes the category from the categories table. The query will work and delete the category, however we will have a whole bunch of products left in our products table that don't belong to ant category, which could become problematic later on.

These products will take up unnecessary space and slow down our system. So when we delete a category we must make sure we delete all the products that belong to it. Have a look at the following fragment of code:

To begin with, we show the user a list of all the categories currently available in the database. Each category is shown with it's corresponding ID. The code that accomplishes this is shown here:

mysql_query(pConnection,"SELECT * FROM category");

pResult = mysql_use_result(pConnection);

while ((Row = mysql_fetch_row(pResult)))

{

printf("%s. [%s]\n",Row[0],Row[1]);

}

mysql_free_result(pResult);

At this point we begin to deal with the individual rows and begin using the MYSQL_ROW structure which we declared earlier. We use mysql_fetch_row() to obtain each row from our result set and store it in the Row variable. This is done via the while loop -- while Row is not NULL.

Once we receive a certain row we deal with it as an array. You might want to re-read that statement because it's very important: Row is just an array of fields. Therefore, Row[0] will give us the first element in that array, and Row[1] will give us the second element in the array, etc.

The output from the loop looks like this:

Next we take the user input and create our query. In that query, we select the CategoryID that belongs to the category and use that CategoryID when we delete the products as well:

gets(Input);

//get the categoryID associated with the category

//so that we could delete all the products that are in this category

sprintf(Query, "SELECT * FROM category WHERE ID=%s",Input);

mysql_query(pConnection,Query); //query the database

pResult = mysql_use_result(pConnection);

Row = mysql_fetch_row(pResult);

Row now contains the all the information concerning this category and Row[2] contains the CategoryID. Thus we can now use Row[2] to delete all of the products by creating the following statement:

sprintf(Query, "DELETE FROM products WHERE ParentID=%s",Row[2]);

And finally, after all the products have been deleted, we are free to delete the category from the category table.

Case 5: Adding a product. The code in this case is very similar to case 4 and I will not go through it line by line since most of it will be repetitive. Initially, we ask the user to choose a category into which he would like to add the product. We then use that category to obtain its CategoryID. Following that, we enter the product's information (price, amount, etc.) and combine all of this into one SQL statement:

Case 6: I will leave case 6 in your hands. The source code accompanying this article contains all of the code that we've examined throughout this article, so you should have no trouble working out the code to delete a product.