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
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 );
You’ll want to use wpdb->update():
If codes are unique
UPDATE vouchers SET taken=1 WHERE code = (SELECT MIN(code) FROM vouchers WHERE taken=0)
You can try combining the UPDATE and SELECT statements, similarly to what is explained here.