我正在尝试在我的网站上添加高级搜索功能。
就目前而言，除了我必须比较同一列中的多个值的时候，其他一切都正常工作
例如，我希望能找回所有同时属于神秘客户和调查人员类型的候选人。
所以我第一次在mysql接口中执行这个查询，检查是否收集了所有的东西，但是只考虑了第一个条件，我只得到神秘客户类型的候选项：
从候选项中选择*，其中候选项类型为“%mysterycustomer%”，候选项类型为“%investors%”；
所以我做了一些研究，在接线员那里遇到了。我用这个操作符修改我的查询。问题是此错误被发回给我：
php消息：php致命错误：uncaughtpdoexception:sqlstate[42000]：语法错误或访问冲突：1064您的sql语法有错误；检查与您的mariadb服务器版本相对应的手册，以了解在////search中第1行的“where zipcode in（“east”）limit？”附近使用的正确语法。php:106
$('.advancedSearchingContent').on('click', function()
{
var data = [];
var index = "";
$('input:checked').each(function(element)
{
//index += $(this).closest('div').attr('id');
data.push($(this).val());
});
var nb_per_page = $('.entriesData').val();
var search = $('#searchContent').val();
var page = 1;
var sort = $('.sortList').val();
var zipcode = data;
loadContent(nb_per_page, search, page, sort, zipcode);
});
function loadContent(nb_per_page, search, page, sort, zipcode)
{
var p = {};
p['nb_per_page'] = nb_per_page;
p['search'] = search;
p['page'] = page;
p['sort'] = sort;
p['zipcode'] = zipcode;
$.post("search.php", p, function(data)
{
$("#result").html(data);
});
}.sidenav
{
height: 100%;
width: 0;
position: fixed;
z-index: 1;
top: 0;
left: 0;
background-color: #778899;
overflow-x: hidden;
transition: 0.5s;
padding-top: 60px;
}
.sidenav a
{
padding: 8px 8px 8px 32px;
text-decoration: none;
font-size: 25px;
color: #818181;
display: block;
transition: 0.3s;
}
.sidenav a:hover
{
color: #f1f1f1;
}
.sidenav .closebtn
{
position: absolute;
top: 0;
right: 25px;
font-size: 36px;
margin-left: 50px;
}
@media screen and (max-height: 450px)
{
.sidenav
{
padding-top: 15px;
}
.sidenav a
{
font-size: 18px;
}
}
.sidenav > div > label
{
font-size: 20px;
width: 100%;
}
.sidenav > div > input
{
width: 100%;
}
.advancedSearchingContent:hover
{
background-color: white;
}
.sidenav > div
{
width: 300px;
margin-left: 10px;
}<html>
<head>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.1/css/bootstrap.min.css" integrity="sha384-WskhaSGFgHYWDcbwN70/dfYBj47jz9qbsMId/iRN3ewGhXQFZCSftd1LZCfmhktB" crossorigin="anonymous">
<link rel="stylesheet" href="https://use.fontawesome.com/releases/v5.2.0/css/all.css" integrity="sha384-hWVjflwFxL6sNzntih27bfxkr27PmbbK/iSvJ+a4+0owXq79v+lsFkW54bOGbiDQ" crossorigin="anonymous">
</head>
<body>
<div class="form-group">
<div class="input-group">
<input type="text" name="searchContent" id="searchContent" placeholder="Search a candidacy" class="form-control" />
<button class="btn btn-outline-success my-2 my-sm-0 searchContent" title="Search"><i class="fa fa-search"></i></button>
<button class="btn btn-outline-info resetContent" title="Reset content"><i class="fas fa-sync-alt" id="iconReset"></i>
</button>
<button class="btn btn-outline-secondary advancedSearching" title="advanced Search"><i class="fas fa-search-plus" id="iconAdvancedSearching"></i></button>
</div>
</div>
<section class="dropdownList">
<label>Display</label>
<div class="dropdownEntries">
<select name="entriesData" class="entriesData">
<option value="5" selected>5</option>
<option value="10">10</option>
<option value="20">20</option>
<option value="50">50</option>
<option value="100">100</option>
</select>
</div>
<label>results</label>
</section>
<section class="dropdownSortList">
<label>Sort by</label>
<div class="sortEntries">
<select name="sortList" class="sortList">
<option value="lastName" selected>lastName</option>
<option value="firstName">firstName</option>
<option value="birthday">Âge</option>
<option value="zipcode">zipcode</option>
<option value="scheduleRange">scheduleRange</option>
<option value="phoneNumber">phoneNumber</option>
<option value="email">Email</option>
<option value="candidacyType">candidacyType</option>
</select>
</div>
</section>
<div id="result">
<?php include('search.php');?>
</div>
<div id="zipContent"></div>
<div id="mySidenav" class="sidenav">
<a href="javascript:void(0)" class="closebtn">×</a>
<div class="row" id="zipcode">
<label for="advancedSearchingZipcode">zipcode : </label>
<input type="checkbox" name="advancedSearchingZipcode[]" value="North">North
<input type="checkbox" name="advancedSearchingZipcode[]" value="South">South
<input type="checkbox" name="advancedSearchingZipcode[]" value="West">West
<input type="checkbox" name="advancedSearchingZipcode[]" value="East">East
</div>
<div class="row" id="age">
<label for="advancedSearchingAge">Âge between :</label>
<input type="text" name="advancedSearchingAge[]">
<label for="advancedSearchingAge">And : </label>
<input type="text" name="advancedSearchingAge[]">
</div>
<div class="row" id="scheduleRange">
<label for="advancedSearchingScheduleRange">Schedule Range between: </label>
<input type="text" name="advancedSearchingScheduleRange[]" id="advancedSearchingScheduleRange">
<label for="advancedSearchingScheduleRange">And :</label>
<input type="text" name="advancedSearchingScheduleRange[]" id="advancedSearchingScheduleRange2">
</div>
<div class="row" id="candidacyType">
<label for="candidacyType">candidacyType :</label>
<input type="checkbox" name="advancedSearchingCandidacyType[]" value="mysteryCustomer">mysteryCustomer
<input type="checkbox" name="advancedSearchingCandidacyType[]" value="investigationUnit">investigationUnit
<input type="checkbox" name="advancedSearchingCandidacyType[]" value="investigators">investigators
</div>
<div class="row">
<button class="btn btn-outline-success my-2 my-sm-0 advancedSearchingContent" title="Search"><i class="fa fa-search"></i></button>
</div>
</div>
</body><?php
require('config/config.php');
if(!isset($_SESSION))
{
session_start();
}
if(isset($_SESSION['flash']))
{
foreach($_SESSION['flash'] as $type => $message)
{
echo $message;
}
unset($_SESSION['flash']);
}
$nb_per_page = !empty($_POST['nb_per_page']) ? $_POST['nb_per_page'] : 5;
$search = !empty($_POST['search']) ? $_POST['search'] : "";
$p = !empty($_POST['page']) ? $_POST['page'] : 1;
$sort = !empty($_POST['sort']) ? $_POST['sort'] : "lastName";
$zipcode = !empty($_POST['zipcode']) ? $_POST['zipcode'] : "";
$output = "";
$result = getList($nb_per_page, $search, $p, $sort, $zipcode, $cnx);
showList($result['list']);
pagination($result['nb_pages'], $p);
function getList($nb_per_page, $search, $p, $sort, $zipcode, $cnx)
{
$nb_per_page = intval($nb_per_page);
$start = intval(($p-1) * $nb_per_page);
$where = "";
if($search != "")
{
$where .= "WHERE lastName LIKE '%".$search."%' OR firstName LIKE '%".$search."%' OR birthday LIKE '%".$search."%' OR zipcode LIKE '%".$search."%' OR scheduleRange LIKE '%".$search."%' OR phoneNumber LIKE '%".$search."%' OR email LIKE '%".$search."%' OR candidacyType LIKE '%".$search."%'";
}
if($sort != "")
{
$order .= "ORDER BY ".$sort." ";
}
if($zipcode != "")
{
if($where != "")
{
$checkbox .= "AND zipcode IN (\"".$zipcode."\")";
}
else
{
$checkbox .= "WHERE zipcode IN (\"".$zipcode[0]."\")";
}
}
$query = $cnx->prepare("SELECT * FROM candidacies ".$where.$order.$checkbox." LIMIT :start, :nb_per_page");
$query->bindValue(':start', $start, PDO::PARAM_INT);
$query->bindValue(':nb_per_page', $nb_per_page, PDO::PARAM_INT);
$query->execute();
$list = $query->fetchAll(PDO::FETCH_ASSOC);
$query = $cnx->prepare('SELECT COUNT(id) AS totalResults FROM candidacies '.$where.' ');
$query->execute();
$result = $query->fetch(PDO::FETCH_OBJ);
$nb = $result->totalResults;
$nb_pages = ceil($nb / $nb_per_page);
if($nb_pages < 1)
{
$nb_pages = 1;
}
return array(
"list" => $list,
"nb" => $nb,
"nb_pages" => $nb_pages
);
}
function showList($list)
{
$output = "";
if(sizeof($list) > 0)
{
$output .= '
<table class="table table-striped" id="candidaciesTable">
<thead class="thead-light">
<tr>
<th scope="col">lastName</th>
<th scope="col">firstName</th>
<th scope="col">Âge</th>
<th scope="col">Zipcode</th>
<th scope="col">Schedule Range</th>
<th scope="col">phoneNumber</th>
<th scope="col">Email</th>
<th scope="col">TcandidacyType</th>
<th scope="col">Action</th>
</tr>
</thead>
<tbody>
';
foreach($list as $key => $value)
{
$currentDate = date("Y");
$currentBirthday = substr($value['birthday'], 0, 4);
$birthday = $currentDate - $currentBirthday;
$output .= "
<tr>
<td>".htmlspecialchars($value["lastName"])."</td>
<td>".htmlspecialchars($value["firstName"])."</td>
<td>".htmlspecialchars($birthday)."</td>
<td>".htmlspecialchars($sort["zipcode"])."</td>
<td>".htmlspecialchars($value["scheduleRange"])."</td>
<td>".htmlspecialchars($value["phoneNumber"])."</td>
<td>".htmlspecialchars($value["email"])."</td>
<td>".htmlspecialchars($search["candidacyType"])."</td>
<td>
<div class='btn-group'>
<button class='btn btn-light btnViewCandidacy' id=".$value['id'].">
<i class='far fa-eye'></i>
</button>
<button class='btn btn-info' id='btnEditCandidacy' onclick='editCandidacy(".$value['id'].")'>
<i class='far fa-edit'></i>
</button>
<button class='btn btn-danger' id='btnDeleteCandidacy' onclick='deleteCandidacy(".$value['id'].")'>
<i class='far fa-trash-alt'></i>
</button>
<button class='btn btn-secondary' id='btnZipContent' onclick='zipExtract(".$value['id'].")'>
<i class='fas fa-file-archive'></i>
</button>
</div>
</td>
</tr>
";
}
$output .= "
</tbody>
</table>";
$output .= '
<div class="modal" tabindex="-1" role="dialog" id="candidacyModal">
<div class="modal-dialog" role="document">
<div class="modal-content">
<div class="modal-header">
<h5 class="modal-title">Candidature</h5>
<button type="button" class="close" data-dismiss="modal" aria-label="Close">
<span aria-hidden="true">×</span>
</button>
</div>
<div class="modal-body" id="modal-body">
</div>
<div class="modal-footer">
<button type="button" class="btn btn-secondary" data-dismiss="modal">Close</button>
</div>
</div>
</div>
</div>';
$output .= '
<div class="modal" tabindex="-1" role="dialog" id="deleteCandidacyModal">
<div class="modal-dialog" role="document">
<div class="modal-content">
<div class="modal-header">
<h5 class="modal-title">Candidature</h5>
<button type="button" class="close" data-dismiss="modal" aria-label="Close">
<span aria-hidden="true">×</span>
</button>
</div>
<div class="modal-bodyCandidacy">
<p></p>
</div>
<div class="modal-footer">
<button type="button" class="btn btn-primary" id="confirmDeletion">Supprimer</button>
<button type="button" class="btn btn-secondary" data-dismiss="modal">Close</button>
</div>
</div>
</div>
</div>';
/*$output .= "
<div class='modal' tabindex='-1' role='dialog' id='advancedSearchingModal'>
<div class='modal-dialog' role='document'>
<div class='modal-content'>
<div class='modal-header'>
<h5 class='modal-titleadvancedSearching'>Recherche avancée</h5>
<button type='button' class='close' data-dismiss='modal' aria-label='Close'>
<span aria-hidden='true'>×</span>
</button>
</div>
<div class='modal-body' id='modal-bodyadvancedSearching'>
</div>
<div class='modal-footer'>
<button type='button' class='btn btn-secondary' data-dismiss='modal'>Close</button>
</div>
</div>
</div>
</div>";*/
}
else
{
$output .= "<div class='alert alert-info' style='margin-top: 20px'>No results found !</div>";
}
echo $output;
}
function pagination($nb_pages, $page)
{
$output = "";
$output .= "<div class='pagination-centered'>";
for($i = 1; $i <= $nb_pages; $i++)
{
$class = "";
if($page == $i){
$class = "active";
}
$output .= '<span class="paginationLink '.$class.'" id="'.$i.'">'.$i.'</span>';
}
$output .= "</div>";
echo $output;
}
?>
<script>
$(document).ready(function()
{
$('.btnViewCandidacy').on('click', function ()
{
var candidacyId = $(this).attr('id');
$.ajax({
url:"viewCandidacy.php",
method:"get",
data:{candidacy:candidacyId},
success:function(data)
{
$('#modal-body').html(data);
$('#candidacyModal').modal("show");
}
});
});
$('.paginationLink').on('click', function()
{
var page = $(this).attr('id');
var nb_per_page = $('.entriesData').val();
var search = $('#searchContent').val();
var sort = $('.sortList').val();
//loadPagination(page);
loadContent(nb_per_page, search, page, sort);
});
$('#candidacyModal').on('shown.bs.modal', function()
{
var urlCoveringLetter = $('#handleCoveringLetter').attr('href');
var urlCurriculumVitae = $('#handleCurriculumVitae').attr('href');
if(urlCoveringLetter)
{
}
else
{
$('#handleCoveringLetter').css('color', 'red');
}
if(urlCurriculumVitae)
{
}
else
{
$('#handleCurriculumVitae').css('color', 'red');
}
});
});
</script>
1条答案
按热度按时间ljsrvy3e1#
你的问题是：
$query = $cnx->prepare("SELECT * FROM candidacies ".$where.$checkbox.$order." LIMIT :start, :nb_per_page");