MySQL SELECT, OUTPUT THEN DELETE

I’m using this query within a WP installation to output the lowest a code from a custom table. It successfully outputs the row with the lowest ID, and with a taken of ‘0’. So, that all works great – however, within the same function I want the outputted code to then have its taken SET to 1, instead of 0. I have tried so many variations but can’t quite crack it. Any ideas? Thanks.

function voucher_func() {
    global $wpdb;
    $vouchers = $wpdb->get_results("SELECT MIN(code) AS code FROM vouchers WHERE taken=0");
    foreach($vouchers as $voucher){
        return $voucher->code;
    }
}
add_shortcode( 'latest_voucher', 'voucher_func' );

Following on from the first bits of advice, i have got this far. This still outputs the right data, but doesnt update the ‘taken’ to 1

Read More
function voucher_func() {
global $wpdb;
$vouchers = $wpdb->get_results("SELECT MIN(code) AS code FROM vouchers WHERE taken=0");
foreach($vouchers as $voucher){
return $voucher->code;
}
}
$wpdb->update('vouchers', array('taken' => 1), array('code' => $voucher->code));

add_shortcode( 'latest_voucher', 'voucher_func' );

Here’s what i have now …

function voucher_func() {
global $wpdb;
$vouchers = $wpdb->get_results("SELECT MIN(code) AS code FROM vouchers WHERE taken=0");
foreach($vouchers as $voucher){
$wpdb->update('vouchers', array('taken' => 1), array('code' => $voucher->code));
return $voucher->code;
}
}

add_shortcode( 'latest_voucher', 'voucher_func' );

Here’s my final and working code:

function voucher_func() {
global $wpdb;
$vouchers = $wpdb->get_results("SELECT MIN(code) AS code FROM vouchers WHERE taken=0");
foreach($vouchers as $voucher){
return $voucher->code;
}
}

add_shortcode( 'latest_voucher', 'voucher_func' );

function action_wpcf7_mail_sent( $contact_form ) {
global $wpdb;
$vouchers1 = $wpdb->get_results("SELECT MIN(code) AS code FROM vouchers WHERE taken=0");
foreach($vouchers1 as $voucher1){
$wpdb->update('vouchers', array('taken' => 1), array('code' => $voucher1->code));
}
};

add_action( 'wpcf7_mail_sent', 'action_wpcf7_mail_sent', 10, 1 );

Related posts

3 comments

  1. If codes are unique
    UPDATE vouchers SET taken=1 WHERE code = (SELECT MIN(code) FROM vouchers WHERE taken=0)

Comments are closed.