Custom mysql search for custom post, tax, meta

<?php
/**
 * Template Name: Race Listing
 *
 * This is the template that displays all pages by default.
 * Please note that this is the WordPress construct of pages
 * and that other 'pages' on your WordPress site will use a
 * different template.
 *
 * @package WordPress
 * @subpackage Twenty_Ten
 * @since Twenty Ten 1.0
 */

get_header();
global $spages;
$racelist_page = get_permalink($spages->racelist);
?>

<div id="container">
    <div id="content" role="main">
        <h1 class="entry-title">Latest Race Entries</h1><br/>
        <?php
            $paged = (get_query_var('paged')) ? get_query_var('paged') : 1;
            $paged = absint($paged);

            $racetype       = (isset ($_REQUEST['racetype']))? $_REQUEST['racetype']:'-1';
            $racedistance   = (isset ($_REQUEST['racedistnce']))? $_REQUEST['racedistance']:'-1';
            $racelocation   = (isset ($_REQUEST['racelocation']))? $_REQUEST['racelocation']:'-1';

            $zipcode        = (isset ($_REQUEST['zipcode']))? sanitize_text_field($_REQUEST['zipcode']):'';
            $distance       = (isset ($_REQUEST['distance']))? sanitize_text_field($_REQUEST['distance']):1; //default 1 mile
            $distance       = absint($distance);

            $showresult = true;
            $zipexists  = false;
            $search_error = '';
            if( zipcodes_exists('zipcode', $zipcode ) != false) {
                 $latlng        = zipcodes_latlng($zipcode);
                 $originlat     = $latlng->latitude;
                 $originlng     = $latlng->longitude;

                 $longitude = $originlng;
                 $latitude =  $originlat;
                 $miles = $distance;
                 //The rad2deg() function converts a radian number to its degree
                 //deg2rad — Converts the number in degrees to the radian equivalent
                 //http://blog.fedecarg.com/2009/02/08/geo-proximity-search-the-haversine-equation/
                 $lng1 = $longitude - $miles / abs(cos(deg2rad($latitude)) * 69);
                 $lng2 = $longitude + $miles / abs(cos(deg2rad($latitude)) * 69);

                 $lat1 = $latitude - ($miles / 69);
                 $lat2 = $latitude + ($miles / 69);
                 $zipexists = true;

            }

            $searchtext     = (isset ($_REQUEST['searchtext']))? sanitize_text_field($_REQUEST['searchtext']):'';
            $startdate      = (isset ($_REQUEST['startdate']))? sanitize_text_field($_REQUEST['startdate']):'';
            //let's convert us date format to date format
            if($startdate != ''){ $startdate = convert_ukdatetodate($startdate); }

            $order          = (isset ($_REQUEST['order']))? sanitize_text_field($_REQUEST['order']):'DESC';
            $orderby        = (isset ($_REQUEST['orderby']))? sanitize_text_field($_REQUEST['orderby']):'date';
            if($zipexists == false && $orderby == 'geodistance'){ $orderby = 'date';}

            $perpage        = (isset ($_REQUEST['posts_per_page']))? sanitize_text_field($_REQUEST['posts_per_page']):10;
            $perpage        = intval($perpage);
            if($perpage == 0) $perpage = 10;

            //sql building
            $rawsql = "SELECT SQL_CALC_FOUND_ROWS  $wpdb->posts.* ";
            $i = $j = $k = 0;
            $metainnerjoin = '';
            if($zipexists){
                $i++;
                $j = $i;
                $i++;
                $k = $i;
                $rawsql .= " , 3958 * 2 * ASIN(SQRT(  POWER(SIN(($originlat - mt$j.meta_value) * pi()/180 / 2), 2) + COS($originlat * pi()/180) *  COS(mt$j.meta_value * pi()/180) *  POWER(SIN(($originlng -mt$k.meta_value) * pi()/180 / 2), 2)  )) as distance ";
                $metainnerjoin = "   INNER JOIN $wpdb->postmeta AS mt$j ON ($wpdb->posts.ID = mt$j.post_id)";
                $metainnerjoin .= "   INNER JOIN $wpdb->postmeta AS mt$k ON ($wpdb->posts.ID = mt$k.post_id)";

            }

            $rawsql .= " FROM $wpdb->posts";

            //tax
            if(!empty($racetype) || !empty($racedistance) || !empty($racelocation)){
                $rawsql .= " INNER JOIN $wpdb->term_relationships ON ($wpdb->posts.ID = $wpdb->term_relationships.object_id)";
            }

            $rawsql .= " INNER JOIN $wpdb->postmeta ON ($wpdb->posts.ID = $wpdb->postmeta.post_id)";
            //meta queries and counts

            if($zipexists){
                $rawsql = $rawsql.$metainnerjoin;
            }
            //custom meta fields
            /*
            if($jnchild > 0){
                $i++;
                $rawsql .= " INNER JOIN $wpdb->postmeta AS mt$i ON ($wpdb->posts.ID = mt$i.post_id)";
            }


            if(intval($jrate1) > 0){
                $i++;
                $rawsql .= " INNER JOIN $wpdb->postmeta AS mt$i ON ($wpdb->posts.ID = mt$i.post_id)";
            }


            if(intval($jrate2) > 0 && intval($jrate1) > 0 && $jrate2 >= $jrate1){
                $i++;
                $rawsql .= " INNER JOIN $wpdb->postmeta AS mt$i ON ($wpdb->posts.ID = mt$i.post_id)";
            }
            if($jrateb != ''){
                $i++;
                $rawsql .= " INNER JOIN $wpdb->postmeta AS mt$i ON ($wpdb->posts.ID = mt$i.post_id)";
            }
            if($jstartdate != ''){
                $i++;
                $rawsql .= " INNER JOIN $wpdb->postmeta AS mt$i ON ($wpdb->posts.ID = mt$i.post_id)";
            }
            */
            if($startdate != ''){ //meta value
                $i++;
                $rawsql .= " INNER JOIN $wpdb->postmeta AS mt$i ON ($wpdb->posts.ID = mt$i.post_id)";
            }

            $i++;
            $rawsql .= " INNER JOIN $wpdb->postmeta AS mt$i ON ($wpdb->posts.ID = mt$i.post_id)";


            $rawsql .= " WHERE 1=1"; // default true condition

            if($racetype > 0){ //tax
                //$rawsql .= " AND ( $wpdb->term_relationships.term_taxonomy_id IN (".implode(",", $racetype).") )"; //for multiple selection
                $rawsql .= " AND ( $wpdb->term_relationships.term_taxonomy_id = ".$racetype." )"; //for single selection
            }
            if($racedistance > 0){//tax
                //$rawsql .= " AND ( $wpdb->term_relationships.term_taxonomy_id IN (".implode(",", $racedistance).") )";
                $rawsql .= " AND ( $wpdb->term_relationships.term_taxonomy_id = ".$racedistance." )";
            }
            if($racelocation > 0){//tax
                //$rawsql .= " AND ( $wpdb->term_relationships.term_taxonomy_id IN (".implode(",", $racelocation).") )";
                $rawsql .= " AND ( $wpdb->term_relationships.term_taxonomy_id =". $racelocation." )";
            }
            //var_dump($searchtext);
            if($searchtext != '' ){
                //var_dump($searchtext);
                $rawsql .= " AND ((($wpdb->posts.post_title LIKE '%$searchtext%') OR ($wpdb->posts.post_content LIKE '%$searchtext%')))";
            }
            $rawsql .= " AND $wpdb->posts.post_type = 'race' AND ($wpdb->posts.post_status = 'publish')";
            //var_dump($orderby);
            $metakeysort = false;
            /*
            if($orderby == 'jrate1'){
                $metakeysort = true;
                $rawsql .= " AND ($wpdb->postmeta.meta_key = 'jrate1'";
            }
            */
            if($orderby == 'startdate'){ //that means sort by meta key
                $metakeysort = true;
                $rawsql .= " AND ($wpdb->postmeta.meta_key = 'startdate'";
            }

            $i = 0;
            if($zipexists){
                $i++;
                $i++;
                $rawsql .= " AND  (mt$j.meta_key = 'latitude' AND (mt$j.meta_value BETWEEN $lat1 AND $lat2 ))";
                $rawsql .= " AND  (mt$k.meta_key = 'longitude' AND (mt$k.meta_value BETWEEN $lng1 AND $lng2))";
            }
            /*
            if($jnchild > 0){
                $i++;
                $rawsql .= " AND  (mt$i.meta_key = 'jnchild' AND CAST(mt$i.meta_value AS SIGNED) <= '$jnchild')";
            }
            if(intval($jrate1) > 0){
                $i++;
                $rawsql .= " AND  (mt$i.meta_key = 'jrate1' AND CAST(mt$i.meta_value AS SIGNED) >= '$jrate1')";
            }
            if(intval($jrate2) > 0 && intval($jrate1) > 0 && $jrate2 >= $jrate1){
                $i++;
                $rawsql .= " AND  (mt$i.meta_key = 'jrate2' AND CAST(mt$i.meta_value AS SIGNED) <= '$jrate2')";
            }
            if($jrateb != ''){
                $i++;
                $rawsql .= " AND  (mt$i.meta_key = 'jrateb' AND CAST(mt$i.meta_value AS CHAR) = '$jrateb')";
            }
            if($jstartdate != ''){
                $i++;
                $rawsql .= " AND  (mt$i.meta_key = 'jstartdate' AND CAST(mt$i.meta_value AS DATE) >= '$jstartdate')";
            }
            */

            if($startdate != ''){  //if sort by meta value then compare and cast if need and date should be yyyy-mm-dd format
                $i++;

                $rawsql .= " AND  (mt$i.meta_key = 'jstartdate' AND CAST(mt$i.meta_value AS DATE) >= '$startdate')";
            }
            $i++;
            //$rawsql .= " AND  (mt$i.meta_key = 'appstatus' AND CAST(mt$i.meta_value AS CHAR) = 'open')";

            if($metakeysort){
                $rawsql .= " )";
            }
            $rawsql .= " GROUP BY $wpdb->posts.ID ORDER BY";
            if($orderby == 'geodistance' && $zipexists == false){
                $orderby = 'date';
            }
            if($orderby == 'date'){
                $rawsql .= " $wpdb->posts.post_date";
            }
            /*
            else if($orderby == 'jrate1'){
                $rawsql .= " $wpdb->postmeta.meta_value";
            }
            */
            else if($orderby == 'startdate'){
                $rawsql .= " $wpdb->postmeta.meta_value";
            }
            else if($orderby == 'geodistance'){
                $rawsql .= " distance";
                //$orderby = ''
            }

            $ofs = ($paged - 1) * $perpage;

            $rawsql .= " $order LIMIT $ofs, $perpage;";


            /*
            $query_args     = array('post_type' => 'race','paged' => $paged, 'post_status' => 'publish', 'posts_per_page' => 10, 'order' => $order, 'orderby' => $orderby);
            $query_args['meta_key'] = 'startdate';
            $query_args['orderby']  = 'meta_value';
            $meta_query     = array();
            //$tax_query      = array();
            $meta_query[] =  array(
                    'key' => 'startdate',
                    'value' => $date,
                    'compare' => '=',
                    'type' => 'DATE',
            );
            query_posts($query_args);
            */
            //var_dump($rawsql);
            $results = $wpdb->get_results($rawsql);
            $found_rows = $wpdb->get_col("SELECT FOUND_ROWS();");
            $found_rows = $found_rows[0];
            //var_dump($found_rows);
         ?>
            <form name="jobsearchform" id="jobsearchform" method="get" action="<?php echo $racelist_page; ?>">
            <div class="filterbox">
                <div class="filterboxl">
                    <p><label for"racetype">Sport:</label> <?php echo cb_wp_dropdown_categories(array('echo' =>false, 'taxonomy' => 'racetype', 'id' => 'filterracetype',  'id' => 'racetype', 'name' => 'racetype','show_option_none' => 'Select Race', 'show_count' => 0,'hierarchical' => 1, 'hide_empty' => 0, 'selected' => $racetype)); ?></p>
                    <p><label for"racedistance">Distance:</label> <?php echo cb_wp_dropdown_categories(array('echo' =>false, 'taxonomy' => 'racedistance', 'id' => 'filterracedistance', 'id' => 'racedistance', 'name' => 'racedistance','show_option_none' => 'Select Distance', 'show_count' => 0,'hierarchical' => 1, 'hide_empty' => 0, 'selected' => $racedistance)); ?></p>
                    <p><label for"racelocation">Sted:</label> <?php echo cb_wp_dropdown_categories(array('echo' =>false, 'taxonomy' => 'racelocation', 'id' => 'filterracelocation', 'id' => 'racelocation', 'name' => 'racelocation','show_option_none' => 'Select Location', 'show_count' => 0,'hierarchical' => 1, 'hide_empty' => 0, 'selected' => $racelocation)); ?></p>
                    <p><label for="distance">Avstand:</label>
                        <select id="distance" name="distance" class="input">
                            <option value="1" <?php selected('1',$distance); ?>>1 Mile</option>
                            <option value="3" <?php selected('3',$distance); ?>>2 Miles</option>
                            <option value="5" <?php selected('5',$distance); ?>>5 Miles</option>
                            <option value="10" <?php selected('10',$distance); ?>>10 Miles</option>
                            <option value="20" <?php selected('20',$distance); ?>>20 Miles</option>
                            <option value="30" <?php selected('30',$distance); ?>>30 Miles</option>
                            <option value="40" <?php selected('40',$distance); ?>>40 Miles</option>
                            <option value="50" <?php selected('50',$distance); ?>>50 Miles</option>
                            <option value="100" <?php selected('100',$distance); ?>>100 Miles</option>
                        </select>
                    </p>

                </div>
                <div class="filterboxr">
                    <p><label>Søkeord:</label><input type="text" value="<?php echo $searchtext; ?>" name="searchtext" /></p>
                    <p><label>Post Nummer:</label><input type="text"  value="<?php echo $zipcode; ?>" name="zipcode" /></p>
                    <p><label>Dato:</label><input type="text" value="<?php echo $startdate; ?>" name="startdate" id="startdate" /></p>
                    <p><label for="orderby" class="orderl">Order By:</label>
                    <select id="orderby" name="orderby" class="input orderselectbox  commonselect">
                        <option value="date" <?php selected('date',$orderby); ?>>Event Posting Date</option>
                        <option value="startdate" <?php selected('startdate',$orderby); ?>>Event Start Date</option>
                        <option value="geodistance" <?php selected('geodistance',$orderby); ?>>Distance</option>
                    </select>
                    </p>
                    <input type="submit" name="searchrace" id="wp-submitsrace" class="button-primary" value="Search" tabindex="100" />
                </div>
                <div class="clear"></div>
                <!--p class="minifilter">
                    <label for="perpage" class="orderl">Show Per Page:</label>
                    <select id="perpage" name="posts_per_page" class="input orderselectbox commonselect">
                        <option value="1" <?php selected('1',$perpage); ?>>1 item</option>
                        <option value="10" <?php selected('10',$perpage); ?>>10 items</option>
                        <option value="20" <?php selected('20',$perpage); ?>>20 items</option>
                        <option value="30" <?php selected('30',$perpage); ?>>30 items</option>
                        <option value="40" <?php selected('40',$perpage); ?>>40 items</option>
                        <option value="50" <?php selected('50',$perpage); ?>>50 items</option>
                    </select>
                    <label for="order" class="orderl">Order:</label>
                    <select id="order" name="order" class="input orderselectbox  commonselect">
                        <option value="DESC" <?php selected('DESC',$order); ?>>Descending</option>
                        <option value="ASC" <?php selected('ASC',$order); ?>>Ascending</option>
                    </select>
                </p-->

                <!--script type="text/javascript">
                        var perpage_link = "<?php echo generate_posts_per_page_link(); ?>";
                        //for video tagging
                        var dropdownperpage = document.getElementById("perpage");
                        function onPerpageChange() {
                                perpagevalue = dropdownperpage.options[dropdownperpage.selectedIndex].value;
                                //if ( tagvalue > 0 || tagvalue == -1 ) {
                                        location.href = perpage_link+perpagevalue;
                                //}
                        }
                        dropdownperpage.onchange = onPerpageChange;

                        var orderby_link = "<?php echo generate_posts_orderby_link(); ?>";

                        //for video tagging
                        var dropdownorderby = document.getElementById("orderby");
                        function onOrderbyChange() {
                                orderbyvalue = dropdownorderby.options[dropdownorderby.selectedIndex].value;
                                //if ( tagvalue > 0 || tagvalue == -1 ) {
                                        location.href = orderby_link+orderbyvalue;
                                //}
                        }
                        dropdownorderby.onchange = onOrderbyChange;

                        var order_link = "<?php echo generate_posts_order_link(); ?>";

                        //for video tagging
                        var dropdownorder = document.getElementById("order");
                        function onOrderChange() {
                                ordervalue = dropdownorder.options[dropdownorder.selectedIndex].value;
                                //if ( tagvalue > 0 || tagvalue == -1 ) {
                                        location.href = order_link+ordervalue;
                                //}
                        }
                        dropdownorder.onchange = onOrderChange;
                </script-->

                <div class="clear"></div>
                <script type="text/javascript">
                    jQuery(window).load(function() {
                        jQuery("#startdate").datepick({dateFormat: 'dd-mm-yyyy'});
                    });
                </script>
            </div>
            </form>
            <div class="clear"></div>
             <?php if ( ! $results ) : ?>
            <div id="post-0" class="post error404 not-found">
                <div class="entry-content">
                    <p><?php _e( 'No race found, please change the search criteria.', 'twentyten' ); ?></p>
                        <?php //get_search_form(); ?>
                </div><!-- .entry-content -->
            </div><!-- #post-0 -->
            <?php endif; ?>

        <?php
        //while ( have_posts() ) : the_post();
        $i =  0;
        foreach($results as $post){
            $i++;
            setup_postdata($post);
            $raceid         = $post->ID;
            $zipcode        = get_post_meta($raceid, 'zipcode', true);
            $startdate      = get_post_meta($raceid, 'startdate', true);
            $startdate_uk   = convert_datetoukdate($startdate);
            $startdate_r    = cb_relativeTime($startdate);
            $distance = '';
            $distance = $post->distance;
            if($zipexists && $distance > 0){
                $distance = ','.ceil($distance).' miles';
            }


        ?>
            <div id="post-<?php the_ID(); ?>" <?php post_class('productlisting'); ?>>
                <h2 class="entry-title"><a href="<?php the_permalink(); ?>" title="<?php printf( esc_attr__( 'Permalink to %s', 'twentyten' ), the_title_attribute( 'echo=0' ) ); ?>" rel="bookmark"><?php the_title(); ?></a></h2>

                <div class="entry-summary">
                    <!--img class="racethumb" src="<?php echo bloginfo('template_directory'); ?>/images/racethumb.jpg" alt="racethumb" /-->
                    <?php echo get_the_post_thumbnail( $raceid, 'post-thumbnail', array('class' => 'racethumb')); ?>
                    <?php the_excerpt(); ?>
                    <div class="clear"></div>
                    <p class="rssummary">Zipcode: <?php echo $zipcode; ?> <?php echo $distance; ?></p>
                    <p class="rssummary">Start Date: <?php echo $startdate_uk; ?>, <?php echo $startdate_r; ?></p>
                </div><!-- .entry-summary -->
                <div class="entry-utility">
                        <?php
                            //$id = $post->ID;
                            //$id = 0, $taxonomy, $before = '', $sep = '', $after = ''
                        ?>
                        <?php
                            $tags_list = get_the_term_list($id,'racetype', '', ', ' );
                            if ( $tags_list ):
                        ?>
                                <span class="tag-links">
                                        <?php printf( __( '<span class="%1$s">Race Type</span> %2$s', 'twentyten' ), 'entry-utility-prep entry-utility-prep-tag-links', $tags_list ); ?>
                                </span>
                                <span class="meta-sep">|</span>
                        <?php endif; ?>
                        <?php
                            $tags_list = get_the_term_list($id,'racedistance', '', ', ' );
                            if ( $tags_list ):
                        ?>
                                <span class="tag-links">
                                        <?php printf( __( '<span class="%1$s">Race Distance</span> %2$s', 'twentyten' ), 'entry-utility-prep entry-utility-prep-tag-links', $tags_list ); ?>
                                </span>
                                <span class="meta-sep">|</span>
                        <?php endif; ?>
                        <?php
                            $tags_list = get_the_term_list($id,'racelocation', '', ', ' );
                            if ( $tags_list ):
                        ?>
                                <span class="tag-links">
                                        <?php printf( __( '<span class="%1$s">Region</span> %2$s', 'twentyten' ), 'entry-utility-prep entry-utility-prep-tag-links', $tags_list ); ?>
                                </span>
                                <span class="meta-sep">|</span>
                        <?php endif; ?>
                        <?php
                            $tags_list = get_the_term_list($id,'awards', '', ', ' );
                            if ( $tags_list ):
                        ?>
                                <span class="tag-links">
                                        <?php printf( __( '<span class="%1$s">Awards & Prizes</span> %2$s', 'twentyten' ), 'entry-utility-prep entry-utility-prep-tag-links', $tags_list ); ?>
                                </span>
                        <?php endif; ?>
                </div><!-- .entry-utility -->
            </div><!-- #post-## -->
        <?php
        //endwhile;
        }
        ?>
        <?php //if(function_exists('wp_paginate')) {wp_paginate();} ?>
        <?php
            if(function_exists('cb_paginate')) { cb_paginate($found_rows,$perpage, $paged); }
            //wp_reset_query();
        ?>
        </div><!-- #content -->
</div><!-- #container -->

<?php get_sidebar(); ?>
<?php get_footer(); ?>