时间戳

首页 > 解决方案 > 在数据表插件上的 mysql WHERE 子句上使用 GET 方法不起作用

php - 在数据表插件上的 mysql WHERE 子句上使用 GET 方法不起作用

问题描述

我正在使用数据表（https://datatables.net/）插件来生成数据。所有数据都来自enrollments数据库表。

我无法找到问题所在。当我尝试使用GET方法获取要在WHERE子句上使用的变量时，出现错误。如果您需要更多代码，请告诉我。

file1: enrollment_select_statement.php

$sqlEnrollments = "SELECT e.enrollment_id, e.studentid, e.joiningdate, e.notes, e.termyearid,
    s.student_id, s.firstname, s.lastname, s.section, s.standard AS studentstandard, s.status,
    c.course_id, c.subject AS coursesubject, c.standard AS coursestandard, c.termyearid AS coursetermyearid,
    b.name AS batchname,
    t.total, t.name AS termname, t.startingdate, t.endingdate,
    sc.name AS schoolname,
    ty.currentyear, ty.nextyear,
    tyc.currentyear AS currentyearforcourse, tyc.nextyear AS nextyearforcourse
FROM enrollments e
    LEFT JOIN students s    ON e.studentid = s.student_id
    LEFT JOIN courses c     ON e.courseid = c.course_id
    LEFT JOIN batches b     ON e.batchid = b.batch_id
    LEFT JOIN terms t       ON e.termid = t.term_id
    LEFT JOIN termyears ty  ON e.termyearid = ty.termyear_id
    LEFT JOIN schools sc    ON s.schoolid = sc.school_id
    LEFT JOIN termyears tyc ON c.termyearid = tyc.termyear_id";
$resultEnrollments = mysqli_query($con, $sqlEnrollments);

上述文件包含在此页面中。

file2: enrollment_data.php

include 'database/db_enrollment/enrollment_select_statement.php';

$data = [];
foreach ($resultEnrollments as $row) {
    $enrollmentNotes = $row['notes'];
    $enrollmentJoiningDate = $row['joiningdate'];
    ...

    $data[] = [
        'term_name_year' => "<small>".$termName."<br> <span class='text-muted'>".termYearFunction($currentYear, $nextYear)."</span></small>",
        'student_name' => $studentFirstname.' '.$studentLastname.'<br>'.$stdSecCondition,
        ...
    ];
}

echo json_encode(array('data' => $data));

这是最终文件js

file3: database_for_enrollment.js

$('#enrollmentTableForDataTable').DataTable({
    ajax: {
        url: 'enrollment_data.php'
    },
    columns:
    [
        { data: 'term_name_year' },
        { data: 'student_name' },
        { data: 'course_subject_standard' },
        { data: 'enrollment_joiningdate' },
        { data: 'notes' },
        { data: 'status' },
        { "mData": function (data, type, dataToSet) {
            var lnk = data.edit_hreflink;
            var hrefO = "<a href='" + lnk + "'>";
            var hrefC = "</a>";
            return hrefO + 'Edit' + hrefC;
        }},
    ],
});

让我解释

选项 1：
使用$id = 1

$id = 1

$sqlEnrollments = "SELECT e.enrollment_id, e.studentid, e.joiningdate, e.notes, e.termyearid,
    ...
    WHERE e.termyearid = $id";

选项 2：
在语句中使用它，SELECT但数字是硬编码的 forWHERE子句

$sql = "SELECT * FROM termyears WHERE termyear_id = 1";
$result = mysqli_query($con, $sql);
$row = mysqli_fetch_array($result);
$id = $row['termyear_id'];

$sqlEnrollments = "SELECT e.enrollment_id, e.studentid, e.joiningdate, e.notes, e.termyearid,
    ...
    WHERE e.termyearid = $id";

选项3：不起作用
从$URLtyId$_GET

$URLtyId = '';
if( isset( $_GET['tyId']) ) {
    $URLtyId = $_GET['tyId'];
}

$sql = "SELECT * FROM termyears WHERE termyear_id = $URLtyId";
$result = mysqli_query($con, $sql);
$row = mysqli_fetch_array($result);
$id = $row['termyear_id'];

$sqlEnrollments = "SELECT e.enrollment_id, e.studentid, e.joiningdate, e.notes, e.termyearid,
    ...
    WHERE e.termyearid = $id";

当我尝试包含方法中的WHERE子句时GET。我得到这个错误。

Warning: Invalid argument supplied for foreach() in localhost\enrollment_data.php on line 15
{"data":[]}

标签： phpmysql

解决方案

尝试将您的请求置于您的条件中：（例如）

if( isset( $_GET['tyId']) ) {
    $URLtyId = $_GET['tyId'];
    $sql = $bdd->query("SELECT * FROM termyears WHERE id = $URLtyId");
    $reqRow = $sql->rowCount();
    if($reqRow == 1){
      while($f = $sql->fetch()){
        echo $f["name"]
        //your database row
      }
     }
}

推荐阅读