1

I want to show ORDER BY id DESC from: WHERE uid=1 and set LIMIT for pagination.

Here in first code it works fine and I can sort rows by id DESC but it selects orders for ALL users:

$rows = $wpdb->get_results( "SELECT * FROM wp_orders ORDER BY id DESC LIMIT ${offset}, ${items_per_page}" );

This line below will get all rows for uid=1 but the are not sorted.

$rows = $wpdb->get_results( "SELECT * FROM wp_orders WHERE uid=1 LIMIT $offset, $items_per_page" );

The both queries above are working as expected but I can not combine them to 1.

I tried this 2 and many other options:

$rows = $wpdb->get_results( "SELECT * FROM wp_orders WHERE uid=1 ORDER BY id DESC LIMIT ${offset}, ${items_per_page}" );

$rows = $wpdb->get_results( "SELECT * FROM wp_orders WHERE uid=1 ORDER BY id DESC AND LIMIT ${offset}, ${items_per_page}" );

  • The 1. query gives: ERROR 502

  • The 2. query gives: Syntax error

FastCGI sent in stderr: "PHP message: WordPress database error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND LIMIT 0, 100' at line 1 for query SELECT * FROM wp_orders WHERE uid=1 ORDER BY id DESC AND LIMIT 0, 100 made by require

I followed this: Answer and now got ERROR 502 or Syntax error.

How can I combine this 2 queries to 1?

Cyborg
  • 1,437
  • 19
  • 40
  • 1
    `"SELECT * FROM wp_orders WHERE uid=1 ORDER BY id DESC LIMIT ${offset}, ${items_per_page}"` should work what error are you getting? – Nick Apr 29 '19 at 01:07
  • @Nick I tired, it takes long time to load (15-20 seconds) and returns error: `failed (104: Connection reset by peer) while reading upstream` I see empty page. – Cyborg Apr 29 '19 at 01:15
  • try this: ```"SELECT * FROM wp_orders WHERE uid=1 ORDER BY id DESC LIMIT ". $offset.", " .$items_per_page``` But try to change to methods existents in the framework instead of building the query as string. – Angelo Mendes Apr 29 '19 at 01:24
  • This might help: https://stackoverflow.com/questions/4481388/why-does-mysql-higher-limit-offset-slow-the-query-down?rq=1 – Nick Apr 29 '19 at 01:26
  • @Cyborg you would put the `WHERE` in the subquery – Nick Apr 29 '19 at 01:42

2 Answers2

1

try:

$rows = $wpdb->get_results( "SELECT * FROM wp_orders WHERE uid=1 ORDER BY id DESC LIMIT $offset, $items_per_page");
cncal
  • 64
  • 6
  • This query takes 15-20 seconds and in the end I see empty page. Nginx error.log file says: `[error] 20015#20015: *27388 readv() failed (104: Connection reset by peer) while reading upstream` Can that be due to Nginx connection timeout? Cant understand why this query takes so long. – Cyborg Apr 29 '19 at 01:20
  • I tried query from phpMyAdmin and got ERROR 502. I figured out the problem was `$items_per_page` value was set higher than actual available records in MySQL. This is confusing, I thought `LIMIT` means MAX LIMIT.. But now I see if `LIMIT` value is set higher then actual available records in MySQL then page will keep loading and nginx will give ERROR 502 after 15 seconds by default connection timeout. Thanks for helping – Cyborg Apr 29 '19 at 03:15
  • @Cyborg Congrats on figuring it out. You really should self answer this question with that information, as this answer doesn't really solve the problem (it's exactly the same as the first query you tried), and other people may run into the same situation but not read these comments. – Nick Apr 29 '19 at 04:38
0

The 1. query I tried had the correct syntax:

$rows = $wpdb->get_results( "SELECT * FROM `wp_orders` WHERE uid='1' ORDER BY `id` DESC LIMIT ${offset}, ${items_per_page}" );

But the page took too long time (15 seconds) to load and returned ERROR 502.

The Problem: $items_per_page value was set to higher then total rows found for uid=1

Case 1: Assume we have 8 rows/records for uid=1 and run this query (LIMIT 10):

"SELECT * FROM wp_orders WHERE uid=1 ORDER BY id DESC LIMIT 0, 10";
  • It will fail with ERROR 502

Case 2: Set pagination to 5 rows per page:

"SELECT * FROM wp_orders WHERE uid=1 ORDER BY id DESC LIMIT 0, 5";
  • The 1. Page with first 5 rows will load fine.
  • The 2. Page will fail with ERROR 502 (will NOT show row 5 till 8 )

The Solution: I ended up with this code where I counted $lastpage, $pagination ++:

<?php
if( $uid = getcuruid() ) {
    echo '<div style="float:right;">Credits: ' . esc_attr( get_the_author_meta( 'credit', $uid ) ) . '</div><br class="clear">';
} else {
    die('<script>window.location.href = "https://www.example.com/login/";</script>');
}

function getcuruid() {
    if ( ! function_exists( 'wp_get_current_user' ) ) { return 0; }
    $user = wp_get_current_user();
    return ( isset( $user->ID ) ? (int) $user->ID : 0 );
}
?>

<div class="wrap">
    <?php
        $items_per_page = 50;
        $table_name = $wpdb->prefix . "orders";
        $pagination = $items_per_page;
        $total = $wpdb->get_var( "SELECT COUNT(1) FROM `$table_name` WHERE uid='$uid'" );
        $page = isset( $_GET['cpage'] ) ? abs( (int) $_GET['cpage'] ) : 1;  
        $offset = ( $page * $items_per_page ) - $items_per_page;

        if($items_per_page > $total) { $items_per_page = $total;}
        $lastpage = $page * $items_per_page;
        if( $lastpage > $total ) { $lastpage = $lastpage - $items_per_page; $items_per_page = $total - $lastpage; }

        $rows = $wpdb->get_results( "SELECT * FROM `$table_name` WHERE uid='$uid' ORDER BY `id` DESC LIMIT ${offset}, ${items_per_page}" );
    ?>

    <table class='wp-list-table'>
        <tr>
            <th class="manage-column">ID</th>
            <th class="manage-column">Result</th>
        </tr>        
        <?php foreach ($rows as $row) { ?>
            <tr>
                <td class="manage-column ss-list-width"><?php echo $row->id; ?></td>    
                <td class="manage-column ss-list-width"><?php echo $row->result; ?></td>
            </tr>
        <?php } ?>
    </table>

    <?php
        echo paginate_links( array(
            'base' => add_query_arg( 'cpage', '%#%' ),
            'format' => '&cpage=%#%',
            'prev_text' => __('&laquo;'),
            'next_text' => __('&raquo;'),
            'total' => ceil($total / $pagination),
            'current' => $page
        ));
    ?>
</div>
Cyborg
  • 1,437
  • 19
  • 40