英文:
WordPress SQL Query - meta_value date saved as string issues in php file - works in phpmyadmin
问题
$query = $wpdb->prepare("SELECT post_id
FROM {$wpdb->prefix}postmeta t1
WHERE meta_key = 'expiry_date'
AND STR_TO_DATE(meta_value, '%Y-%m-%d') BETWEEN NOW() AND NOW() + INTERVAL 6 MONTH
AND EXISTS (SELECT 1 FROM wp_postmeta t2
WHERE t2.meta_key = 'customer_email'
AND t2.meta_value = %s
AND t2.post_id = t1.post_id)
GROUP BY post_id", 'test@hotmail.co.uk');
$result = $wpdb->get_results($query, ARRAY_A);
$result returns empty because of this:
AND STR_TO_DATE(meta_value, '%Y-%m-%d') BETWEEN NOW() AND NOW() + INTERVAL 6 MONTH
meta_value column saves the date as a string hence I need to use STR_TO_DATE.
This query FULLY WORKS when I run it in phpmyadmin but not in my php wordpress file.
It only works inside my php file when I remove the line of code above.
英文:
$query = $wpdb->prepare("SELECT post_id
FROM {$wpdb->prefix}postmeta t1
WHERE meta_key = 'expiry_date'
AND STR_TO_DATE(meta_value, '%Y-%m-%d') BETWEEN NOW() AND NOW() + INTERVAL 6 MONTH
AND EXISTS (SELECT 1 FROM wp_postmeta t2
WHERE t2.meta_key = 'customer_email'
AND t2.meta_value = %s
AND t2.post_id = t1.post_id)
GROUP BY post_id", 'test@hotmail.co.uk');
$result = $wpdb->get_results( $query, ARRAY_A );
$result returns empty because of this:
AND STR_TO_DATE(meta_value, '%Y-%m-%d') BETWEEN NOW() AND NOW() + INTERVAL 6 MONTH
meta_value column saves the date as a string hence I need to use STR_TO_DATE.
This query FULLY WORKS when I run it in phpmyadmin but not in my php wordpress file.
It only works inside my php file when I remove the line of code above.
答案1
得分: 1
使用'%%Y-%%m-%%d'解决了这个问题。谢谢
英文:
@anyber Using '%%Y-%%m-%%d' solved this issue. Thanks
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论