个人中心
gpt4 book ai didi

php - 使用 HTML 链接对 MySQL 数据进行排序

转载 作者：行者123 更新时间：2023-11-29 21:06:57 25 4
gpt4 key购买 nike

我试图允许用户通过单击链接并运行 php 对其进行排序来订购列表，但我无法让它工作，我不知道为什么。这些过滤器工作得很好，但由于我对 PHP 很陌生，它们可能不是最有效的制作方法。我试图让用户从第一个 html 部分末尾的 anchor 标记中单击，然后使用正确的排序对 $student_query 进行排序

<head>
<title>Student Data</title>
<link href="stylesheet.css" rel="stylesheet" type="text/css">
</head>
<body>

<form action="registered.php" method="POST">
<h2>Filters:</h2>
Year of Graduation: <input type="text" id="yearFilter" name="yearFilter"/><br/>

Major:
<select name="majorFilter">
    <option></option>
    <option value="Creative Media and Entertainment">Creative Media and Entertainment</option>
    <option value="Interactive Media">Interactive Media</option>
    <option value="Recording Industry Studies">Recording Industry Studies</option>
    <option value="Sports Media">Sports Media</option>
</select><br />

Post-Grad Activity:
<select name="postGradFilter">
    <option></option>
    <option value="Employed">Employed</option>
    <option value="Graduate/Professional School or Advanced Coursework"> Graduate/Professional School or Advanced Coursework</option>
    <option value="Gap Year">Gap Year</option>
    <option value="Other">Other</option>
</select><br />

<input type="submit" name="submitFilters" value="Filter" id="submitFilters" />


    </form>

</body>
//<a href="registered.php?sorting='.$sort.'&field=lastName">Sort By Name</a>
//<a href="registered.php?sorting='.$sort.'&field=gradDate">Sort By Graduation Year</a>
//<a href="registered.php?sorting='.$sort.'&field=major">Sort By Major</a>

<?php
include('includes/db_connect.php');

$sort = 'ASC';
$field = 'major.major';
$filter_gradYear = mysqli_real_escape_string($conn, $_POST['yearFilter']);
$filter_major = mysqli_real_escape_string($conn, $_POST['majorFilter']); 
$filter_postGrad = mysqli_real_escape_string($conn, $_POST['postGradFilter']);
$query_student;
if(isset($conn, $_POST['submitFilters'])){
    $query_student = "  SELECT *
                        FROM student
                        JOIN major
                        ON student.studentID=major.studentID
                        ";
    if(!empty($filter_gradYear) && empty($filter_major)){
        $query_student = "  SELECT *
                            FROM student
                            JOIN major
                            ON student.studentID=major.studentID AND gradDate = '$filter_gradYear'";
    }
    else if(!empty($filter_gradYear) && !empty($filter_major)){
        $query_student = "  SELECT *
                            FROM student
                            JOIN major
                            ON student.studentID=major.studentID AND (gradDate = '$filter_gradYear' AND major = '$filter_major')";
    }
    else if(empty($filter_gradYear) && !empty($filter_major)){
        $query_student = "  SELECT *
                            FROM student
                            JOIN major
                            ON student.studentID=major.studentID AND major = '$filter_major'";
    }
    if(!empty($filter_postGrad)){
        if($filter_postGrad == "Employed"){
        $query_student .= " JOIN jobSearch
                            ON major.studentID=jobSearch.studentID
                            JOIN job
                            WHERE jobSearch.studentID=job.studentID AND jobSearch.activity = '$filter_postGrad'";
        }
        else if($filter_postGrad == "Graduate/Professional School or Advanced Coursework"){
            $query_student .= " JOIN education
                                WHERE major.studentID=education.studentID";
        }
        else if($filter_postGrad == "Gap Year" || $filter_postGrad == "Other"){
            $query_student .= " JOIN jobSearch
                                WHERE major.studentID=jobSearch.studentID AND jobSearch.activity = '$filter_postGrad'";
        }
        else if($filter_postGrad == ""){

        }
    }

}

if(isset($_GET['sorting']))
{
  if($_GET['sorting']=='ASC')
  {
  $sort='DESC';
  }
  else { $sort='ASC'; }
}
if($_GET['field']=='lastName')
{ 
    $field = "student.lastName"; 
    $query_student .= "ORDER BY $field $sort";
}
else if($_GET['field']=='gradDate')
{
   $field = "major.gradDate";
   $query_student .= "ORDER BY $field $sort";
}
else if($_GET['field']=='major')
{ 
   $field="major.major";
   $query_student .= "ORDER BY $field $sort";
}
echo $query_student;
$result_student=mysqli_query($conn, $query_student) or die(mysqli_error($conn));

?>

<table>
    <tr>
        <th>First Name</th> 
        <th>
            Last Name
        </th>
        <th>
            Graduate Year
        </th>
        <th>
            Major(s)
        </th>
        <th>Activity After Graduation</th>
        <?php
        if($filter_postGrad == "Employed") {
            echo '<th>Job Title</th>';
            echo '<th>Employer Name</th>';
            echo '<th>Earnings</th>';
            echo '<th>Is it their interest?</th>';
            echo '<th>Where was employment found?</th>';
            echo '<th>When did they start to search?</th>';
        }
        else if($filter_postGrad == "Graduate/Professional School or Advanced Coursework"){
            echo '<th>Location</th>';
            echo '<th>Institution</th>';
        }
        ?>
    </tr>

<?php

    while($row = mysqli_fetch_array($result_student)){ ?>
    <tr>
        <td><?php echo $row['firstName']?></td>
        <td><?php echo $row['lastName']?></td>
        <td><?php echo $row['gradDate']?></td>
        <td><?php echo $row['major']?></td>
        <td><?php echo $row['activity']?></td>
        <?php
        if($filter_postGrad == "Employed") {
            echo '<td>' . $row['title'] . '</td>';
            echo '<td>' . $row['employer'] . '</td>';
            echo '<td>' . $row['salary'] . '</td>';
            echo '<td>' . $row['interest'] . '</td>';
            echo '<td>' . $row['service'] . '</td>';
            echo '<td>' . $row['startSearch'] . '</td>';
         }  
         else if($filter_postGrad == "Graduate/Professional School or Advanced Coursework"){
             echo '<td>' . $row['location'] . '</td>';
             echo '<td>' . $row['schoolName'] . '</td>';
         }?>

    </tr>
<?php } ?>
</table>

<?php
$conn->close();
?>

最佳答案

在之前的回答中，我提到您在 ORDER BY 之前缺少一个空格。 。我仍然相信这是真的(当存在过滤器时)，但我认为还有一些其他问题。

当您第一次访问该页面(例如: /registered.php?sort=DESC&field=major )时，您会丢失 SELECT 的第一部分因为$query_student如果 $_POST['submitFilters'] 则永远不会初始化为任何内容未设置。

原始代码:

// When you first go to the page this will evaluate to 
// false and $query_student will never be initialized
if(isset($conn, $_POST['submitFilters'])){
    $query_student = "  SELECT *
                        FROM student
                        JOIN major
                        ON student.studentID=major.studentID
                     ";

这会导致 echo 仅打印 ORDER BY 。因此，第一步是将默认查询移至 isset 之上检查:

$query_student = "  SELECT *
                    FROM student
                    JOIN major
                    ON student.studentID=major.studentID
                 ";
if(isset($conn, $_POST['submitFilters'])){

但是还有另一个问题。一旦您提交表单，您将丢失排序和字段参数，因为这些是获取参数，并且您正在提交到“registered.php”。您只需从表单中删除操作标记即可解决此问题:

<form method="POST">

现在这将提交到与查询字符串相同的页面。

关于php - 使用 HTML 链接对 MySQL 数据进行排序，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/36733107/

25 4 0
文章推荐： mysql - 带有 DISTINCT 和 WHERE 子句的简单 DBIx::Class 查询
文章推荐： javascript - Facebook 登录在移动浏览器中不起作用
文章推荐： javascript - angular2/javascript 中的动态变量
文章推荐： MySql LEFT OUTER JOIN 导致重复行
行者123
个人简介

我是一名优秀的程序员,十分优秀！

滴滴打车优惠券免费领取
滴滴打车优惠券
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com