In this article, we will learn about the deletion of data from the database using C program. Database is a structured collection of information. It stores data in an organized manner. We are going to learn how to delete data from database using C language.
The purpose of this article is to demonstrate the process of removing the records from the table by using the delete command. In order to understand the delete command, it is necessary to know about tables and rows. Tables are the basic unit of data storage in any relational database system. There are two types of tables: primary and secondary. Primary tables contain no constraints. On the other hand, secondary tables are limited by the constraints that are placed on them by the database administrator. The main reason to use secondary tables is for performance reasons. Because the secondary tables have fewer columns, they are faster to query and retrieve data. They also don’t require the same amount of storage space because they don’t hold all the information from the original tables. The original data, however, remains intact.
This is a very simple program to delete all the records stored in the MySQL database. The program is called as ‘delete’. This program is very simple and can be easily understood. In this program we use mysql _fetch_row to get all the records from the database table. Then we ‘DELETE’ to remove all the record. We use ‘mysql_fetch_row(resultset)’ to fetch all the records one by one.
#include <mysql/mysql.h>
#include
#include
#include
void main() {
MYSQL *conn;
MYSQL_RES *resultset;
MYSQL_ROW row;
char *server = "127.0.0.1";
char *user = "root";
char *password = "pass123";
char *database = "userdatabase";
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);
}
We will start by invoking the mysql_init function to initialize a MYSQL object named conn. Then, we will pass the MYSQL object conn to the mysql_real_connect function that we invoked to establish a connection to the MySQL server running at the specified host. Several other parameters will also be passed to the mysql_real_connection function, including a valid user ID, password, host details, and the database with which we want to work. The mysql_real_connect function will establish a connection to the MySQL server running at the specified host and will declare a MYSQL object conn as the connection handler. This means that conn can connect to the MySQL server and the commerce database wherever it is used.
The program will stop after displaying an error message if any errors occur during the connection establishment to the ecommerce database or to the MySQL server engine. If the connection to the ecommerce database is established successfully, you will be prompted to enter the email address of the user whose record you want to delete.
