WebTechGlobal

Oh yes sorry about that.

Not much to show…

$pageposts = $wpdb->get_results($q, OBJECT);

$pageposts gets dumped by var_dump() and global $wpdb is called before this. I have error display on and no MySQL errors. I have worked on this plugin for almost a year so I’m confident in how it stores the meta data plus its easy to check in database as each posts meta is grouped.

The two meta values I’m searching in my query are close together and I can confirm there are 9 posts that share the same meta keys and I should get 9 records returned.

Yes sure. I did try it already, on both on each of them and trying the query with variable and with date string.

Tried loads of variations. What you have told me allows it to work in MySQL Workbench only, which is so strange especially how you have it working!

They query is going through get_results() and the returned value is being dumped, which has always been NULL with the queries that work in Workbench.

Sorry no the actual query and I just tried it again is …

SELECT wposts.* FROM wp_posts wposts, wp_postmeta metaprojectcode, wp_postmeta metaupdated
WHERE wposts.ID = metaprojectcode.post_id
AND wposts.ID = metaupdated.post_id
AND (metaprojectcode.meta_key = 'csv2post_project_code' AND metaprojectcode.meta_value = 'proh4hyq2')
AND (metaupdated.meta_key = 'csv2post_last_update')
AND (metaupdated.meta_value < STR_TO_DATE('2012-11-05 12:43:39', GET_FORMAT(DATETIME, 'ISO')))
LIMIT 1

This returns a record in MySQL Workbench but not in PHP

I tried a few approaches because I’ve not worked with functions like this in MySQL before but I think this is the proper syntax and it still does not return records.

SELECT wposts.* FROM wp_posts wposts, wp_postmeta metaprojectcode, wp_postmeta metaupdated WHERE wposts.ID = metaprojectcode.post_id AND wposts.ID = metaupdated.post_id AND (metaprojectcode.meta_key = 'csv2post_project_code' AND metaprojectcode.meta_value = 'proh4hyq2') AND (metaprojectcode.meta_key = 'csv2post_last_update' AND STR_TO_DATE(metaupdated.meta_key, GET_FORMAT(DATETIME, 'ISO')) < STR_TO_DATE('2012-11-05 12:35:04', GET_FORMAT(DATETIME, 'ISO'))) LIMIT 1'

It has been a while since I spent so many hours on one problem, just like the Uni days lol

Was nice to wake up to such a good answer this morning thanks.

It make think about trying greater than on the same query and that returned a record. However it should not have so it must be a format issue.

The meta value stored is 2012-11-04 00:45:14.

Even in MySQL Workbench it is behaving as if 2012-11-04 00:45:14 is greater than 2012-11-04 23:41:31.

Oh and there is a correct to my original query however before making this post it was correct. I had started over again in trying to figure this issue out and typed it up wrong.

SELECT wposts.* FROM wp_posts wposts, wp_postmeta metaprojectcode, wp_postmeta metaupdated
    WHERE wposts.ID = metaprojectcode.post_id
    AND wposts.ID = metaupdated.post_id
    AND (metaprojectcode.meta_key = 'csv2post_project_code' AND metaprojectcode.meta_value = 'proh4hyq2')
    AND (metaupdated.meta_key = 'csv2post_last_update' AND metaupdated.meta_key > '2012-11-04 23:41:31')
    LIMIT 1

I will try your second method.