MySQL

Creating a CRUD application in C with a MySQL database involves using the MySQL C API. Here's a step-by-step guide to create such an application:

Setup

Install MySQL and MySQL C Connector:

Make sure MySQL is installed on your system. Also, you need to install the MySQL C Connector:

sudo apt-get install libmysqlclient-dev

Create MySQL Database and Table:

Open your MySQL client and create a database and a table:

CREATE DATABASE crud_db;
USE crud_db;
CREATE TABLE users (
 id INT AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(255),
 email VARCHAR(255)
);

Code

Include Necessary Headers:

Create a file crud.c and include necessary headers:

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

Define MySQL Connection Function:

MYSQL *connect_db() {
   MYSQL *conn = mysql_init(NULL);
   if (conn == NULL) {
       fprintf(stderr, "mysql_init() failed\n");
       exit(EXIT_FAILURE);
   }
   if (mysql_real_connect(conn, "localhost", "root", "password", "crud_db", 0, NULL, 0) == NULL) {
       fprintf(stderr, "mysql_real_connect() failed\n");
       mysql_close(conn);
       exit(EXIT_FAILURE);
   }
   return conn;
}

Define CRUD Operations:

void create_user(MYSQL *conn, const char *name, const char *email) {
   char query[256];
   snprintf(query, sizeof(query), "INSERT INTO users (name, email) VALUES ('%s', '%s')", name, email);
   if (mysql_query(conn, query)) {
       fprintf(stderr, "INSERT error: %s\n", mysql_error(conn));
   } else {
       printf("User added.\n");
   }
}
void read_users(MYSQL *conn) {
   if (mysql_query(conn, "SELECT * FROM users")) {
       fprintf(stderr, "SELECT error: %s\n", mysql_error(conn));
       return;
   }
   MYSQL_RES *result = mysql_store_result(conn);
   if (result == NULL) {
       fprintf(stderr, "mysql_store_result() failed: %s\n", mysql_error(conn));
       return;
   }
   int num_fields = mysql_num_fields(result);
   MYSQL_ROW row;
   while ((row = mysql_fetch_row(result))) {
       for (int i = 0; i < num_fields; i++) {
           printf("%s ", row[i] ? row[i] : "NULL");
       }
       printf("\n");
   }
   mysql_free_result(result);
}
void update_user(MYSQL *conn, int id, const char *name, const char *email) {
   char query[256];
   snprintf(query, sizeof(query), "UPDATE users SET name='%s', email='%s' WHERE id=%d", name, email, id);
   if (mysql_query(conn, query)) {
       fprintf(stderr, "UPDATE error: %s\n", mysql_error(conn));
   } else {
       printf("User updated.\n");
   }
}
void delete_user(MYSQL *conn, int id) {
   char query[256];
   snprintf(query, sizeof(query), "DELETE FROM users WHERE id=%d", id);
   if (mysql_query(conn, query)) {
       fprintf(stderr, "DELETE error: %s\n", mysql_error(conn));
   } else {
       printf("User deleted.\n");
   }
}

Main Function:

int main() {
   MYSQL *conn = connect_db();
   int choice, id;
   char name[255], email[255];
   while (1) {
       printf("1. Create User\n");
       printf("2. Read Users\n");
       printf("3. Update User\n");
       printf("4. Delete User\n");
       printf("5. Exit\n");
       printf("Enter your choice: ");
       scanf("%d", &choice);
       switch (choice) {
           case 1:
               printf("Enter name: ");
               scanf("%s", name);
               printf("Enter email: ");
               scanf("%s", email);
               create_user(conn, name, email);
               break;
           case 2:
               read_users(conn);
               break;
           case 3:
               printf("Enter user ID: ");
               scanf("%d", &id);
               printf("Enter new name: ");
               scanf("%s", name);
               printf("Enter new email: ");
               scanf("%s", email);
               update_user(conn, id, name, email);
               break;
           case 4:
               printf("Enter user ID: ");
               scanf("%d", &id);
               delete_user(conn, id);
               break;
           case 5:
               mysql_close(conn);
               exit(EXIT_SUCCESS);
           default:
               printf("Invalid choice.\n");
       }
   }
   mysql_close(conn);
   return 0;
}

Compilation and Execution

Compile the program:

gcc -o crud crud.c -lmysqlclient

Run the program:

./crud

This will provide you with a basic terminal-based CRUD application using C and MySQL. The program allows you to create, read, update, and delete users from the database.