将php变量绑定到pdo查询
将php变量绑定到pdo查询
提问于 2017-09-01 12:43:22
到目前为止，我的数据库记录可以搜索并分页。我在集成按链接参数设置的单独排序函数时遇到了问题。例如。?sort=id, ?sort=last_name etc..。我可以用isset($_GET['sort']捕获参数，但在将变量绑定到我的查询时遇到困难。
这是我的密码
$pdo_conn = new PDO( 'mysql:host=localhost;dbname=database_name', $database_username, $database_password );
$search_keyword = '';
if(!empty($_POST['search']['keyword'])) {
$search_keyword = $_POST['search']['keyword'];
}
$sql = 'SELECT * FROM submissions WHERE company_name LIKE :keyword OR attorney_last_name LIKE :keyword OR attorney_first_name LIKE :keyword ORDER BY id ASC ';
/* Pagination Code starts */
$per_page_html = '';
$page = 1;
$start=0;
if(!empty($_POST["page"])) {
$page = $_POST["page"];
$start=($page-1) * ROW_PER_PAGE;
}
$limit=" limit " . $start . "," . ROW_PER_PAGE;
$pagination_statement = $pdo_conn->prepare($sql);
$pagination_statement->bindValue(':keyword', '%' . $search_keyword . '%', PDO::PARAM_STR);
$pagination_statement->execute();
$row_count = $pagination_statement->rowCount();
if(!empty($row_count)){
$per_page_html .= "<div style='text-align:center;margin:20px 0px;'>";
$page_count=ceil($row_count/ROW_PER_PAGE);
if($page_count>1) {
for($i=1;$i<=$page_count;$i++){
if($i==$page){
$per_page_html .= '<input type="submit" name="page" value="' . $i . '" class="btn-page current" />';
} else {
$per_page_html .= '<input type="submit" name="page" value="' . $i . '" class="btn-page" />';
}
}
}
$per_page_html .= "</div>";
}
$query = $sql.$limit;
$pdo_statement = $pdo_conn->prepare($query);
$pdo_statement->bindValue(':keyword', '%' . $search_keyword . '%', PDO::PARAM_STR);
$pdo_statement->execute();
$result = $pdo_statement->fetchAll();回答 1
Stack Overflow用户
回答已采纳
发布于 2017-09-01 12:51:31
$sort = isset($_GET['sort']) ? $_GET['sort'] : null;
//to prevent SQL injection
if(!in_array($sort, array('id', 'last_name'))) $sort = null;
//use default field to sort
if(!$sort) $sort = 'id';
//build the SQL
$sql = "SELECT * FROM submissions
WHERE company_name LIKE :keyword
OR attorney_last_name LIKE :keyword
OR attorney_first_name LIKE :keyword
ORDER BY `$sort` ASC
";页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/46000534
复制相关文章
相似问题
腾讯云开发者
