CODEWITHSUNDEEP
phpmysqldatabaseselect
Manish Rai
Manish Rai

Reputation: 23

mysql SELECT with condition preference

i have a database with 4 fields, i want to search user from that database by multiple or conditions, the problem is how to index the selection according to number of true or conditions. fields are course, school, college, city i want that the results should be displayed according to number of true or conditions here is the code

    <?php
    $con = mysql_connect("xxx.xx.xxx.xxx","myusername","mypassword");
    if (!$con)
      {
     die('Could not connect: ' . mysql_error());
      }
    mysql_select_db("character", $con);
    $searchfrnd = mysql_query("SELECT * FROM user WHERE (course='physics' or  school='st thomas' or college='mit' or city='austin'))");
    WHILE($display = mysql_fetch_array($searchfrnd))
    {
    $frnd=$display[userid];
    echo $frnd;
    }
    ?>

Upvotes: 1

Views: 971

Answers (2)

Rapha&#235;l Althaus
Rapha&#235;l Althaus

Reputation: 60493

you could do something like this

select (case when surname = 'jack' then 1 else 0 end +
        case when school ='st' then 1 else 0 end +
        case when college='mit' then 1 else 0 end +
        case when city='austin' then 1 else 0 end) as score,
        *
from 
user 
WHERE surname='jack' or  
      school='st'     or 
      college='mit' or 
      city='austin')
order by score desc;

Upvotes: 3

bobwienholt
bobwienholt

Reputation: 17610

Try this query:

SELECT *
FROM user 
WHERE surname='jack' 
  OR  school='st' 
  OR college='austin' 
  OR city='mit'
ORDER BY (
  IF(surname='jack', 1, 0)
  + IF(school='st', 1, 0)
  + IF(college='austin' , 1, 0)
  + IF(city='mit', 1, 0)
) DESC;

Upvotes: 2

Related Questions