CODEWITHSUNDEEP
phpmysql
Jack Torris
Jack Torris

Reputation: 804

Mysql Query to get event between two dates

I am trying to get the pots between two dates.

I am trying following query to fetch the data from mysql but it is now working.

$start = $_POST['evnt_date_from'];
$end =  $_POST['evnt_date_to'];
$querystr = " SELECT *  FROM `wp_postmeta`
WHERE`meta_key` = 'date' AND STR_TO_DATE(meta_value, '%d/%m/%Y') between '".date("Y-m-d", strtotime($start))."' AND '".date("Y-m-d", strtotime($end))."' ORDER BY STR_TO_DATE(meta_value, '%d/%m/%Y') ASC" ;

using this sql query to fetch the Posts between 2 dates and which are publish.

need help to fix mysql query.

any help would be appreciated.

Upvotes: 0

Views: 861

Answers (3)

Manoj Dhiman
Manoj Dhiman

Reputation: 5166

of you are using wordpress then you should use wordpress query like this this is just a example . change it according to your need

<?php
query_posts( array(
'post_type' => 'event',
'event-category' => 'bmx racing'
'posts_per_page' => '-1',
    'orderby' => 'date',
    'order' => 'ASC',
    'meta_query' => array(
            array(
            'key' => 'event-start-date',   //meta key here 
            'value' => array( 2012-05-01, 2012-05-31 ), // enter your dates here 
            'compare' => 'BETWEEN',
            'type' => 'date',
            ),
     ), ) );
  if ( have_posts() ) : while ( have_posts() ) : the_post();
?>

Upvotes: 0

Dr. Z
Dr. Z

Reputation: 237

Probably this is better :

$querystr = "SELECT *, STR_TO_DATE(meta_value, '%d/%m/%Y') dated FROM `wp_postmeta`
            WHERE`meta_key` = 'date' 
            AND meta_value between '".date("Y-m-d", strtotime($start))."' AND '".date("Y-m-d", strtotime($end))."' 
            ORDER BY dated ASC";

You are trying to compare date (format d/m/Y) with dates (format Y-m-d)

Upvotes: 1

Qarib Haider
Qarib Haider

Reputation: 4906

Try this:

$querystr = "SELECT *, STR_TO_DATE(meta_value, '%d/%m/%Y') dated FROM `wp_postmeta`
             WHERE `meta_key` = 'date' 
             AND dated between '".date("Y-m-d", strtotime($start))."' AND '".date("Y-m-d", strtotime($end))."' 
             ORDER BY dated ASC";

Upvotes: 0

Related Questions