MySQL错误502:"SELECT * FROM table WHERE uid = 1 ORDER BY ID DESC LIMIT 0，100"；分页不起作用 [英] MySQL ERROR 502: "SELECT * FROM table WHERE uid=1 ORDER BY id DESC LIMIT 0, 100"; Pagination not working

问题描述

我想从WHERE uid=1中显示ORDER BY id DESC并将分页设置为LIMIT.

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

在第一个代码中，它工作正常，我可以按ID DESC对行进行排序，但它为 ALL 用户选择订单:

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}" );

下面的这一行将获取uid = 1的所有行，但未排序.

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" );

以上两个查询均按预期工作，但我无法将它们合并为1.

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

我尝试了这2种方法和许多其他选择:

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}" );

• 1.查询给出:错误502

  2.查询给出:语法错误

  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
  

  我遵循以下步骤:答案，现在出现错误502或语法错误.

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

  如何将这2个查询合并为1个?

  How can I combine this 2 queries to 1?

  推荐答案

  我尝试过的1.查询具有正确的语法:

  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}" );
  

  但是该页面加载时间过长(15秒)，并返回了错误502 .

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

  问题: $items_per_page的值设置为高于找到的uid=1

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

  案例1:假设我们为uid=1有8行/记录，并运行此查询(LIMIT 10):

  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";
  

  • 它将失败，并显示错误502
  • It will fail with ERROR 502

  情况2:将分页设置为每页5行:

  Case 2: Set pagination to 5 rows per page:

  "SELECT * FROM wp_orders WHERE uid=1 ORDER BY id DESC LIMIT 0, 5"; 
  

  • 前5行的1.页面可以正常加载.
  • 2.页面将失败，并显示 ERROR 502 (不会显示第5行到第8行)
  • 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 )

  解决方案:我最终得到了这段代码，其中计算了$lastpage，$pagination ++:

  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>
  

  这篇关于MySQL错误502:"SELECT * FROM table WHERE uid = 1 ORDER BY ID DESC LIMIT 0，100"；分页不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！