Site icon Win Bet Asia

Woocommerce order status update from select from frontend

I have displayed wc orders in frontend with SQL Select. Select looks like it:

 $res = $wpdb->get_results("SELECT
    p.ID as order_id,
    p.post_date as dater,
    p.post_excerpt as komentaras,
    oimi.status as statusas,
    oim.meta_value as qty,
    max( CASE WHEN pm.meta_key = '_billing_email' and p.ID = pm.post_id THEN pm.meta_value END ) as billing_email,
    max( CASE WHEN pm.meta_key = '_billing_first_name' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_first_name,
    max( CASE WHEN pm.meta_key = '_billing_last_name' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_last_name,
    max( CASE WHEN pm.meta_key = '_shipping_address_index' and p.ID = pm.post_id THEN pm.meta_value END ) as shipping_address_index,
    max( CASE WHEN pm.meta_key = '_order_total' and p.ID = pm.post_id THEN pm.meta_value END ) as order_total,                 
    max( CASE WHEN pm.meta_key = 'additional_' and p.ID = pm.post_id THEN pm.meta_value END ) as atsiemimo_vieta,
    max( CASE WHEN pm.meta_key = '_payment_method_title' and p.ID = pm.post_id THEN pm.meta_value END ) as payment_method_title,
    ( select group_concat(order_item_name separator ' <hr> ' ) from wp_woocommerce_order_items where order_id = p.ID and order_item_type = 'line_item' ) as order_items,
    ( select order_item_name  from wp_woocommerce_order_items where order_id = p.ID and order_item_type = 'shipping' ) as order_shipping       
    
FROM
    wp_posts p 
    join wp_postmeta pm on p.ID = pm.post_id
    join wp_woocommerce_order_items oi on p.ID = oi.order_id
    right join wp_woocommerce_order_itemmeta oim on oi.order_item_id = oim.order_item_id
    join wp_wc_order_stats oimi on pm.post_id = oimi.order_id
WHERE
    post_type = 'shop_order' AND
    oim.meta_key = '_qty'
    
group by
    p.ID DESC");

And display looks like this

    echo '<table id="customers"><tr>';
echo '<th>ID order</th>';    
echo '<th>Items</th>';
echo '<th>Qty</th>';
echo '<th>Info</th>';
echo '<th>Total</th>';
echo '<th>Status</th>';
echo '<th>Info customers</th>';
echo '<th>Action</th>';
echo '<th>Date</th>';
echo '</tr>';
// Loop through each order post object
foreach( $res as $res ){
    echo '<tr>';
    echo '<td>' . $res->order_id . '<br>' . $res->order_item_id . '</td>';
    echo '<td>' . $res->order_items . '</td>';   
    echo '<td>' . $res->qty . '</td>';   
    echo '<td>' . $res->payment_method_title . '<br>' . $res->order_shipping . '<br>' . $res->atsiemimo_vieta . '</td>';
    echo '<td>' . $res->order_total . '€ </td>';
    
    if($res->statusas == 'wc-on-hold'){ 
         echo '<td> Sustabdytas </td>';
    } else{
        echo '<td>' . $res->statusas . '</td>';    
    };    
    
    echo '<td>' . $res->_billing_first_name.' '.$res->_billing_last_name . ' '. $res->billing_email . '<br><b>Pristatymo adresas: </b>'.$res->shipping_address_index .'<br><b> Komentaras : </b>'. $res->komentaras .'</td>';  
    
    
    echo '<td>   <form method="post">
                    <select name="statusass">
                        <option value="wc-on-hold">On Hold</option>
                        <option value="wc-processing">Processing</option>
                        <option value="">Cancel</option>
                        <option value="">Completed</option>
                    </select>
                  <br><br>
                  <input type="submit" value="Submit">
                  </form>
        </td>';
    echo '<td>' . $res->dater . '</td>';
    echo '</tr>';
}

echo '</table>';

U see this is select with status options and i wanna make what if u select option and press button it update status in database. I was trying to make simple like this

<?php
if(isset($_POST["submit"])){
    $Status=$_POST["statusass"];
    $stmt = "UPDATE wp_wc_order_stats SET status='$Status' WHERE order_id=177386"; 
        echo "Pakeista";
    }

?>

But it is not working. How i can take selected option and order_id from specific tables row where it was selected? Its a litle bit hard to me with SQL and i really lost in it like for month, please help

Exit mobile version