Select Data From A Different Table

[frostiecuk]

Hi all

 

I'm having fun and games (well, not really fun) trying to get a SELECT statement to work on a wordpress website and wondered if anyone out there could help.

 

Basically I am trying to retrieve 10 posts that have an expiry date of today. Selecting 10 posts is fine, the problem start when I try to filter it based on date.

SELECT * FROM wp_posts WHERE post_type = "listing" LIMIT 10

 

However the expiry date is held in a different table;

Table: wp_postmeta

Field: listing_expiry_date

Date: is stored in a column called meta_value (in mm-dd-yyyy format)

 

I suspect the link between wp_posts and wp_postmeta takes place using post_id. Although I know what it is that needs to happen, I'm at a loss as to how to code it correctly;

SELECT * FROM wp_posts WHERE post_type = "listing" WHERE meta_value IN listing_expiry_date in wp_postmeta = TODAY LIMIT 10

 

Can anyone please advise how to go about this?

 

Many thanks

Edited November 29, 2012 by frostiecuk

[Barand]

Use a JOIN

 

SELECT *
FROM wp_posts
INNER JOIN wp_postmeta USING (post_id)
WHERE post_type = 'listing' AND listing_expiry_date = '11-29-2012'
LIMIT 10

 

edit

PS. your date format is pretty useless. You should use DATE type fields, format yyyy-mm-dd, which allows for comparisons and sorting and can be used by the inbuilt shedload of mysql datetime functions

Edited November 29, 2012 by Barand

[frostiecuk]

Hi Barand

Firstly many thanks. I got an error "Unknown column 'post_id' in 'from clause' but based your suggestion have concocted the following;

SELECT *
FROM wp_posts
INNER JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id
WHERE post_type = 'listing'
AND post_status = 'publish'
AND meta_value = '11-29-2012'
LIMIT 10

 

This works a treat, however now I need the date to be dynamic (i.e. not an actual date, but @Today). I've tried GETDATE TODAY AND CURDATE but nothing seems to return the correct results. Any ideas on this last tweak?

 

Thanks again.

[Barand]

you'll need to reformat CURDATE()

 

DATE_FORMAT(CURDATE(), '%m-%d-%Y')

[frostiecuk]

I cracked it and was coming back here to let you know. I've used the following;

DATE_FORMAT(NOW(),'%m-%d-%Y')

Is there any reason why I should use NOW over CURDATE or the other way round?

[Barand]

NOW() is the current date and time

CURDATE() is the current date.

 

If I'm only concerned with the date, I use CURDATE()

[frostiecuk]

Thanks once again. Changed it to CURDATE and it works an absolute treat.

[fenway]

Of course, you should really be storing dates as DATEs.