I'm trying to use one mysql select query to count and increment a variable/non existing column only if orders_status not equal to pulled or pulling or else it's empty. I intend on using the count in a php script that executes a flag if 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";
Initial result:
orders_id | orders_status
34a | pulling
45a | pending
45u | pulled
36c | payment_due
Desired result returned
orders_id | orders_status | rownumber
34a | pulling |
45a | pending | 1
45u | pulled |
36c | payment_due | 2
You can start with this code. Just replace the necessary table name, column name, and 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 */
}
You can increment the value as you want using variables. Your query is a bit complicated, but here is the idea:
select . . .,
(case when order_status in ('pulled', 'pulling') then NULL
else @rn := @rn + 1
end) as rownumber
from . . . cross join
(select @rn := 0) vars;