How to do it…

1. Initialize a MYSQL object:

mysql_init(NULL);

2. Establish a connection to the MySQL server running at the specified host. Also, generate a connection handler. If any error occurs in establishing a connection to the MySQL server engine, the program will terminate:

  if (!mysql_real_connect(conn, server, user, password, database, 0, 
    NULL, 0)) {
      fprintf(stderr, "%s\n", mysql_error(conn));
      exit(1);
  }

3. If the connection to the MySQL database engine is established successfully, you will be prompted to enter the email address of the user whose record you want to delete:

 printf("Enter email address of the user to delete: ");
 scanf("%s", emailaddress);

4. Create an SQL SELECT statement that will search the row from the users table that matches the email address that's entered by the user:

 strcpy(sqlquery,"SELECT * FROM users where email_address like \'");
 strcat(sqlquery,emailaddress);
 strcat(sqlquery,"\'");

5. Execute the SQL SELECT statement. The program will terminate after displaying an error message if the SQL query does not execute successfully:

 if (mysql_query(conn, sqlquery) != 0)                 
 {                                                                                                                                   
    fprintf(stderr, "No row found in the users table with this email 
    address\n");                                                                                                     
    exit(1);                                                                     
 }  

6. If the query executes successfully, then the resulting row(s) that match the supplied email address will be retrieved and assigned to the resultset:

resultset = mysql_store_result(conn);

7. Invoke the mysql_num_rows function to ensure that there is at least one row in the resultset:

if(mysql_num_rows(resultset) >0)

8. If there is no row in the resultset, that means no row was found in the users table that matches the given email address; hence, the program will terminate:

printf("No user found with this email address\n");

9. If there is any row in the result set, that row is extracted from the resultset and will be assigned to the array row:

row = mysql_fetch_row(resultset)

10. The information of the user is displayed by displaying the corresponding subscripts in the array row:

printf("Email Address: %s \n", row[0]);
printf("Password: %s \n", row[1]);
printf("Address of delivery: %s \n", row[2]);

11. The memory that's allocated to the resultset is freed up:

mysql_free_result(resultset);The user is asked whether he/she really want to delete the shown record.
printf("Are you sure you want to delete this record yes/no: ");
scanf("%s", k);

12. If the user enters yes, an SQL DELETE statement will be created that will delete the row from the users table that matches the specified email address:

if(strcmp(k,"yes")==0)
{
    strcpy(sqlquery, "Delete from users where email_address like 
    \'");
    strcat(sqlquery,emailaddress);
    strcat(sqlquery,"\'");

13. The SQL DELETE statement is executed. If there are any error occurs in executing the SQL DELETE query, the program will terminate:

if (mysql_query(conn, sqlquery) != 0)                 
{                                                                                   
    fprintf(stderr, "The user account could not be deleted\n");                                                             
    exit(1);                                                                     
}

14. If the SQL DELETE statement is executed successfully, a message informing that the user account with the specified email address is deleted successfully is displayed:

printf("The user with the given email address is successfully deleted from the users table\n");

15. The opened connection handler is closed:

mysql_close(conn);

The deleteuser.c program for deleting a specific row from a MySQL database table is shown in the following code:

#include <mysql/mysql.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>

void main() {
MYSQL *conn;
MYSQL_RES *resultset;
MYSQL_ROW row;
char *server = "127.0.0.1";
char *user = "root";
char *password = "Bintu2018$";
char *database = "ecommerce";
char emailaddress[30], sqlquery[255],k[10];
conn = mysql_init(NULL);
if (!mysql_real_connect(conn, server, user, password, database, 0, NULL, 0)) {
    fprintf(stderr, "%s\n", mysql_error(conn));
    exit(1);
}
printf("Enter email address of the user to delete: ");
scanf("%s", emailaddress);
strcpy(sqlquery,"SELECT * FROM users where email_address like \'");
strcat(sqlquery,emailaddress);
strcat(sqlquery,"\'");
if (mysql_query(conn, sqlquery) != 0)                 
{                                                                          
    fprintf(stderr, "No row found in the users table with this email 
    address\n");                                                             
    exit(1);                                                                      
}  
resultset = mysql_store_result(conn);
if(mysql_num_rows(resultset) >0)
{
    printf("The details of the user with this email address are as 
    follows:\n");
    while ((row = mysql_fetch_row(resultset)) != NULL)
    {
        printf("Email Address: %s \n", row[0]);
        printf("Password: %s \n", row[1]);
        printf("Address of delivery: %s \n", row[2]);
    }
    mysql_free_result(resultset);
    printf("Are you sure you want to delete this record yes/no: ");
    scanf("%s", k);
    if(strcmp(k,"yes")==0)
    {
        strcpy(sqlquery, "Delete from users where email_address like 
        \'");
        strcat(sqlquery,emailaddress);
        strcat(sqlquery,"\'");
        if (mysql_query(conn, sqlquery) != 0)                 
        {                                                                                 
            fprintf(stderr, "The user account could not be deleted\n");                                                             
            exit(1);                                                                      
        }  
        printf("The user with the given email address is successfully 
        deleted from the users table\n");
    }
}
else
    printf("No user found with this email address\n");
    mysql_close(conn);
}

Now, let's go behind the scenes to understand the code better.