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: case '4':
{
printf("\n\nShowing Current Categories\n");
mysql_query(pConnection,"SELECT * FROM category"); //query the database
pResult = mysql_use_result(pConnection);
while ((Row = mysql_fetch_row(pResult)))
{
printf("%s. [%s]\n",Row[0],Row[1]);
}
mysql_free_result(pResult); //free the result
printf("Enter Number to Delete: ");
gets(Input); //take the input as a string
//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);
printf("\nQuery Used: %s\n",Query);
mysql_query(pConnection,Query); //query the database
pResult = mysql_use_result(pConnection);
Row = mysql_fetch_row(pResult);
printf("The CategoryID is: %s\n", Row[2] );
mysql_free_result(pResult);
//now that we know the CategoryID for this category, we can delete
//all the products that belong to it
sprintf(Query, "DELETE FROM products WHERE ParentID=%s",Row[2]);
printf("\nQuery Used: %s\n",Query);
if(mysql_query(pConnection,Query) != 0)
{
printf("Error Executing the query: %s\n\n",mysql_error(pConnection));
}
//with the products deleted we can delete the actual category
sprintf(Query, "DELETE FROM category WHERE ID=%s\n",Input);
printf("\nQuery Used: %s\n",Query);
mysql_query(pConnection,Query); //delete the category
printf("Category Deleted\n\n");
break;
}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 '5':
{
printf("\n\nAdding Product:\n");
printf("Choose The Product's Category: \n");
mysql_query(pConnection,"SELECT * FROM category"); //query the database
pResult = mysql_use_result(pConnection);
while ((Row = mysql_fetch_row(pResult)))
{
printf("%s. [%s]\n",Row[0],Row[1]);
}
mysql_free_result(pResult);
printf("Choose the Category: ");
gets(Input); //take the input as a string
//get the categoryID associated with the category
//After this we will be able to set the product's ParentID properly
sprintf(Query, "SELECT * FROM category WHERE ID=%s",Input);
printf("\nQuery Used: %s\n",Query);
mysql_query(pConnection,Query);
pResult = mysql_use_result(pConnection);
Row = mysql_fetch_row(pResult);
printf("The CategoryID is: %s\n", Row[2] );
strcpy(newProductParentID,Row[2]);
mysql_free_result(pResult);
printf("Enter The Product's Name:");
gets(newProductName);
printf("Enter Quantity:");
gets(newProductQuantity);
printf("Enter Serial:");
gets(newProductSerial);
printf("Enter Price:");
gets(newProductPrice);
sprintf(Query, "INSERT INTO products VALUES(NULL,'%s','%s',%d,%d,'%s')", newProductParentID, newProductName, atoi(newProductQuantity), atoi(newProductSerial), newProductPrice);
printf("\nQuery Used: %s\n",Query);
mysql_query(pConnection,Query);
break;
} 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. Next: Conclusion >>
More C++ Articles
More By Igal Raizman |
