php - PDO SQL 查询未按预期工作

Question

我在 PHP 中有这个函数:

function getFilteredList($conn, $limit, $offset, $job_app, $Job_Type, $location) {
    /*
    <option value="0">Select category or Search all</option>
    <option value="1">Business and Administration</option>
    <option value="2">Education and Training</option>
    <option value="3">Leisure, Travel and Tourism</option>
    <option value="4">Information and Communication Technology</option>
    */
    if($Job_Type == 1){
        $Job_Type2 = '%1%';
    }elseif($Job_Type == 2){
        $Job_Type2 = '%2%';
    }elseif($Job_Type == 3){
        $Job_Type2 = '%3%';
    }elseif($Job_Type == 4){
        $Job_Type2 = '%4%';
    }
    $query = $conn->prepare("SELECT * FROM `jobs_current` WHERE `job_type` LIKE :job_type ORDER BY `jobs_current`.`job_comp_loc` ASC LIMIT :limit OFFSET :offset ");
    $query->bindParam(':limit', $limit, PDO::PARAM_INT);
    $query->bindParam(':offset', $offset, PDO::PARAM_INT);
    $query->bindParam(':job_type', $Job_Type2, PDO::PARAM_STR);
    $query->execute();

    $end = "";
    while ($row = $query->fetch(PDO::FETCH_ASSOC))
    {
        if($row['job_app'] == $job_app){
            $end = $end.'<tr class="hoverOver"><td class="td1"><a   href="/jobview.php?id='.$row['id'].'">'.$row['job_comp_loc'].'</a></td>';
            $end = $end.'<td class="td2"><a   href="/jobview.php?id='.$row['id'].'">'.$row['job_desc'].'</a></td>';
            $end = $end.'<td class="td3"><a   href="/jobview.php?id='.$row['id'].'">'.$row['location'].'</a></td></tr>';
        }else if($job_app == 0){
            $end = $end.'<tr class="hoverOver"><td class="td1"><a   href="/jobview.php?id='.$row['id'].'">'.$row['job_comp_loc'].'</a></td>';
            $end = $end.'<td class="td2"><a   href="/jobview.php?id='.$row['id'].'">'.$row['job_desc'].'</a></td>';
            $end = $end.'<td class="td3"><a   href="/jobview.php?id='.$row['id'].'">'.$row['location'].'</a></td></tr>';
        }else{
            return '<td class="td1"></td><td class="td2" style="text-align: center;color: red;">Sorry, there doesnt seem to be any results!</td><td class="td3"></td>';
        }

    }
    return $end;
}

如果我为 job_app 传递任何参数，它只会返回“抱歉，似乎没有任何结果!”尽管有结果，因为某些行实际上包含 job-app 参数。如果我将 job_app 作为 2 并将 job_type 作为 2 传递，它应该会产生 6 个结果，但事实并非如此。相关 PHP 的其他部分:

数据库列表功能:

function getGenericList($conn, $limit, $offset, $job_app) {

    $query = $conn->prepare("SELECT * FROM `jobs_current` ORDER BY `jobs_current`.`job_comp_loc` ASC LIMIT :limit OFFSET :offset ");
    $query->bindParam(':limit', $limit, PDO::PARAM_INT);
    $query->bindParam(':offset', $offset, PDO::PARAM_INT);
    $query->execute();

    $end = "";
    while ($row = $query->fetch(PDO::FETCH_ASSOC))
    {
        if($row['job_app'] == $job_app){
            $end = $end.'<tr class="hoverOver"><td class="td1"><a   href="/jobview.php?id='.$row['id'].'">'.$row['job_comp_loc'].'</a></td>';
            $end = $end.'<td class="td2"><a   href="/jobview.php?id='.$row['id'].'">'.$row['job_desc'].'</a></td>';
            $end = $end.'<td class="td3"><a   href="/jobview.php?id='.$row['id'].'">'.$row['location'].'</a></td></tr>';
        }else if($job_app == 0){
            $end = $end.'<tr class="hoverOver"><td class="td1"><a   href="/jobview.php?id='.$row['id'].'">'.$row['job_comp_loc'].'</a></td>';
            $end = $end.'<td class="td2"><a   href="/jobview.php?id='.$row['id'].'">'.$row['job_desc'].'</a></td>';
            $end = $end.'<td class="td3"><a   href="/jobview.php?id='.$row['id'].'">'.$row['location'].'</a></td></tr>';
        }else{
            return '<td class="td1"></td><td class="td2" style="text-align: center;color: red;">Sorry, there doesnt seem to be any results!</td><td class="td3"></td>';
        }

    }
    return $end;
}

调用函数的.php文件:

$Job_Type = null;
$Location = null;
$Job_App = 0;
if(!$_GET['Job_App'] == null){
    $Job_App = $_GET['Job_App'];
}
if(!$_GET['Job_Type'] == null){
    if(!$_GET['Job_Type'] == 0){
        $Job_Type = $_GET['Job_Type'];
    }
}
if(!$_GET['Location'] == null){
    $Location = $_GET['Location'];
}
//irreleven stuff in between
if(!$_GET['page'] == null){
    $pageno = ($_GET['page']-1)*15;
    if(!$_GET['Job_Type'] == null){
        echo ''.getFilteredList($dbh, 15, $pageno, $Job_App, $Job_Type, $Location);
    }else{
        echo ''.getGenericList($dbh, 15, $pageno, $Job_App);
    }
}else{
    if(!$_GET['Job_Type'] == null){
        echo ''.getFilteredList($dbh, 15, 0, $Job_App, $Job_Type, $Location);
}else{
    echo ''.getGenericList($dbh, 15, 0, $Job_App);
}
}

我是否缺少一些可以正确过滤它们的东西？我自己读了几遍，似乎是对的，但只是没有按预期过滤。

编辑:

我似乎说得不够清楚，它确实成功完成了查询，因为它在 while 循环中返回了 end else 语句。

Answer 1

您在 while 循环中生成错误消息，因此您肯定会得到结果:

while ($row = $query->fetch(PDO::FETCH_ASSOC))
{
    if($row['job_app'] == $job_app){
        $end = $end.'<tr class="hoverOver"><td class="td1"><a   href="/jobview.php?id='.$row['id'].'">'.$row['job_comp_loc'].'</a></td>';
        $end = $end.'<td class="td2"><a   href="/jobview.php?id='.$row['id'].'">'.$row['job_desc'].'</a></td>';
        $end = $end.'<td class="td3"><a   href="/jobview.php?id='.$row['id'].'">'.$row['location'].'</a></td></tr>';
    }else if($job_app == 0){
        $end = $end.'<tr class="hoverOver"><td class="td1"><a   href="/jobview.php?id='.$row['id'].'">'.$row['job_comp_loc'].'</a></td>';
        $end = $end.'<td class="td2"><a   href="/jobview.php?id='.$row['id'].'">'.$row['job_desc'].'</a></td>';
        $end = $end.'<td class="td3"><a   href="/jobview.php?id='.$row['id'].'">'.$row['location'].'</a></td></tr>';
    }else{
        // Here you are in the while loop so you have results!
        return '<td class="td1"></td><td class="td2" style="text-align: center;color: red;">Sorry, there doesnt seem to be any results!</td><td class="td3"></td>';
    }
}

您的问题是 if 条件在任何行中都不匹配。您需要找出原因，例如通过转储行。