Home  >  Article  >  Backend Development  >  有and 有OR的依据几个字段生成SQL语句

有and 有OR的依据几个字段生成SQL语句

WBOY
WBOYOriginal
2016-06-13 13:16:57789browse

有and 有OR的根据几个字段生成SQL语句
有如下图的数据表信息,有什么好的方法把它们组合成一条SQL?如果全是and 的或者全是or的,这方法好办,可是两个同时都有的,怎么办呢?



全是AND 或OR的话

PHP code
<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->
        $sql = "select * from sk_cvfilter where cvid=58 and columnname!=''";
        $query = mysql_query($sql);
        $sql = '';
        while($row = mysql_fetch_assoc($query)){
            if($row['comparator']=='eq'){$comparator="=";}
            else{$comparator=$row['comparator'];}
            $sql .=$row['columnname'].$comparator."'".$row['value']."' ".$row['andor'].' ';
        }



------解决方案--------------------
SQL code
select * from sk_cvfilter where cvid=58 and columnname!='' and andor='and' or andor='or'
<br><font color="#e78608">------解决方案--------------------</font><br>你的这个记录有问题,为什么呢?<br><br>例如出现一个 (a = 1 or (b=2 and b=3)),这个时候,你是怎么记录的?<br><br>我猜是这样的<br><br>columnname   comparator   value   andor<br>a            eq           1       or<br>b            eq           2       and<br>c            eq           3      <br><br>这个时候问题出现了,假如你不知道上面的那个关系式(a = 1 or (b=2 and b=3)),<br>如果只从结构看的话,你是认为<br><br>(a = 1 or b =2 ) and b = 3<br><br>还是<br><br>  a = 1 or (b=2 and b=3)<br><br>上述问题不考虑,所有or为一个条件和其他条件and的写法<br><br><br>
PHP code

<?php $sql = "select * from sk_cvfilter where cvid=58 and columnname!=''";
$query = mysql_query($sql);
$sql = '';

// 记录上一句的是不是or
$is_last_or = false;


while($row = mysql_fetch_assoc($query)){
    if($row['comparator']=='eq'){$comparator="=";}
    else{$comparator=$row['comparator'];}
    
    
    if($is_last_or) {
        if($row['andor'] == 'and') {
            // 和下一句是and关系说明括号要结束了
            $sql .=$row['columnname'].$comparator."'".$row['value']."' ) ".$row['andor'].' ';
        } else {
            // 持续括号里的内容
            $sql .=$row['columnname'].$comparator."'".$row['value']."' ".$row['andor'].' ';
        }
    } elseif($row['andor'] == 'or') {
        // 如果有or则意味着和下一句有或的关系(因为你这里没有括号)
        $is_last_or = true;
        // 加上括号吧
        $sql .= '( ' . $row['columnname'].$comparator."'".$row['value']."' ".$row['andor'].' ';
    } else {
        $sql .=$row['columnname'].$comparator."'".$row['value']."' ".$row['andor'].' ';
    }
    
}
<br /><font color="#e78608">------解决方案--------------------</font><br>
PHP code

<?php $sql = "select * from sk_cvfilter where cvid=58 and columnname!=''";
$query = mysql_query($sql);
$sql = '';

// 记录上一句的是不是or
$is_last_or = false;


while($row = mysql_fetch_assoc($query)){
    if($row['comparator']=='eq'){$comparator="=";}
    else{$comparator=$row['comparator'];}
    
    
    if($is_last_or) {
        if($row['andor']  != 'or') {
            // 和下一句是and或者结束了关系说明括号要结束了
            $sql .=$row['columnname'].$comparator."'".$row['value']."' ) ".$row['andor'].' ';
        } else {
            // 持续括号里的内容
            $sql .=$row['columnname'].$comparator."'".$row['value']."' ".$row['andor'].' ';
        }
    } elseif($row['andor'] == 'or') {
        // 如果有or则意味着和下一句有或的关系(因为你这里没有括号)
        $is_last_or = true;
        // 加上括号吧
        $sql .= '( ' . $row['columnname'].$comparator."'".$row['value']."' ".$row['andor'].' ';
    } else {
        $sql .=$row['columnname'].$comparator."'".$row['value']."' ".$row['andor'].' ';
    }
    
} <div class="clear">
                 
              
              
        
            
Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn