我试图在MySQL语句中将两个变量传递到ORDER BY$variable1$variable2中。变量是从下拉菜单窗体中获取的。

PHP

if(isset($_POST['order'])){
         $sort1 = mysql_real_escape_string($_POST['sort']);
         $sort2 = mysql_real_escape_string($_POST['order']);
        }
        if(!@$_POST['order']){
            $sort1 = 'ID';
            $sort2 = 'DESC';
        }
        $topics = mysql_query(" SELECT topic_id AS 'ID', topic_head AS 'Title', 
                                       topic_tags AS 'TAGS', topic_owner AS 'CREATED BY', topic_date AS 'CREATED ON'
                                FROM   forum_topics 
                                ORDER BY '{$sort1}' '{$sort2}'  ") or die (mysql_error());
                        ?>

HTML表单

<ul class="sort">
        <li><form action="topics.php" method="post">
                <label class="label">Sort Table By</label>
                <select name="sort">
                    <option value ="">    </option>
                    <option value ="ID">ID</option>
                    <option value ="Title">Title</option>
                    <option value ="TAGS">TAGS</option>
                    <option value ="VIEWS">VIEWS</option>
                    <option value ="CREATED ON">CREATED ON</option>
                    <option value ="CREATED BY">CREATED BY</option>
                </select>
                <label class="label">Order By</label>
                <select name = "order" class="tap_Select">
                    <option value ="">    </option>
                    <option value ="ASC">Ascending</option>
                    <option value ="DESC">Descending</option>
                </select>
                <input type="submit" name="order" value="SORT" >
           </form>
        </li>
    </ul>

请帮忙。

更新

当我删除$sort1和$sort2 周围的报价时

我收到以下错误：You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SORT' at line 4

更新2

当我在不通过mysql_querry（）传递的情况下执行$sql=".."时我有这个

SELECT topic_id AS 'ID', topic_head AS 'Title', topic_tags AS 'TAGS', topic_owner AS 'CREATED BY', topic_date AS 'CREATED ON' FROM forum_topics ORDER BY ID SORT

不要使用引号，例如

ORDER BY 'ID' 'DESC'

不正确。应该是

ORDER BY ID DESC

引号将id和desc转换为纯字符串，而不是关键字/字段名。

删除括号并在排序元素之间添加逗号

ORDER BY $sort1 $sort2  ") or die (

编辑：做以下更改，这样我们就可以看到发生了什么…

走这条

$topics = mysql_query(" SELECT topic_id AS 'ID', topic_head AS 'Title',  
                                   topic_tags AS 'TAGS', topic_owner AS 'CREATED BY', topic_date AS 'CREATED ON' 
                            FROM   forum_topics  
                            ORDER BY '{$sort1}' '{$sort2}'  ") or die (mysql_error());

将查询粘贴到一个变量中，并用该变量替换mysql_query中的字符串；

$query="SELECT topic_id AS 'ID', topic_head AS 'Title',  
                                   topic_tags AS 'TAGS', topic_owner AS 'CREATED BY', topic_date AS 'CREATED ON' 
                            FROM   forum_topics  
                            ORDER BY '{$sort1}' '{$sort2}'  ";
$topics = mysql_query($query) or die (mysql_error());

然后回显查询并将结果剪切/粘贴到问题中。有些事情并没有按照你想象的那样发生。

echo $query;

您的列名似乎包含空格，因此需要使用backtick:

ORDER BY `{$sort1}` {$sort2}

还要注意，在ORDER BY子句的情况下，mysql_real_escape_string不提供任何保护。您需要对照允许输入的白名单来检查您的输入。

$topics = mysql_query("SELECT
    topic_id AS `ID`,
    topic_head AS `Title`, 
    topic_tags AS `TAGS`,
    topic_owner AS `CREATED BY`,
    topic_date AS `CREATED ON`
FROM   forum_topics 
ORDER BY {$sort1} {$sort2}'
") or die (mysql_error());

您需要删除ORDERBY值周围的'。此外，请注意别名值周围首选的反勾号。

还请注意，您应该使用mysqli_*或PDO，因为mysql_*已被弃用（请参阅PHP.net上与mysql_*相关的大多数功能中的红色警告。

以下语法在PHP 7.4.12和MySQL 5.7.32中适用：

$sort = sanitize_text_field($_POST['sort']; // e.g., 'title'
$order = sanitize_text_field($_POST['order']; // e.g., 'ASC'
$order_by = $sort . ' ' . $order;
$results = $wpdb->get_results( "
    SELECT
        *
    FROM
        MyTable
    ORDER BY
        $order_by
" );

将变量传递到ORDER BY MySQL语句

Passing Variables to ORDER BY MySQL Statements

PHP

HTML表单