ComplexQuery.c

#pragma warning(disable : 4996)

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

#define CREATE 1
#define READ 2
#define UPDATE 3
#define DELETE_RECORD 4
#define COMPLEX_QUERY 5
#define EXIT 10

#define MAX_STRING_SIZE 500

void executeComplexQuery(MYSQL* databaseObject);
void topPaidPlayers(MYSQL* databaseObject);
void clubsWithHighestBudgets(MYSQL* databaseObject);

void executeComplexQuery(MYSQL* databaseObject) {
    int choice;
    printf("\n-------- Complex Queries --------\n");
    printf("1. List Top 5 Highest Paid Players\n");
    printf("2. Show Clubs with Highest Player Budgets\n");
    printf("3. Back to Main Menu\n");
    printf("Select an option (1-3): ");
    choice = getNum();

    switch (choice) {
    case 1:
        topPaidPlayers(databaseObject);
        break;
    case 2:
        clubsWithHighestBudgets(databaseObject);
        break;
    default:
        printf("Returning to main menu.\n");
    }
}

void topPaidPlayers(MYSQL* databaseObject) {
    const char* query = "SELECT Player.FirstName, Player.LastName, Contract.Salary "
        "FROM Player "
        "JOIN Contract ON Player.PlayerID = Contract.PlayerID "
        "ORDER BY Contract.Salary DESC "
        "LIMIT 5";

    if (mysql_query(databaseObject, query)) {
        printf("Error fetching top paid players: %s\n", mysql_error(databaseObject));
        return;
    }

    MYSQL_RES* res = mysql_store_result(databaseObject);
    if (res == NULL) {
        printf("Error retrieving result: %s\n", mysql_error(databaseObject));
        return;
    }

    MYSQL_ROW row;
    printf("\nFirstName\tLastName\tSalary\n");
    while ((row = mysql_fetch_row(res)) != NULL) {
        printf("%s\t%s\t%s\n", row[0], row[1], row[2]);
    }
    mysql_free_result(res);
}

void clubsWithHighestBudgets(MYSQL* databaseObject) {
    const char* query = "SELECT Club.ClubName, Club.PlayerBudget "
        "FROM Club "
        "ORDER BY Club.PlayerBudget DESC "
        "LIMIT 5";

    if (mysql_query(databaseObject, query)) {
        printf("Error fetching clubs with highest budgets: %s\n", mysql_error(databaseObject));
        return;
    }

    MYSQL_RES* res = mysql_store_result(databaseObject);
    if (res == NULL) {
        printf("Error retrieving result: %s\n", mysql_error(databaseObject));
        return;
    }

    MYSQL_ROW row;
    printf("\nClubName\tPlayerBudget\n");
    while ((row = mysql_fetch_row(res)) != NULL) {
        printf("%s\t%s\n", row[0], row[1]);
    }
    mysql_free_result(res);
}