具有条件首选项的mysql SELECT [英] mysql SELECT with condition preference

问题描述

我有一个包含4个字段的数据库，我想按多个或条件从该数据库中搜索用户，问题是如何根据正确或条件的数量对选择进行索引. 领域是课程，学校，大学，城市 我希望结果应该根据正确的数目或条件在这里显示，这是代码

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;
    }
    ?>

• 我只是希望显示的结果的优先级应按真实OR条件数的降序排列，换句话说，按匹配值数的降序排列

推荐答案

尝试以下查询:

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;

这篇关于具有条件首选项的mysql SELECT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！