php/sql-模糊搜索结果分页

yr9zkbsy  于 2021-06-18  发布在  Mysql
关注(0)|答案(1)|浏览(299)

我已经为我的网站上的产品创建了一个搜索功能,并且我尝试设置分页,这样它就不仅仅是一个长长的结果列表。所以我从这样开始:

**注意:我刚换了 $_GET['search_term']'whatever' 举个例子,我有 var_dump() 我有一个函数,用于显示给定数组中每个id的乘积。

$term = 'whatever'; //$_GET['search_term'];
$new_term = '%'.$term.'%';

if(isset($_GET['page'])){
    $page = $_GET['page'];
}else{
    $page = 1;
}
$per_page = 20;
$last_page = ceil($resultCount/$per_page);
if($page<1){
    $page = 1;
}else if($page>$last_page){
    $page = $last_page;
}
$pagination = "";
$limit = "LIMIT ".($page-1)*$per_page.",".$per_page;
if($last_page!=1){
    if($page!=1){
        $prev = $page-1;
        $pagination .= "<a class='pagination' href='store'><<</a>";
        $pagination .= "<a class='pagination' href='store/$prev'><</a>";
    }
    for($i=$page-2; $i<=$page+2; $i++){
        if($i>0 && $i<=$last_page){
            if($i == $page){
                $pagination .= "<a class='pagination selected'>$i</a>";
            }else{
                $pagination .= "<a class='pagination' href='store/$i'>$i</a>";
            }
        }
    }
    if($page!=$last_page){
        $next = $page+1;
        $pagination .= "<a class='pagination' href='store/$next'>></a>";
        $pagination .= "<a class='pagination' href='store/$last_page'>>></a>";
    }
}

if(isset($term)){
    echo $pagination;
    $ids = [];
    $params = [$new_term];
    $sql = "SELECT * FROM products WHERE name LIKE ? $limit";
    $stmt = DB::run($sql,$params);
    $resultCount = $stmt->rowCount();
    if($resultCount > 0){
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)){
            $id = $row["pro_id"];
            $params3 = [$id];
            $sql3 = "SELECT * FROM products WHERE id=?";
            $stmt3 = DB::run($sql3,$params3);
            while($row = $stmt3->fetch(PDO::FETCH_ASSOC)){
                $id = $row["id"];
                array_push($ids,$id);
            }
        }
        var_dump($ids);
    }
    echo $pagination;
}

这很好,但后来我想让它成为一个模糊的搜索,所以我做了:

$term = 'whatever'; //$_GET['search_term'];
$new_term = '%'.$term.'%';

$params = [$new_term];
$sql = "SELECT * FROM products WHERE name LIKE ?";
$stmt = DB::run($sql,$params);
$resultCount = $stmt->rowCount();
if($resultCount < 1){
    $sql = "SELECT * FROM products";
    $stmt = DB::run($sql);
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)){
        $id = $row["pro_id"];
        $result = $row[$lang];
        similar_text($term,$result,$similarity);
        $similar_array[$similarity][] = $id;
    }
    $closest_match = array_keys($similar_array);
    rsort($closest_match);
    $match_count = count($closest_match);
    $similar_ids = [];
    for($i=0; $i<$match_count; $i++){
        foreach($similar_array[$closest_match[$i]] as $id){
            array_push($similar_ids,$id);
        }
    }
    $resultCount = count($similar_ids);
}

if(isset($_GET['page'])){
    $page = $_GET['page'];
}else{
    $page = 1;
}
$per_page = 20;
$last_page = ceil($resultCount/$per_page);
if($page<1){
    $page = 1;
}else if($page>$last_page){
    $page = $last_page;
}
$pagination = "";
$limit = "LIMIT ".($page-1)*$per_page.",".$per_page;
if($last_page!=1){
    if($page!=1){
        $prev = $page-1;
        $pagination .= "<a class='pagination' href='store'><<</a>";
        $pagination .= "<a class='pagination' href='store/$prev'><</a>";
    }
    for($i=$page-2; $i<=$page+2; $i++){
        if($i>0 && $i<=$last_page){
            if($i == $page){
                $pagination .= "<a class='pagination selected'>$i</a>";
            }else{
                $pagination .= "<a class='pagination' href='store/$i'>$i</a>";
            }
        }
    }
    if($page!=$last_page){
        $next = $page+1;
        $pagination .= "<a class='pagination' href='store/$next'>></a>";
        $pagination .= "<a class='pagination' href='store/$last_page'>>></a>";
    }
}

if(isset($term)){
    echo $pagination;
    $ids = [];
    $params = [$new_term];
    $sql = "SELECT * FROM products WHERE name LIKE ? $limit";
    $stmt = DB::run($sql,$params);
    $resultCount = $stmt->rowCount();
    if($resultCount > 0){
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)){
            $id = $row["pro_id"];
            $params3 = [$id];
            $sql3 = "SELECT * FROM products WHERE id=?";
            $stmt3 = DB::run($sql3,$params3);
            while($row = $stmt3->fetch(PDO::FETCH_ASSOC)){
                $id = $row["id"];
                array_push($ids,$id);
            }
        }
        var_dump($ids);
    }else{
        var_dump($similar_ids);
    }
    echo $pagination;
}

也许有更好的方法,但这就是我所拥有的。我的问题是,如何才能让分页工作在这里的模糊结果( $similar_ids )? 我在想一些函数,可以根据页码拼接数组,但我不知道该怎么做。

rt4zxlrg

rt4zxlrg1#

这并不能回答你的问题,但我还是要说:
仔细看,在处理 $limit .
另外,使用ApacheSolr或elasticsearch之类的搜索索引几乎可以免费进行分页。你可能想调查一下。建立一个专门的索引是另一种蠕虫,是的,但是你也会有更多更好的选择来处理模糊搜索部分。

相关问题