25 January 2006 @ 06:51 am
Hmmm...
I think I found a way around the problem with the dual ORDER BY statements in the C code that caused problems in MySQL 5.x and in PostgreSQL as well.

In PostgreSQL, I need to have the row # when retrieving a result when using libpq (PHP automatically handles this). In MySQL, I don't... but I can use mysql_data_seek() to go to a specific row. So on rows I'm doing:
sprintf(buffer, "(SELECT * FROM xtable ORDER BY xfield DESC LIMIT mylimit) ORDER BY xfield ASC");

pthread_mutex_lock(&db_mutex);
results     = db_query(dbconn, buffer);
pthread_mutex_unlock(&db_mutex);
num_rows    = db_num_rows(results);

for (i = 0; i < num_rows; i++) {
    row     = db_fetch_row(results, i);
    // further processing
}
I can now just do:
sprintf(buffer, "SELECT * FROM xtable ORDER BY xfield DESC LIMIT mylimit");

pthread_mutex_lock(&db_mutex);
results     = db_query(dbconn, buffer);
pthread_mutex_unlock(&db_mutex);
num_rows    = db_num_rows(results);

for (i = num_rows - 1; i >= 0; i++) {
    row     = db_fetch_row(results, i);
    // further processing
}


Still need to work this all the way in (I'm looking at how master_poll_events operates and I'm trying to determine if I need to handle it the way I currently am or not).