How can I combine this search and this pagination script together in PHP, Msqli?

o8codex

NOTE: Ignore the includes they aren't necessary for what I need help with but Hey guys I i'm going to post the code here then and I have a link you guys can visit to understand this better this is an option. THE LINK OK here it goes.

//The index file

<DOCTYPE html>
<html>
<head>
</head>
<body>
<form action="si_search.php" method="GET">
<input type="text" name="q" dir="ltr">
<input type="submit" value="go">
</form>

</body>
</html>


//The Search script  file

<?php
$conn = mysqli_connect("localhost","1234user","1234pw","1234db");

$output = ' ';

if(isset($_GET['q']) && $_GET['q'] !== ' ') {
	$searchq = $_GET['q'];


$searchq = trim(" $searchq ");

$searchq = preg_replace('/\s+/', ' ', $searchq);

$searchq = mysqli_real_escape_string($conn, $searchq);

$searchq = htmlspecialchars($searchq);

if ($searchq === "") {
        header('Location: '.$_SERVER['PHP_SELF']);
die;
}



?>

<!DOCTYPE html>
<html>
  <head>

<title>
Results
</title>

<style>


</style>

  </head>

  <meta name="viewport" content="width=device-width">
<meta charset="UTF-8">

<link rel="stylesheet" type="text/css" href="css/results/desktop.css">
<link rel="stylesheet" type="text/css" href="css/results/mobile.css">

 <body>

<!-- loading Screen -->
<?php include("loading_screen.php"); ?>

<!-- Search Bar -->
 <?php include "index.php"; ?>


<?php
$q = mysqli_query($conn, "SELECT * FROM search_i WHERE keywords LIKE '%$searchq%' OR title LIKE '%$searchq%' ORDER BY title DESC") or die(mysqli_error());

$c = mysqli_num_rows($q);
if($c == 0) {
	$output = '<h2 class="no_results_error_message";><span style="color: red;">No results for:</span><b>"<span style="color: white;">' . $searchq . '</span></h2>"</b>';


}  else {

?>


  <div class="result_section";>
    <h2><span class="for_headline">Results For: </span><span id="result_output"><?php $outputx = "$searchq"; print("$outputx"); ?></span></h2>
    </div>


<div class="box_template";>

<?php 

while($row = mysqli_fetch_array($q)) {

$id = $row['id'];
$title = $row['title'];
$desc = $row['description'];
$images = $row['images'];

?>
<div class="image_and_title_box";>

 <div class="image_box";>
 <a href="<?php echo $row['link']; ?>"><img id="image_content_inside_box" src="<?php echo $row['images']; ?>"> </a>
     </div>



  <div class="box_general_text";>
  <h2 class="box_title"> <a href="<?php echo $row['link']; ?>"><?php echo $row['title']; ?>
    </a>
    </h2>
     <div>
</div>

     <div class="external_box_paragraph";>
  <p id="box_paragraph_content";><?php echo $row['description']; ?></p>
  </div>
  </div>
   </div>

<?php

$output .= '<a href="' . $link . '">

		</a>';


	}
}
} else {
	header("location: ./");
}

print("$output");

mysqli_close($conn);


?>


  </body>
</html>


//The Pagination script  file

<?php

$conn = mysqli_connect("localhost","user1234","1234pw","1234db");

$q =  mysqli_query($conn, "SELECT COUNT(title) FROM search_i");
// total row count
$row = mysqli_fetch_row($q);
$rows = $row[0];
// results displayed per page
$page_rows = 1;
// page number of last page
$last = ceil($rows/$page_rows);
// makes sure $last cannot be less than 1
if($last < 1) {
  $last = 1;
}
// page num
$pagenum = 1;
// get pagenum from URL if it is present otherwise it is 1
if(isset($_GET['q'])) {
  $pagenum = preg_replace('#[^0-9]#', '', $_GET['q']);
}
// makes sure the page number isn't below 1, or more then our $last page
if($pagenum < 1) {
  $pagenum = 1;
}
else if($pagenum > $last) {
  $pagenum = $last;
}
// set the rage of rows to query for the chosen $pagenum
$limit = 'LIMIT ' . ($pagenum - 1) * $page_rows . ',' . $page_rows;
$q = mysqli_query($conn, "SELECT images, title, link FROM search_i ORDER BY title DESC $limit");
// shows the user what page they are on, and the total number of pages
$textline1 = "Search_i";
$textline2 = "Page <b>$pagenum</b> of <b>$last</b>";
// establish $paginationCtrls variable
$paginationCtrls = '';
// if more the 1 page
if($last != 1) {
  if($pagenum > 1) {
    $previous = $pagenum - 1;
    $paginationCtrls .= '<a href="'. $row["link"].'?q='.$previous.'">Previous</a> &nbsp; &nbsp; ';
    // Render clickable number links
    for($i = $pagenum - 4; $i < $pagenum; $i++) {
      if($i > 0) {
        $paginationCtrls .= '<a href="'. $row["link"].'?q='.$i.'">'.$i.'</a> &nbsp; ';
      }
    }
  }
  // render the target page number without a link
  $paginationCtrls .= ''. $pagenum . ' &nbsp; ';
  // render clickable number links that appear on the right
  for($i = $pagenum + 1; $i < $last; $i++) {
    $paginationCtrls .= '<a href="'. $row["link"].'?q='.$i.'">'.$i.'</a> &nbsp; ';
    // allows up to 4 pages
    if($i >= $pagenum + 4) {
      break;
    }
  }
  if($pagenum != $last) {
    $next = $pagenum + 1;
    $paginationCtrls .= ' &nbsp; &nbsp; <a href="'. $row["link"].'?q='. $next .'">Next</a> ';
  }
}
?>
<!DOCTYPE html>
<html>
<head>
  <title>Paging Example</title>

  <style>
    div#pagination_controls {
      font-size:21px;
    }
    div#pagination_controls > a {
      color: #06F;
    }
    div#pagination_controls > a:visited {
      color: #06F;
    }


img {
height: 200px;
width: 125px;
}

.a {
margin-top: 30px;
margin-bottom: 30px;
}

body {
background-color: pink;
font-family: arial;
}

h1 {
color: white;
}

p {
color: white;
}

a {
color: red;
}


  </style>

  <meta title="viewport" content="width=device-width">
<meta charset="UTF-8">

</head>
<body>
<center>
    <h1><?php echo $textline1; ?> </h2>
<?php
while($row = mysqli_fetch_array($q, MYSQLI_ASSOC)) {
  echo "<h3>".$row["title"]."</h1>";
  echo "<img src='".$row["images"]."'>";
echo "<p><a href='".$row["link"]."'>Link</a></p>";
}
// close your database connection
mysqli_close($conn);


?>

  <div>
    <p><?php echo $textline2; ?></p>
    <div title = "pagination_controls"><?php echo $paginationCtrls; ?></div>
  </div>
  </center>
</body>
</html>

dalecosp

I'm not sure how the really big guns do theirs, but here are some concepts I used.

LIKE search is okay ... a full-text is better. But it might be a good idea to figure out your main goal here with the search you've already got. If you have a decently large set and want a very performant search engine, look into things like ElasticSearch or SphinxSearch (the latter has the advantage of being generally "Free" as in "Free Beer", IIRC).
A search page should probably enact the redirect-after-post paradigm. If you implement this like I did (assuming I can explain it), you'll be headed that way.
The POSTed form sets up the search and saves the terms, result count, and other relevant data (we grab a timestamp, the user_id, if any, the IP address, stuff like that) and saves them in the database.
The script then redirects to a GET page, with the searchid given and page=1.
Since the GET page knows its page number (and presumably the number of results/page), it's able to query using a LIMIT statement, say, "LIMIT 0,100" for page 1, "LIMIT 100,100" for page 2, etc.
The core search class I use selects only the ID (primary key) for the entire result set (we limit to 5000 results per search, but an indexed query like that is fast), and then does a 2nd query to get only the 100 entries for the current pageload (after array_slice()ing the result set IDs.
Since the page number is a given (it's in the GET string), pagination is fairly simple.

o8codex

I see what your talking about I know it's haves to do with link related stuff like GET. But how would you do it in this code where would I start I been messing around with the link etc.. but with no success how would you do this in these files? Do you have any example files that you don't mind sharing?

pbismad

Your goal is to run a query that gets the data you want in the order that you want it. Then all you have to do is loop over the query result and output the data the way you want. Have you tried to write a query that contains the WHERE clause due to the search logic and the LIMIT clause due to the pagination logic?

The pagination code also has a query to get the total count of matching rows. It needs the same WHERE clause as the data retrieval query. The easiest way of making sure that both queries contain the same WHERE clause is to build it in a php variable, then use that php variable in both queries.

The pagination links will need to propagate the search term between pages. They would need to have the search term added to them. The best way of doing this would be to use the php http_build_query() function and use any existing get parameters as a starting point and just set the pagination get parameter when building each link.

Also, your search form should be 'sticky' and repopulate the field with any existing search term. This will serve to display what the current search term is and will also let the visitor modify the search without needing to retype the value. Your search form code is a complete html document. When you include this into your main code, it results in an invalid html document. The search form code should only be the search form.

o8codex

I know the terms that you guys are using but can you demonstrate physically? Just to show me what you mean I don't care how your are interpenetrating these files just do it your way if you can I really appreciate it because visually I don't know what you guys are saying sorry i'm just being honest here that's all.

pbismad

You need to attempt each of the things that have been listed, one at a time, then if you have specific questions, problems, or errors with an item, ask for help with it.

For producing the sql query, if you know what each existing query is doing, it should be easy to produce the combined query. Here is a hint, the following is the SELECT query prototype/definition, which shows what you can put into a select query and in what order they must be, with the relevant parts shown in red -

SELECT
[ALL | DISTINCT | DISTINCTROW ]
[HIGH_PRIORITY]
[STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
select_expr [, select_expr ...]
[FROM table_references
[PARTITION partition_list]
[WHERE where_condition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}
[ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[PROCEDURE procedure_name(argument_list)]
[INTO OUTFILE 'file_name'
[CHARACTER SET charset_name]
export_options
| INTO DUMPFILE 'file_name'
| INTO var_name [, var_name]]
[FOR UPDATE | LOCK IN SHARE MODE]]

o8codex

Hello guys I have figured it out. It was really hard but I never give up you guys told me to look at the SQL section so I did. It got me some where kind of but I had to play around with the combine scripts for a long time. So in order to combine the pagination script and the search script I had to modify and remove some areas with new structures to bridge the scripts together, No offense to people that put time to try to help me here or people from other chat room websites but Google didn't help me you tube didn't help me and really nobody from this and other forum websites didn't help me neither really. I had to rely on clues and playing around with the code till I got it to work how I wanted it to work but at least you guys made an attempt to try to help me out so thank you very much for putting the effort at least I really appreciate your time and effort, but any ways here's the finished file pagination with search my version.

//INDEX.PHP ignore the svg it's just w3.org search template (optional*)

<!DOCTYPE html>
<html>
<head>

<meta name="viewport" content="width=device-width">
<meta charset="UTF-8">

<link rel="stylesheet" type="text/css" href="css/i/desktop.css">
<link rel="stylesheet" type="text/css" href="css/i/mobile.css">

<script src="javascript/script.js"></script>

</head>

<body>


<svg xmlns="http://www.w3.org/2000/svg" style="display:none">
  <symbol xmlns="http://www.w3.org/2000/svg" id="sbx-icon-search-13" viewBox="0 0 40 40">
    <path d="M26.804 29.01c-2.832 2.34-6.465 3.746-10.426 3.746C7.333 32.756 0 25.424 0 16.378 0 7.333 7.333 0 16.378 0c9.046 0 16.378 7.333 16.378 16.378 0 3.96-1.406 7.594-3.746 10.426l10.534 10.534c.607.607.61 1.59-.004 2.202-.61.61-1.597.61-2.202.004L26.804 29.01zm-10.426.627c7.323 0 13.26-5.936 13.26-13.26 0-7.32-5.937-13.257-13.26-13.257C9.056 3.12 3.12 9.056 3.12 16.378c0 7.323 5.936 13.26 13.258 13.26z"
    fill-rule="evenodd" />
  </symbol>
  <symbol xmlns="http://www.w3.org/2000/svg" id="sbx-icon-clear-3" viewBox="0 0 20 20">
    <path d="M8.114 10L.944 2.83 0 1.885 1.886 0l.943.943L10 8.113l7.17-7.17.944-.943L20 1.886l-.943.943-7.17 7.17 7.17 7.17.943.944L18.114 20l-.943-.943-7.17-7.17-7.17 7.17-.944.943L0 18.114l.943-.943L8.113 10z" fill-rule="evenodd" />
  </symbol>
</svg>

<div class="center_search_box";>

<div class="move_search_box";>

<form action="query" method="GET" class="searchbox sbx-google">

  <div role="search" class="sbx-google__wrapper">
    <input type="search" name="search" dir="ltr" placeholder="Search" autocomplete="off" required="required" class="sbx-google__input">
    <button type="submit" title="Submit your search query." class="sbx-google__submit">
      <svg role="img" aria-label="Search">
        <use xmlns:xlink="http://www.w3.org/1999/xlink" xlink:href="#sbx-icon-search-13"></use>
      </svg>
    </button>
    <button type="reset" title="Clear the search query." class="sbx-google__reset">
      <svg role="img" aria-label="Reset">
        <use xmlns:xlink="http://www.w3.org/1999/xlink" xlink:href="#sbx-icon-clear-3"></use>
      </svg>
    </button>
  </div>

  </form>

  </div>

  </div>

</body>
</html>



//QUERY.PHP combine search and pagination scripts with semi removed structures and new added structures to make the both scripts work together 


<?php 
$conn = mysqli_connect("localhost","1234user","pw1234","db1234");

//SEARCHX

$output = ' ';

if(isset($_GET['search']) && $_GET['search'] !== ' ') {
	$user_input = $_GET['search'];


if ($user_input === "") {
        header('Location: '.$_SERVER['PHP_SELF']);
die;
}

//PAGINATION
$user_input = $_GET['search'];
$and = "&";


$pagevx = "page=";

//SEARCHX
$user_input = trim(" $user_input ");

$user_input = preg_replace('/\s+/', ' ', $user_input);

$user_input = mysqli_real_escape_string($conn, $user_input);

$user_input = htmlspecialchars($user_input);

//PAGINATION
$page =  mysqli_query($conn, "SELECT COUNT(title) FROM search_i WHERE keywords LIKE '%$user_input%' OR title LIKE '%$user_input%'");
// total row count
$row = mysqli_fetch_row($page);
$rows = $row[0];
// results displayed per page
$page_rows = 1;
// page number of last page
$last = ceil($rows/$page_rows);
// makes sure $last cannot be less than 1
if($last < 1) {
  $last = 1;
}
// page num
$pagenum = 1;
// get pagenum from URL if it is present otherwise it is 1
if(isset($_GET['page'])) {
  $pagenum = preg_replace('#[^0-9]#', '', $_GET['page']);
}
// makes sure the page number isn't below 1, or more then our $last page
if($pagenum < 1) {
  $pagenum = 1;
}
else if($pagenum > $last) {
  $pagenum = $last;
}
// set the rage of rows to query for the chosen $pagenum
$limit = 'LIMIT ' . ($pagenum - 1) * $page_rows . ',' . $page_rows;
$page = mysqli_query($conn, "SELECT keywords, images, title, link, description FROM search_i WHERE keywords LIKE '%$user_input%' OR title LIKE '%$user_input%' ORDER BY title DESC $limit");



// establish $paginationCtrls variable
$paginationCtrls = '';
// if more the 1 page
if($last != 1) {
  if($pagenum > 1) {
    $previous = $pagenum - 1;
    $paginationCtrls .= '<a href="'. $row["link"].'?search='.$user_input.$and.$pagevx.$previous.'"><span class="pag_back_arrow"; style="text-decoration: none;"><</a></span> &nbsp; &nbsp; ';
    // Render clickable number links
    for($i = $pagenum - 4; $i < $pagenum; $i++) {
      if($i > 0) {
        $paginationCtrls .= '<a href="'. $row["link"].'?search='.$user_input.$and.$pagevx.$i.'">'.$i.'</a> &nbsp; ';
      }
    }
  }
  // render the target page number without a link
  $paginationCtrls .= ''. $pagenum . ' &nbsp; ';
  // render clickable number links that appear on the right
  for($i = $pagenum + 1; $i < $last; $i++) {
    $paginationCtrls .= '<a href="'. $row["link"].'?search='.$user_input.$and.$pagevx.$i.'">'.$i.'</a> &nbsp; ';
    // allows up to 4 pages
    if($i >= $pagenum + 4) {
      break;
    }
  }
  if($pagenum != $last) {
    $next = $pagenum + 1;
    $paginationCtrls .= ' &nbsp; <a href="'. $row["link"].'?search='.$user_input.$and.$pagevx.$next.'"><span class="pag_next_arrow"; style="text-decoration: none;">></a></span> ';
  }
}

//SEARCHX

?>

<!DOCTYPE html>
<html>
  <head>

<title>
Results
</title>

  </head>

  <meta name="viewport" content="width=device-width">
<meta charset="UTF-8">

<link rel="stylesheet" type="text/css" href="css/query/desktop.css">
<link rel="stylesheet" type="text/css" href="css/query/mobile.css">

 <body>

<!-- loading Screen -->
<?php include("loading_screen.php"); ?>

<!-- Search Bar -->
 <?php include("x/index.php"); ?>


<?php
//SEARCHX
$c = mysqli_num_rows($page);
	if($c == 0) {
		$output = '<h2 class="no_results_error_message";><span style="color: red;">No results for:</span><b><span style="color: white;">' . $user_input . '</span></h2></b>';


}  else {

?>


  <div class="result_section";>
    <h2><span class="for_headline">Results For: </span><span id="result_output"><?php $outputx = "$user_input"; print("$outputx"); ?></span></h2>
    </div>


<div class="box_template";>

<?php

// shows the user what page they are on, and the total number of pages
$textline1 = "Search_i";
$textline2 = "Page <b>$pagenum</b> of <b>$last</b>";

while($row = mysqli_fetch_array($page, MYSQLI_ASSOC)) {

//SEARCHX
$id = $row['id'];
$title = $row['title'];
$desc = $row['description'];
$images = $row['images'];

?>
<div class="image_and_title_box";>

 <div class="image_box";>
 <a href="<?php echo $row['link']; ?>"><img id="image_content_inside_box" src="<?php echo $row['images']; ?>"> </a>
     </div>



  <div class="box_general_text";>
  <h2 class="box_title"> <a href="<?php echo $row['link']; ?>"><?php echo $row['title']; ?>
    </a>
    </h2>
     <div>
</div>

     <div class="external_box_paragraph";>
  <p id="box_paragraph_content";><?php echo $row['description']; ?></p>
  </div>
  </div>
   </div>

<?php

$output .= '<a href="' . $link . '">

		</a>';


	}
}
} else {
	header("location: ./");
}

print("$output");

mysqli_close($conn);

?>

<!-- PAGINATION -->
<!--shows the user what page they are on, and the total number of pages -->
	<p><?php //echo $textline1 = "Search_i"; ?></p>
	 <p id="page_of";><?php //echo $textline2; ?></p>

<div class="pagination_controls"><?php echo $paginationCtrls; ?></div>


  </body>
</html>