MySQL select query with multiple conditions

Question

I have written a MySQL query but it doesn t seem to be working  because the result is empty  Is there any mistake in my code    result   mysql query        SELECT user id        FROM wp usermeta        WHERE            meta key    first name  AND meta value     us name   AND                    meta key    yearofpassing  AND meta value     us yearselect   AND            meta key    u city  AND meta value     us reg   AND            meta key    us course  AND meta value     us course

User · Answer

You have conditions that are mutually exclusive - if meta key is  first name   it can t also be  yearofpassing    Most likely you need your AND s to be OR s    result   mysql query  SELECT user id FROM wp usermeta  WHERE  meta key    first name  AND meta value     us name    OR  meta key    yearofpassing  AND meta value     us yearselect    OR  meta key    u city  AND meta value     us reg    OR  meta key    us course  AND meta value     us course

User · Answer

fthiella  s solution is very elegant   If in future you want show more than user id you could use joins  and there in one line could be all data you need   If you want to use AND conditions  and the conditions are in multiple lines in your table  you can use JOINS example   SELECT  w name   user id        FROM  wp usermeta  as  w name       JOIN  wp usermeta  as  w year  ON  w name   user id   w year   user id             AND  w name   meta key     first name             AND  w year   meta key     yearofpassing        JOIN  wp usermeta  as  w city  ON  w name   user id   w city  user id            AND  w city   meta key     u city       JOIN  wp usermeta  as  w course  ON  w name   user id   w course   user id             AND  w course   meta key     us course       WHERE            w name   meta value      us name  AND                    w year  meta value       us yearselect  AND            w city   meta value      us reg  AND            w course   meta value      us course    Other thing  Recommend to use prepared statements  because mysql   functions is not SQL injection save  and will be deprecated   If you want to change your code the less as possible  you can use mysqli  functions  http   php net manual en book mysqli php  Recommendation   Use indexes in this table  user id highly recommend to be and index  and recommend to be the meta key AND meta value too  for faster run of query   The explain   If you use AND you  connect  the conditions for one line  So if you want AND condition for multiple lines  first you must create one line from multiple lines  like this   Tests  Table Data             PRIMARY                 INDEX       int       varchar 255     varchar 255                                           --------- --------------- -----------      user id   meta key        meta value     --------- --------------- -----------      1         first name      Kovge          --------- --------------- -----------      1         yearofpassing   2012           --------- --------------- -----------      1         u city          GaPa           --------- --------------- -----------      1         us course       PHP            --------- --------------- -----------    The result of Query with  us name  Kovge   us yearselect  2012   us reg  GaPa    us course  PHP      ---------     user id     ---------     1           ---------    So it should works

User · Answer

Lets suppose there is a table with following describe command for table  hello -      name char 100       id integer       count integer      city char 100     we have following basic commands for MySQL -   select   from hello  select name  city from hello  etc   select name from hello where id   8  select id from hello where name    GAURAV     now lets see multiple where condition -   select name from hello where id   3 or id   4 or id   8 or id   22   select name from hello where id  3 and count   3 city    Delhi     This is how we can use multiple where commands in MySQL

User · Answer

I would use this query   SELECT   user id FROM   wp usermeta  WHERE     meta key    first name  AND meta value     us name   OR     meta key    yearofpassing  AND meta value     us yearselect   OR     meta key    u city  AND meta value     us reg   OR    meta key    us course  AND meta value     us course   GROUP BY   user id HAVING   COUNT DISTINCT meta key  4   this will select all user id that meets all four conditions

User · Answer

also you can use  AND  instead of  OR  if you want both attributes to be applied   select   from tickets where  assigned to  1   and  status  open   order by created at desc

[mysql] MySQL select query with multiple conditions

Examples related to mysql

Examples related to select