sql - 如何用 C 语言从 SQL 数据库中检索具有格式的消息？

Question

 while(1)
{
    menu();

    char *choice = readline("Your choice: ");
    switch(choice[0])
    {
        case 'P':
        case 'p':
        {
            // Post a tweet
            char *tweet = readline("Enter tweet: ");
            char *lat = readline("Enter latitude (press Enter to skip): ");
            char *lon = readline("Enter longitude (press Enter to skip): ");
            char query[300];
            if (*lat == '\n' || *lon == '\n')
            {
                uid_t uid = getuid();
                struct passwd *pw;
                pw = getpwuid(uid);
                sprintf(query, "insert into tweet values (default, '%s', '%s', now(), null, null)", pw->pw_name, tweet);
            }
            else
            {
                uid_t uid = getuid();
                struct passwd *pw;
                pw = getpwuid(uid);
                sprintf(query, "insert into tweet values (default, '%s', '%s', now(), %s, %s)", pw->pw_name, tweet, lat, lon);

            }

            res = PQexec(db, query);
            if (PQresultStatus(res) == PGRES_COMMAND_OK)
            {
                printf("INSERT succeeded\n");
            }
            else
            {
                printf("INSERT failed\n");
            }
            PQclear(res);
        }
        break;

        case 'A':
        case 'a':
        // Read all tweets
        {
            res = PQexec(db, "select * from tweet");
            int rows = PQntuples(res);
            printf("Getting %d rows\n", rows);

            for (int i = 0; i < rows; i++)
            {
                char *msg = PQgetvalue(res, i, 0);
                printf("%s\n", msg);
            }

            PQclear(res);
        }
        break;

我正在尝试将“推文”存储在数据库中。我可以让程序显示 INSERT SUCCESSFUL，但是当我在菜单中键入“a”查看所有推文时，只显示推文数量，但不显示消息或用户名等。我是否需要包含

create table tweet ( id serial primary key, username varchar(15), msg varchar(140), ts timestamp, lat float, lon float); 

在我的代码中的某个地方？

当输入“a”查看所有推文时，我还必须将其格式化为这样:

@username
tweet message
2014-4-20 18:53 (38.765, -121.240)

如何才能将数据库格式化为其他内容？

Answer 1

好吧，你的问题似乎是这样的:

char *PQgetvalue(const PGresult *res,
                       int row_number,
                       int column_number
                );

意思是，您只获得第一列(id)。您应该获得所有列(其中有 6 列)

int i, j, rows, cols; //define your variables up top
char *tweet, *lat, *lon, *choice, *msg, query[300];
struct passwd *pw;
uid_t uid;
while(1)
{
menu();

choice = readline("Your choice: ");
switch(choice[0])
{
    case 'P':
    case 'p':
    {
        // Post a tweet
        tweet = readline("Enter tweet: ");
        lat = readline("Enter latitude (press Enter to skip): ");
        lon = readline("Enter longitude (press Enter to skip): ");
        if (*lat == '\n' || *lon == '\n')
        {
            uid = getuid();
            pw = getpwuid(uid);
            sprintf(query, "insert into tweet values (default, '%s', '%s', now(), null, null)", pw->pw_name, tweet);
        }
        else
        {
            uid = getuid();
            pw = getpwuid(uid);
            sprintf(query, "insert into tweet values (default, '%s', '%s', now(), %s, %s)", pw->pw_name, tweet, lat, lon);

        }

        res = PQexec(db, query);
        if (PQresultStatus(res) == PGRES_COMMAND_OK)
        {
            printf("INSERT succeeded\n");
        }
        else
        {
            printf("INSERT failed\n");
        }
        PQclear(res);
    }
    break;

    case 'A':
    case 'a':
    // Read all tweets
    {
        res = PQexec(db, "select * from tweet");
        rows = PQntuples(res);
        cols = 5; //i dont know the PQL method of getting cols num
        printf("Getting %d rows\n", rows);

        for (i = 0; i < rows; i++)
        {
            for (j = 0; j < cols; j++)
            {
                msg = PQgetvalue(res, i, j);
                printf("%s\t", msg);
            }
            putchar(10);
        }

        PQclear(res);
    }
    break;

此代码存在一些主要问题(主要是内存泄漏)，但这应该可以完成您的任务。