Paginate MySQL query results in C++

A detailed guide on how to paginate MySQL query results in C++ using Prepared Statements. This article helps you understand how to query data and efficiently paginate results when working with MySQL in C++.

In this article, we will explore how to paginate query results from MySQL using C++ and Prepared Statements. You'll learn how to connect to MySQL, perform queries, and split data into smaller pages for display.

C++ code

#include <iostream>
#include <mysql/mysql.h>

// MySQL connection details
#define SERVER "localhost"
#define USER "root"
#define PASSWORD "password"
#define DATABASE "test_db"

int main() {
    MYSQL *conn;
    MYSQL_STMT *stmt;
    MYSQL_RES *result;
    MYSQL_ROW row;
    
    conn = mysql_init(NULL);

    // Connect to the database
    if (!mysql_real_connect(conn, SERVER, USER, PASSWORD, DATABASE, 0, NULL, 0)) {
        std::cerr << "Connection failed: " << mysql_error(conn) << std::endl;
        return 1;
    }

    // Query with LIMIT and OFFSET
    int page = 1; // First page
    int pageSize = 5; // Number of records per page
    int offset = (page - 1) * pageSize;

    std::string query = "SELECT id, name FROM users LIMIT ? OFFSET ?";
    
    // Prepare the statement
    stmt = mysql_stmt_init(conn);
    if (!stmt) {
        std::cerr << "Could not initialize Prepared Statement: " << mysql_error(conn) << std::endl;
        return 1;
    }

    if (mysql_stmt_prepare(stmt, query.c_str(), query.length())) {
        std::cerr << "Query preparation failed: " << mysql_stmt_error(stmt) << std::endl;
        return 1;
    }

    // Bind parameters
    MYSQL_BIND bind[2];
    memset(bind, 0, sizeof(bind));

    bind[0].buffer_type = MYSQL_TYPE_LONG;
    bind[0].buffer = (char *)&pageSize;

    bind[1].buffer_type = MYSQL_TYPE_LONG;
    bind[1].buffer = (char *)&offset;

    if (mysql_stmt_bind_param(stmt, bind)) {
        std::cerr << "Parameter binding failed: " << mysql_stmt_error(stmt) << std::endl;
        return 1;
    }

    // Execute the query
    if (mysql_stmt_execute(stmt)) {
        std::cerr << "Query execution failed: " << mysql_stmt_error(stmt) << std::endl;
        return 1;
    }

    // Fetch results
    result = mysql_stmt_result_metadata(stmt);
    int num_fields = mysql_num_fields(result);

    // Read and print the results
    MYSQL_BIND resultBind[num_fields];
    memset(resultBind, 0, sizeof(resultBind));

    unsigned long length[2];
    char id[10];
    char name[50];
    bool is_null[2];
    bool error[2];

    resultBind[0].buffer_type = MYSQL_TYPE_STRING;
    resultBind[0].buffer = id;
    resultBind[0].buffer_length = sizeof(id);
    resultBind[0].is_null = &is_null[0];
    resultBind[0].length = &length[0];
    resultBind[0].error = &error[0];

    resultBind[1].buffer_type = MYSQL_TYPE_STRING;
    resultBind[1].buffer = name;
    resultBind[1].buffer_length = sizeof(name);
    resultBind[1].is_null = &is_null[1];
    resultBind[1].length = &length[1];
    resultBind[1].error = &error[1];

    if (mysql_stmt_bind_result(stmt, resultBind)) {
        std::cerr << "Result binding failed: " << mysql_stmt_error(stmt) << std::endl;
        return 1;
    }

    while (!mysql_stmt_fetch(stmt)) {
        std::cout << "ID: " << id << ", Name: " << name << std::endl;
    }

    // Clean up
    mysql_free_result(result);
    mysql_stmt_close(stmt);
    mysql_close(conn);

    return 0;
}

Detailed explanation

• mysql_real_connect: Connects to the MySQL database with the provided credentials.
• mysql_stmt_prepare: Prepares the SQL query with LIMIT and OFFSET for pagination.
• mysql_stmt_bind_param: Binds the parameters to the query (pageSize and offset).
• mysql_stmt_execute: Executes the query with the bound parameters.
• mysql_stmt_bind_result: Binds the query results to variables for reading and displaying.

System Requirements:

• C++: C++11 or later
• MySQL Connector/C++: Latest version compatible with the system

How to install the libraries:

1. Install MySQL Connector/C++:
   • Ubuntu: sudo apt-get install libmysqlclient-dev
   • Windows: Download and install from the MySQL website.

Tips:

• Always check the connection and query results to avoid runtime errors.
• Using Prepared Statements increases security and performance when working with MySQL.

---