CODEWITHSUNDEEP
mysqlwordpress
Evgeny Malkov
Evgeny Malkov

Reputation: 477

UNIX_TIMESTAMP MYSQL, can't get right query

Could someone review this query, i'm banging through wall all day. In my database i've got event with fields "_start_date_picker" equals to "
2018-04-30" and "_end_date_picker" equals to "2018-05-03"

I'm trying to get "post_id" of this with choosing date into this interval

SELECT * FROM wp_postmeta
WHERE (wp_postmeta.meta_key='_start_date_picker' AND 
UNIX_TIMESTAMP(STR_TO_DATE(wp_postmeta.meta_value, "%Y-%m-%d")) <= 
UNIX_TIMESTAMP(STR_TO_DATE('2018-05-01', "%Y-%m-%d"))) AND 
(wp_postmeta.meta_key='_end_date_picker'
AND UNIX_TIMESTAMP(STR_TO_DATE(wp_postmeta.meta_value, "%Y-%m-%d")) >= 
UNIX_TIMESTAMP(STR_TO_DATE('2018-05-01', "%Y-%m-%d")))

and nothing...

By the way, does anyone knows how to convert meta value before comparing in 'woocommerce_product_query' hook

    $meta_query= array(
    'relation' => 'OR',
        array(
            'key' => '_start_date_picker',
            'value' => $_REQUEST['event_start_date'],
            'compare' => '>=',
            'type' => 'DATE'
        ),
        array(
            'key' => '_end_date_picker',
            'value' => $_REQUEST['event_start_date'],
            'compare' => '<=',
            'type' => 'DATE'
        )
    );

I'm trying to filter my events by this event fields from $_REQUEST values..could anyone help...?

Upvotes: 0

Views: 40

Answers (2)

spencer7593
spencer7593

Reputation: 108420

I can guarantee that there is no row that satisfies both of these conditions:

      meta_key='_start_date_picker' 
  AND meta_key='_end_date_picker'

Think about it. If one of those conditions evaluates to TRUE, the other is going to evaluate to FALSE.

We need two references to the wp_postmeta table.

  FROM wp_postmeta sdp 
  JOIN wp_postmeta edp
    ON edp.post_id = sdp.post_id
 WHERE ( sdp.meta_key = '_start_date_picker' )
   AND ( sdp.meta_value conditions )
   AND ( edp.meta_key = '_end_date_picker' )
   AND ( edp.meta_value conditions )

Upvotes: 1

Kickstart
Kickstart

Reputation: 21523

From memory of wordpress, the 2 dates in the table are on 2 different rows.

So you need a query that joins the table against itself, getting the matching rows.

SELECT * 
FROM wp_postmeta a
INNER JOIN wp_postmeta b
ON a.meta_key = '_start_date_picker'
AND b.meta_key = '_end_date_picker'
WHERE UNIX_TIMESTAMP(STR_TO_DATE(a.meta_value, "%Y-%m-%d")) <= 
UNIX_TIMESTAMP(STR_TO_DATE('2018-05-01', "%Y-%m-%d"))) 
AND UNIX_TIMESTAMP(STR_TO_DATE(b.meta_value, "%Y-%m-%d")) >= 
UNIX_TIMESTAMP(STR_TO_DATE('2018-05-01', "%Y-%m-%d")))

Upvotes: 1

Related Questions