Imagine I've got a database with lots of data, from which users can search.
The result of a typical search is generally around 20-100 rows, which are then paginated (20 rows per page).
I've thought of two approaches to handle the navigation for these pages and would like to know if there are any pros and/or cons to these and if there are any better alternatives.
-
Query once, store results in
$_SESSION
variable and filter rows according to current page. The reason I came up with this was to make the data retrieval once, without having to connect to the database for every page the user navigates. I don't know if it's better or worse than the other alternative I've come up with.session_start(); $search = rawurldecode($_GET['search']); //search word $interval = rawurldecode($_GET['interval']); //rows per page $page = rawurldecode($_GET['page']); //page $min_row = $interval * ($page-1)+1; $max_row = $interval * $page; //query if (no results stored or first page) && the current search is not the previous search if((empty($_SESSION['SEARCH_RESULTS']) || $page == 1) && $_SESSION['SEARCH_RESULTS']['TERM'] != $search){ $_SESSION['SEARCH_RESULTS'] = array(); $_SESSION['SEARCH_RESULTS']['TERM'] = $search; $query = "exec usp_Search '$search'"; $dbh = new DBH; $dbh->Connect()->Query($query); while($row = $dbh->Fetch_Array()){ $_SESSION['SEARCH_RESULTS']['ROWS'][] = $row; } } for($j = 0; $j < count($_SESSION['SEARCH_RESULTS']['ROWS']); $j++){ $row = $_SESSION['SEARCH_RESULTS']['ROWS'][$j]; //ignore all other rows not on the page if($j < ($min_row-1) || $j > $max_row) continue; //print stuff }
-
Query page by page. The query and the pagination is pretty straightforward.
//Query $search = rawurldecode($_GET['search']); $interval = rawurldecode($_GET['interval']); $page = rawurldecode($_GET['page']); $min_row = $interval * ($page-1)+1; $max_row = $interval * $page; $query = "exec usp_Search '$search', $min_row, $max_row"; $dbh = new DBH; $dbh->Connect()->Query($query); while($row = $dbh->Fetch_Array()){ //print stuff }
SQL procedures from the alternatives
-
Is just a procedure with a SELECT query
SELECT COL1, COL2, COL... FROM TABLE1 WHERE ( COL1 LIKE '%'+@search+'%' OR COL2 LIKE '%'+@search+'%' OR COL... LIKE '%'+@search+'%' )
-
Is a procedure that creates a temp table and then selects rows from variables start to end.
SELECT COL1, COL2, COL..., ROW_NUMBER() OVER (ORDER BY COL1) AS [ROW_NUMBER] INTO #result FROM TABLE1 WHERE ( COL1 LIKE '%'+@search+'%' OR COL2 LIKE '%'+@search+'%' OR COL... LIKE '%'+@search+'%' ) SELECT COL1, COL2, COL... FROM #result WHERE ROW_NUMBER BETWEEN @row_start AND @row_end