I've been trying to get this query to work but it keeps failing to return any rows

  FROM wp_users a, wp_usermeta b 
WHERE b.user_id=a.ID 
  (b.meta_key='firstname-groom' AND b.meta_value LIKE 'Darren') 
  OR (b.meta_key='firstname-bride' AND b.meta_value LIKE 'Someone') 
  OR (b.meta_key='lastname-bride' AND b.meta_value LIKE 'Cool')
AND (b.meta_key='weddingdate' AND b.meta_value='12/14/2014') 

The query return rows if I remove this part AND (b.meta_key='weddingdate' AND b.meta_value='12/14/2014') but I need to be able to make it work with that requirement (weddingdate=12/14/2014)


Actually i figured it out now using join

SELECT a.ID FROM wp_users a INNER JOIN wp_usermeta b ON b.user_id = a.ID AND 
( b.meta_key = 'firstname-groom' AND b.meta_value LIKE 'Darren' )
OR ( b.meta_key = 'firstname-bride' AND b.meta_value LIKE 'Someone' )
OR ( b.meta_key = 'lastname-bride' AND b.meta_value LIKE 'Cool' )
INNER JOIN wp_usermeta c ON c.user_id = a.ID AND 
( c.meta_key = 'weddingdate' AND c.meta_value = '12/14/2014' )

The SQL does not trigger an error, so you do not have data in your database that matches all of the criteria. As written, you'd need one or more of these:

(b.meta_key='firstname-groom' AND b.meta_value LIKE 'Darren') 
OR (b.meta_key='firstname-bride' AND b.meta_value LIKE 'Someone') 
OR (b.meta_key='lastname-bride' AND b.meta_value LIKE 'Cool')

And this:

AND (b.meta_key='weddingdate' AND b.meta_value='12/14/2014') 

To match for this to return results. However, if you look carefully, that condition is never going to occur because it would require that b.meta_key be both weddingdate and one of firstname-groom, firstname-bride, lastname-bride at the same time, which will never happen. You will need to JOIN the tables multiple times once for the names and once for the date. (It looks like you worked that out already in a comment, so I won't write the SQL.)

I would make another comment though.

  1. Saving a date like 12/14/2014 is a bad idea. There is very little rational sorting you can do with that date format and extracting month, day, or year data is not going to be especially efficient or reliable as none of MySQL's date functions will work.

I would suggest a user query but looking at your SQL I think the logic is too complicated for that class.

  • yea i dont need any rational sorting for the date it needs to be exact and will always be in that format (from jqueryui-datepicker) so a string is fine for this particular case
    – b747fp
    Jun 22 '13 at 16:55
  • But converting the date is simple enough and much more future-proof.
    – s_ha_dum
    Jun 22 '13 at 17:27
SELECT m1.`user_id` as `ID`, m1.p1, m2.p2, m3.p3, m4.p4 
            (SELECT `user_id`, `meta_value` as p1 FROM `wp_usermeta`
                WHERE (`meta_key` = 'firstname-groom' AND `meta_value` LIKE 'Darren') 
            GROUP BY `user_id`) m1,
            (SELECT `user_id`, `meta_value` as p2 FROM `wp_usermeta`
                WHERE (`meta_key` = 'firstname-bride' AND `meta_value` LIKE 'Someone') 
            GROUP BY `user_id`) m2,
            (SELECT `user_id`, `meta_value` as p3 FROM `wp_usermeta`
                WHERE (`meta_key` = 'lastname-bride' AND `meta_value` LIKE 'Cool') 
            GROUP BY `user_id`) m3,
            (SELECT `user_id`, `meta_value` as p4 FROM `wp_usermeta`
WHERE (`meta_key` = 'weddingdate' AND `meta_value` = '12/14/2014' )
            GROUP BY `user_id`) m4
            m1.`user_id` = m2.`user_id` AND m1.`user_id` = m3.`user_id` AND m1.`user_id` = m4.`user_id`

If necessary, where it was ready function generating such queries. The function returns an array of user IDs

  • EXPLAIN gives "Impossible WHERE noticed after reading const table..."
    – s_ha_dum
    Jun 22 '13 at 18:14
  • Do not quite understand what you mean. Use similar in my project. You can replace the group m1 adding a parameter query OR. Plus, you can use CAST or CONVERT to convert values ​​on the fly. Jun 23 '13 at 4:11
  • Put the keyword EXPLAIN in front of the query: dev.mysql.com/doc/refman/5.0/en/explain.html
    – s_ha_dum
    Jun 23 '13 at 4:29
  • It all depends on the severity of queries... Jun 23 '13 at 5:10
  • No idea what that means but MySQL thinks that the WHERE clause will always be false. I get the same message with your code as-is, and when I substitute values that should be valid for my database. The problem, I think, is that you require m1.user_id` = m2.user_id which requires someone to have two first names. Your query is a very complicated construction-- overly complicated I'd say-- but it looks like the equivalent of an AND query for all of the fields. That is not what the OP wants.
    – s_ha_dum
    Jun 23 '13 at 15:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.