initialize，然后递增记录集的行号if - initialize then increment the rownumber of a record set if

initialize then increment the rownumber of a record set if

本文关键字：if 记录然后 initialize | 更新日期: 2023-09-27

只有当orders_status不等于pulled或pulling，或者是empty时，我才尝试使用一个mysql select query来计数和递增变量/不存在的列。我打算在php脚本中使用计数，如果row_number=400

$orders_query_raw = "SELECT
                    o.orders_id,
                    o.customers_name,
                    o.payment_method,
                    o.date_purchased,
                    o.delivery_date,
                    o.delivery_time_slotid,
                    o.last_modified,
                    o.currency,
                    o.currency_value,
                    s.orders_status_name,
                    ot.text as order_total
                FROM " . TABLE_ORDERS . " o
                LEFT JOIN " . TABLE_ORDERS_TOTAL . " ot ON (o.orders_id = ot.orders_id), " . TABLE_ORDERS_STATUS . " s
                WHERE o.orders_status = s.orders_status_id
                  AND s.language_id = '" . (int)$languages_id . "'
                  AND s.orders_status_id = '" . (int)$status . "'
                  AND ot.class = 'ot_total'
                ORDER BY o.orders_id DESC";

初始结果：

orders_id | orders_status 
     34a  |  pulling          
     45a  |  pending      
     45u  |  pulled       
     36c  |  payment_due

返回所需结果

orders_id | orders_status | rownumber
     34a  |  pulling      |   
     45a  |  pending      |   1
     45u  |  pulled       |   
     36c  |  payment_due  |   2

您可以使用变量根据需要增加值。您的查询有点复杂，但想法如下：

select . . .,
       (case when order_status in ('pulled', 'pulling') then NULL
             else @rn := @rn + 1
        end) as rownumber
from . . . cross join
     (select @rn := 0) vars;

您可以从这段代码开始。只需替换必要的table名称、column名称和variables:

$result = mysqli_query($YourDBconnection,"SELECT * FROM yourTable WHERE orders_status<>'pulling' AND orders_status<>'pulled' ORDER BY orders_id");
$count = 1; /* SET A COUNTER */
while($row = mysqli_fetch_array($result)){
  $orderid = $row["orders_id"];
  mysqli_query($YourDBconnection,"UPDATE yourTable SET rownumber='$count' WHERE orders_id='$orderid'"); /* UPDATE THE TABLE */
  $count=$count+1; /* INCREMENT YOUR COUNTER */
}