MYSQL - Multiple Select and LIKE Method

Question

What i want is: Get posts with date greater then 2010-03-02 and with the meta_value 'something' + like '2010-'

because there are other values like 239048192304 or 293811743

$query = "SELECT DISTINCT wp_postmeta.meta_key, wp_postmeta.meta_value, wp_posts.ID, wp_posts.guid, wp_postmeta.post_id, wp_posts.post_title
FROM wp_postmeta
INNER JOIN wp_posts
ON wp_postmeta.post_id=wp_posts.ID
WHERE wp_postmeta.meta_value >='2010-03-02'
AND wp_postmeta.meta_value = 'something'
AND wp_postmeta.meta_value LIKE '2010-'
ORDER BY wp_postmeta.meta_value ASC
LIMIT 0,10";

can you help me out please? thank you!

Update2:

table wp_postmeta

post_id | meta_value    
5       | 2010-12-30    
5       | Berlin

3       | 2010-12-29
3       | Paris

2       | 2009-12-29
2       | Paris

14      | 12232456521
14      | Berlin

Output:

2010-12-30 Berlin ID 5
2010-12-29 Paris  ID 3

Answer 1

Those statements contradict themselves. First you are asking for it to be >= a date. So is meta_value a date? Then you say it must be equaled to 'something', so now it is a string and not a date. Finally you say have it be like 2010-, so now we are back to a string or a date, but no wild card % in the like so you are basically saying it has to be equaled to 2010- as well.

• What is the value stored in meta_value?
• Are you sure you do not mean to query multiple fields?

UPDATE

Based on the new information, I think this is what you want:

$query = "SELECT DISTINCT wp_postmeta.meta_key, wp_postmeta.meta_value, wp_posts.ID, wp_posts.guid, wp_postmeta.post_id, wp_posts.post_title
FROM wp_postmeta
INNER JOIN wp_posts
ON wp_postmeta.post_id=wp_posts.ID
WHERE (wp_postmeta.meta_value = 'something'
OR wp_postmeta.meta_value LIKE '2010-%')
ORDER BY wp_postmeta.meta_value ASC
LIMIT 0,10";

Hopefully that is what you were after. I removed the 2010 >= condition given that the 2010 LIKE condition will pull that same data.

Answer 2

Maybe you mean an OR instead of an AND?

...
WHERE wp_postmeta.meta_value >= '2010-03-02' OR
      wp_postmeta.meta_value = 'something' OR
      wp_postmeta.meta_value LIKE '2010-'

Unfortunately in the English language, AND and OR can be used interchangeably in certain cases:

• "I always carry an umbrella for when it rains and snows."
• "I always carry an umbrella for when it rains or snows."

The above wouldn't be equivalent for computers :)

Answer 3

Try this:

$query = "SELECT p.ID, m1.meta_value, m2.meta_value, p.post_title FROM
          wp_posts p, wp_postmeta m1, wp_postmeta m2
          WHERE p.post_date > '2010-03-02' AND
          m1.post_id=p.ID AND m2.post_id=p.ID AND 
          m2.meta_value LIKE '2010-%' AND 
          m1.meta_value = 'something'
          ORDER BY m1.meta_value, m2.meta_value 
          LIMIT 0,10";

No need for the distinct, since we're showing everything on one row anyway.

Answer 4

For getting post information and their post meta values you need the following query:

SELECT DISTINCT wp_postmeta.meta_key, wp_postmeta.meta_value, wp_posts.ID,
                wp_posts.guid, wp_postmeta.post_id, wp_posts.post_title
FROM   wp_postmeta JOIN wp_posts
ON     wp_postmeta.post_id = wp_posts.ID
WHERE  wp_posts.post_date >= '2010-03-02'
AND    EXISTS (SELECT 1 from wp_postmeta m1 
               WHERE m1.post_ised = wp_posts.ID
               AND wp_postmeta.meta_value = 'something'
AND    EXISTS (SELECT 1 from wp_postmeta m2 
               WHERE m2.post_ised = wp_posts.ID
               AND wp_postmeta.meta_value LIKE '2010-%')
ORDER  BY wp_postmeta.meta_value 
ASC LIMIT 0, 10

Answer 5

A larger data set and sample answer would help clarify the question but here is my interpretation of what you are looking for. It's not elegant but if you've got the buffer space allocated it works.

SELECT DISTINCT wp_postmeta.meta_key, wp_postmeta.meta_value, wp_posts.ID, wp_posts.guid, wp_postmeta.post_id, wp_posts.post_title FROM wp_postmeta INNER JOIN wp_posts ON wp_postmeta.post_id=wp_posts.ID WHERE wp_postmeta.post_id IN ( select post_id from wp_postmeta where str_to_date(meta_value, '%Y-%m-%d') >= 2010-03-02' and post_id in (select post_id from wp_postmeta where meta_value = 'something') );