PHP Query posts and add unqueried posts to end

Okay, so through Wordpress, I've utimately ended up with a query string of the following:

"SELECT * FROM wp_z4azbl_posts 
LEFT JOIN wp_z4azbl_postmeta ON(
    wp_z4azbl_posts.ID = wp_z4azbl_postmeta.post_id) 
LEFT JOIN wp_z4azbl_term_relationships ON(
    wp_z4azbl_posts.ID = wp_z4azbl_term_relationships.object_id) 
LEFT JOIN wp_z4azbl_term_taxonomy ON(
    wp_z4azbl_term_relationships.term_taxonomy_id = wp_z4azbl_term_taxonomy.term_taxonomy_id) 
LEFT JOIN wp_z4azbl_terms ON(
    wp_z4azbl_terms.term_id = wp_z4azbl_term_taxonomy.term_id) 
WHERE wp_z4azbl_term_taxonomy.taxonomy = 'opportunitytype' 
AND wp_z4azbl_posts.post_status = 'publish' 
AND wp_z4azbl_posts.post_type = 'opportunities' 
AND wp_z4azbl_postmeta.meta_key = 'date_timestamp' 
GROUP BY wp_z4azbl_posts.ID 
ORDER BY wp_z4azbl_postmeta.meta_value ASC"

Ultimately this works. It grabs my posts with a timestamp custom field and sorts it in ascending order. However, I need it to also append posts that don't have a timestamp after all the posts that have a timestamp.

So right now, it showing something like this:

Event Title
January 1, 2011

Event Title 2
January 2, 2011

But I want it to append all posts without a timestamp:

Event Title
January 1, 2011

Event Title 2
January 2, 2011

Event Title Nostamp

Event Title Nostamp 2

How would I alter my query to get this to work?

EDIT

Sorry, I messed up on my original query. I actually need them to sort in ascending order with the non-timestamp posts still at the bottom.

Solutions

To include rows without a timestamp in the resultset, move meta_key = 'date_timestamp' from the where clause to on:

LEFT JOIN 
        wp_z4azbl_postmeta 
ON      wp_z4azbl_posts.ID = wp_z4azbl_postmeta.post_id
        and wp_z4azbl_postmeta.meta_key = 'date_timestamp'

Since you are sorting descending, the null values will already appear on the bottom. null is treated as the lowest possible value for purposes of sorting.

EDIT: To sort null differently, use a case:

order by
        case
        when wp_z4azbl_postmeta.meta_value is null then 2
        else 1
        end
,       wp_z4azbl_postmeta.meta_value
Tags: PHP / Mysql / SQL

Similar questions

How do I create a form inside my back end of widget and display it in a certain area for the end of my widget?
I have used the youtube video: (https://www.youtube.com/watch?v=OJdIUU1pjl4&t=1261s) to learn how to create my first custom widget. From here I have create a class with a front end display and a backend display (forms) I want to create a form to post the information, then gets saved to the database that later gets generated on the front end. I ...
which CSS query method is the best to use:container query.element query or media query?
I like to know which kind of queries I have to use because this is my first website I am developing and I am working with foundation grid system. I see at this moment you have containers queries which is more targeted to an element . also I see you have element queries and media queries. to have modern responsive website which one I have to use and...
Front end empty (i.e. nothing displays); Back end looks fine; What are possible causes?
My customer's site was working last night. Today nothing displays from the front end. Firebug gives as the contents of the home page. I logged in. The back end looks fine. The most recent post (added yesterday some time) is there. I looked (briefly) at index.php, wp-blogheader.php, wp-config.php. They all look right at first glance. No obvious prob...
Determining whether it's a AJAX call from front-end or from back-end
I am developing an admin-only plugin, which uses some ajax calls in its interface. The website itself also relies on ajax calls on its front-end, too. I'd like to be able to identify when the WP is loaded due to "simple backend call", "AJAX backend call" or "AJAX frontend call". The use case, in its simplest form, could be to load my plugin only on...
Using AJAX to generate front end / viewer end pages
I am currently trying to send certain data via POST method from code in the widget of my plugin to the theme's archive.php page. The code that contains the data for jquery/ajax is : The js code is: And the page where this code will be used is: The $args array will be used to create a new query in the WP Loop. I am fairly experienced with php yet AJ...
front end publishing not working on front-end page
I have a custom front end publishing form on my website, based on this article. It works perfectly when I put this code inside a page, but when on the front-end page (homepage), it doesn't work, and when I submit the form, I'm redirected to an archive.php page, the archive page from the post_category I've chosen in my form. anyone knows why it work...

Also ask

We use cookies to deliver the best possible experience on our website. By continuing to use this site, accepting or closing this box, you consent to our use of cookies. To learn more, visit our privacy policy.