php和mysql对表中的数据进行排序

dwthyt8l 于 2021-06-19 发布在 Mysql

关注(0)|答案(2)|浏览(329)

我在使用下拉列表对表中的数据库进行排序时遇到问题我已经使用了一些方法，但是没有起作用
这是我的php/查询代码：

<?php
    $servername = "localhost";
    $username = "root";
    $password = "";
    $dbname = "admin_table";

    // Create connection
    $conn = mysqli_connect($servername, $username, $password, $dbname);
    // Check connection
    if (!$conn) {
        die("Connection failed: " . mysqli_connect_error());
    }
    // $res = array('un' => 'username',
    // 'hs' => 'highscore',
    // 'oi' => 'oil',
    // 'ml' => 'metal',
    // 'cp' => 'copper',
    // 'gd' => 'Gold', );
    $sql = " SELECT id,username,highscore,oil,metal,copper,gold FROM users";

    switch ($_POST['sort']) {
        case 'un':
            $sql .='ORDER BY username';
            break;
        case 'hs':
            $sql .='ORDER BY highscore';
            break;
        case 'oi':
            $sql .='ORDER BY oil';
            break;
        case 'ml':
            $sql .='ORDER BY metal';
            break;
        case 'cp':
            $sql .='ORDER BY copper';
            break;
        case 'gd':
            $sql .='ORDER BY gold';
            break;

    }
    $result = mysqli_query($conn, $sql);

    if($result->num_rows > 0){
     while($row = $result->fetch_assoc()){
?>

这是我的html代码：

<div class = 'sort-table'>
<form name="sort" action="" method="POST">
    <select id="sorting" name="select">
        <option value="id"> Sort by...</option>
        <option value="username">Username</option>
        <option value="highscore">Highscore</option>
        <option value="oil">Oil</option>
        <option value="metal">Metal</option>
        <option value="copper">Copper</option>
        <option value="gold">Gold</option>
    </select>
    <input type="submit" name="go-sort" value="OK" id="go-sort">
</form>

如果有其他的方法，请让我知道提前谢谢你，并为不便表示歉意

mysql php Html forms sorting

来源：https://stackoverflow.com/questions/53395015/php-and-mysql-sorting-data-inside-the-table

2条答案

按热度按时间

vecaoik11#

我觉得这样更好

if(isset($_POST['sort'])){

    $servername = "localhost";
    $username = "root";
    $password = "";
    $dbname = "admin_table";

    // Create connection

    try {

        $conn = new mysqli($servername, $username, $password, $dbname);

        $selected = $_POST['select'];

        $stmt = $conn->prepare("SELECT id,username,highscore,oil,metal,copper,gold FROM users ORDER BY ? ");

        $stmt->bind_param('s', $selected);

        $stmt->execute();

        $result = $stmt->get_result();

    } catch (Exception $e ) {
        echo "Service unavailable";
        echo "message: " . $e->message;
        exit;
    }

}

赞(0）回复(0）举报 2021-06-19

sq1bmfud2#

您指的是表单本身，而不是输入元素。
生成的sql格式不正确。
您的代码易受sql注入的影响。

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "admin_table";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

$sql = "SELECT id, username, highscore, oil, metal, copper, gold FROM users";
$orders = [
   'username',
   'highscore',
   'oil',
   'metal',
   'copper',
   'gold',
];

// Refer to correct form element 'select' rather than 'sort'
// Only use a predefined option to order by
$order = in_array($_POST['select'], $orders) ? $_POST['select'] : false;

if ($order) {
   $sql .= ' ORDER BY ' . $order;
}

$result = mysqli_query($conn, $sql);

if($result->num_rows > 0){
 while($row = $result->fetch_assoc()){
?>

赞(0）回复(0）举报 2021-06-19

我来回答

php和mysql对表中的数据进行排序

2条答案

相关问题

热门标签

最新问答