在SQL SELECT语句和PDO中使用大于或等于(> =)和小于或等于(< =) [英] Use of greater than or equals(>=) and lesser than or equals(<=) in SQL SELECT statements and PDO

问题描述

我一直在尝试做一个小功能，以从存储在MySQL数据库中的一组范围中确定等级，即A在80到100之间，A-在74到79之间，依此类推.我正在使用PDO和准备好的语句.但是PDO没有给我结果.有人可以帮我解决这个问题.下面是表格预览

I have been trying to make a small function to determine grades from a set of ranges stored in a MySQL database i.e A is between 80 and 100,A- is between 74 and 79 and so on. Am using PDO and prepared statements. However PDO does not give me the result. Could someone please help me resolve this.Below is the table preview

<?php
     include("./inc/db.php");
    //i have set PDO attribute to ERRMODE_EXCEPTION in the connection file
    //this code has failed to work
    $mark = 85;

    try{
        $pk = $db->prepare("SELECT * FROM grading WHERE ? BETWEEN min AND max");
        $pk->bindParam(1,$mark);
        $pk->execute();
        $ind=$pk->fetch(PDO::FETCH_ASSOC);
        echo $ind['grade'];
    } catch(PDOexception $f) {
        echo $f->getMessage();   
    }

//however when i try this it displays the grade. whats wrong with the previous code?

    try{
        $pk = $db->prepare("SELECT * FROM grading WHERE 85 BETWEEN min AND max");
        $pk->execute();
        $ind=$pk->fetch(PDO::FETCH_ASSOC);
        echo $ind['grade'];
    } catch(PDOexception $f) {
        echo $f->getMessage();   
    }
 ?>

推荐答案

尝试稍微调整一下逻辑.就像Phoenix Wright一样，有时需要花很多时间才能解决问题:

Try adjusting the logic a little. Just like Phoenix Wright, sometimes all it takes it flipping things around to solve them:

$pk = $db->prepare("SELECT `grade` FROM `grading` WHERE ? BETWEEN `min` and `max`");
$pk->execute(array($mark));
$ind = $pk->fetch(PDO::FETCH_ASSOC);
echo $ind['grade'];

使用一个值作为BETWEEN和IN的第一个操作数并不是很多人会想到的，但是它是如此强大；)

Using a value as the first operand for BETWEEN and IN is not something many people would think of, yet it's so powerful ;)

更重要的是(感谢Fred -ii-偶然帮助我实现了)，min和max是函数名.如果要将它们用作列名，则必须 将它们包装在反引号中，就像我在上面的代码中所做的那样.

More importantly (and thanks to Fred -ii- for accidentally helping me realise), min and max are function names. If you want to use them as column names, you must wrap them in backticks, as I have done in my code above.

通常，您应该始终在表名和列名前后加上反引号.不这样做类似于在PHP中编写$foo = bar;.当然，它会正常工作，但是如果bar恰好是常量或函数名，那么一切都将变得一团糟.

As a general rule, you should always put backticks around your table and column names. Not doing so is akin to writing $foo = bar; in PHP. Sure, it'll work, but if bar happens to be a constant or function name then all hell breaks loose.

这篇关于在SQL SELECT语句和PDO中使用大于或等于(> =)和小于或等于(< =)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！