medium
0 views

Developing a To-Do List Application Using PHP and SQL

Build a complete To-Do List web application using PHP for backend logic and SQL for data storage with full CRUD operations

Understand the Problem

Problem Statement

In this tutorial, you will learn how to create a fully functional To-Do List web application using PHP and SQL. The application must support all four CRUD operations:

  • Create: Add new tasks to the database
  • Read: Retrieve and display all existing tasks
  • Update: Modify existing tasks (bonus feature)
  • Delete: Remove tasks from the database

The application should have a clean HTML interface with CSS styling, JavaScript for client-side interactions, and PHP scripts to handle database operations. All tasks should be stored in a MySQL database with proper error handling and security measures.

Constraints

  • Must use PHP 7.4 or higher for backend processing
  • Must use MySQL or MariaDB for data storage
  • Database table must have auto-incrementing primary key
  • Task descriptions must be non-empty strings (1-255 characters)
  • Must implement proper SQL injection prevention
  • Application must handle database connection errors gracefully
  • Must support UTF-8 character encoding for international tasks
  • Must include proper form validation on both client and server sides

Examples

Example 1
Input
User visits index.php and sees empty task list
Output
Display 'No tasks found' message with form to add new tasks
Explanation

Initial state shows empty list when database has no tasks, prompting user to add new items

Example 2
Input
User enters 'Buy groceries' in form and clicks 'Add Task'
Output
Task appears in the list with timestamp, form clears for next entry
Explanation

Successful task creation adds record to database and updates display without page refresh (if using AJAX)

Example 3
Input
User clicks delete button next to existing task 'Buy groceries'
Output
Task is removed from database and disappears from the list
Explanation

Delete operation removes the database record and updates the UI to reflect the change

Solution

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

#define MAX_TASK_LENGTH 255
#define HOST "localhost"
#define USER "your_username"
#define PASS "your_password"
#define DB "todo_app"

MYSQL *conn;

// Initialize database connection
int init_db() {
    conn = mysql_init(NULL);
    if (!conn) {
        printf("MySQL initialization failed\n");
        return 0;
    }
    
    if (!mysql_real_connect(conn, HOST, USER, PASS, DB, 0, NULL, 0)) {
        printf("Connection failed: %s\n", mysql_error(conn));
        return 0;
    }
    return 1;
}

// Add a new task
void add_task(const char *task) {
    if (strlen(task) == 0 || strlen(task) > MAX_TASK_LENGTH) {
        printf("Invalid task length\n");
        return;
    }
    
    char query[500];
    snprintf(query, sizeof(query), "INSERT INTO tasks (task) VALUES ('%s')", 
             mysql_real_escape_string(conn, malloc(strlen(task)*2+1), task, strlen(task)));
    
    if (mysql_query(conn, query)) {
        printf("Error adding task: %s\n", mysql_error(conn));
    } else {
        printf("Task added successfully\n");
    }
}

// Display all tasks
void show_tasks() {
    if (mysql_query(conn, "SELECT id, task, created_at FROM tasks ORDER BY created_at DESC")) {
        printf("Error retrieving tasks: %s\n", mysql_error(conn));
        return;
    }
    
    MYSQL_RES *result = mysql_store_result(conn);
    if (result == NULL) {
        printf("No tasks found\n");
        return;
    }
    
    MYSQL_ROW row;
    printf("\nCurrent Tasks:\n");
    printf("===============\n");
    while ((row = mysql_fetch_row(result))) {
        printf("%s: %s (Added: %s)\n", row[0], row[1], row[2]);
    }
    
    mysql_free_result(result);
}

// Delete a task by ID
void delete_task(int id) {
    char query[200];
    snprintf(query, sizeof(query), "DELETE FROM tasks WHERE id = %d", id);
    
    if (mysql_query(conn, query)) {
        printf("Error deleting task: %s\n", mysql_error(conn));
    } else {
        printf("Task deleted successfully\n");
    }
}

// Interactive menu
void show_menu() {
    printf("\nTo-Do List Application\n");
    printf("=====================\n");
    printf("1. Add Task\n");
    printf("2. Show Tasks\n");
    printf("3. Delete Task\n");
    printf("4. Exit\n");
    printf("Enter your choice: ");
}

int main() {
    if (!init_db()) {
        return 1;
    }
    
    printf("To-Do List Application (C Version)\n");
    
    char task[MAX_TASK_LENGTH + 1];
    int choice, task_id;
    
    while (1) {
        show_menu();
        scanf("%d", &choice);
        getchar(); // consume newline
        
        switch (choice) {
            case 1:
                printf("Enter task: ");
                fgets(task, sizeof(task), stdin);
                task[strcspn(task, "\n")] = 0; // remove newline
                add_task(task);
                break;
            case 2:
                show_tasks();
                break;
            case 3:
                printf("Enter task ID to delete: ");
                scanf("%d", &task_id);
                delete_task(task_id);
                break;
            case 4:
                printf("Goodbye!\n");
                mysql_close(conn);
                return 0;
            default:
                printf("Invalid choice\n");
        }
    }
    
    mysql_close(conn);
    return 0;
}
Time:O(n) for displaying all tasks, O(1) for adding/deleting individual tasks
Space:O(1) - only stores connection handle and temporary variables
Approach:

C Implementation Overview:

This C version demonstrates a command-line To-Do List application using MySQL C API. The implementation follows these steps:

  1. Database Connection: Uses mysql_init() and mysql_real_connect() to establish connection
  2. Task Management: Implements add_task(), show_tasks(), and delete_task() functions
  3. Input Validation: Checks task length and validates user input
  4. SQL Injection Prevention: Uses mysql_real_escape_string() to sanitize inputs
  5. Error Handling: Comprehensive error checking for all database operations
  6. Interactive Interface: Menu-driven command-line interface for user interaction

Key Features:

  • Proper memory management with malloc for string escaping
  • Safe string handling to prevent buffer overflows
  • Structured error reporting with mysql_error()
  • Clean separation of concerns between database operations and UI

Visual Explanation

Loading diagram...
All Problems