how to create search functionality in php and mysql and the search result will be on another page apart from the search page

mahenda · May 13, 2019

The result pages is supposed to have pagination like google help me please

chhorn · May 13, 2019

Start with a research for `php mysql tutorial` and present your attempts.

ginerjm · May 13, 2019

The first thing is to write the script. Put out a form that perhaps asks the user what he/she wants in the results. Assign a new script name to the action attribute of your form tag. Then accept that input from the form in the new script and build your query statement using whatever arguments the user chose to give you.

Execute the query and begin a loop to process each row of the results. Now you are going to build the presentation in whatever fashion you imagine it to be. This will probably use an html table structure. Assign all of this output to a single php variable. To be more detailed, start the output of the table with the actual 'table' tag and the column heading tags and then start the loop. For each row, assign the elements to that same php variable and continue until the loop finishes. Then add even more "table ending" code to that php variable.

Then output your new html page from the beginning and at the place you want your results to show up place that php variable and then finish up the web page and exit the script.

Notice how I don't intermingle the html and php code except in the loop where I create an html table row for each iteration of the loop. All of the html stuff is done at the end which include the doctype tag, the meta tags, the css stuff and the <body> tag and some headings and eventually the php variable that contains the table you built.

mahenda · May 13, 2019

for example i have index.php as my home page it is already having some information (blog like structure)

<!DOCTYPE html>
<html>
<head>
<title>Home</title>
</head>
<body>
<!--navigation here-->

	
<!--search here-->
<form action="search.php" method="GET">
<input type="text" placeholder="Enter movie name"    name="search">
<button type="submit" value="search" title="search"></button>
</form>
<!--default content here--> 
</body>
</html>

on the above code i want to implement search from mysql database so when user search and submit the default content disappear and new search page with search result appear if there is more than 5 result it will create pagenation

this is table mysql table

CREATE TABLE movies (
Id INT(11) NOT NULL UNIQUE AUTO_INCREMENT,
cover VARCHAR(255) NOT NULL,
name CHAR(25) NOT NULL,
genre CHAR(25) NOT NULL,
producer CHAR(25) NOT NULL,
shortDescription CHAR(25) NULL,
PRIMARY KEY (Id)
);

what about php code

<?php

include_once ('conn.php');

$sql = "SELECT *FROM movies";

$result = mysqli_query($conn, $sql);

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

echo ' <div>

<span>'.$row['producer'].'</span>

<p>'.$row['shortDescription'].'</p>

</div> ';

} ?>

Barand · May 13, 2019

The search value that the user enters will be available to search.php as $_GET['search'].

If this has a value then you need to add a WHERE clause to your query to limit the results to just those rows that the user wants.

mahenda · May 14, 2019

17 hours ago, mahenda said:
for example i have index.php as my home page it is already having some information (blog like structure)
<!DOCTYPE html>
<html>
<head>
<title>Home</title>
</head>
<body>


	

<form action="search.php" method="GET">
<input type="text" placeholder="Enter movie name"    name="search">
<button type="submit" value="search" title="search"></button>
</form>
 
</body>
</html>
on the above code i want to implement search from mysql database so when user search and submit the default content disappear and new search page with search result appear if there is more than 5 result it will create pagenation

this is table mysql table

CREATE TABLE movies (
Id INT(11) NOT NULL UNIQUE AUTO_INCREMENT,
cover VARCHAR(255) NOT NULL,
name CHAR(25) NOT NULL,
genre CHAR(25) NOT NULL,
producer CHAR(25) NOT NULL,
shortDescription CHAR(25) NULL,
PRIMARY KEY (Id)
);

what about php code

<?php

include_once ('conn.php');

$sql = "SELECT *FROM movies";

$result = mysqli_query($conn, $sql);

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

echo ' <div>

<span>'.$row['name'].'</span>

<span>'.$row['producer'].'</span>

<p>'.$row['shortDescription'].'</p>

</div> ';

} ?>

<?php

if(isset($_POST['submit']) && isset($_POST['submit']) !=""){
include_once('conn.php');
$movieName = $_POST['search'];

$sql = "SELECT *FROM movies WHERE name LIKE %$movieName% OR producer LIKE %$movieName% ";

$result = mysqli_query($conn, $sql);

while($rows = mysqli_fetch_all($result, MYSQLI_ASSOC)){

echo ' <div>

<span>'.$rows['producer'].'</span>

<p>'.$rows['shortDescription'].'</p>

</div> ';

}} ?>

undefined index error occur and mysqli_fetch_all() expects parameter 1 to be mysqli_result, boolean given in C:\xampp\htdocs\tmovies\func\searh.php on line 7

chhorn · May 14, 2019

So your statement is invalid, check for quotes. Also you are building an SQL injection vulnerability, so everybody can easily delete your database.

Prepared Statements would solve both problems, just have a research for it.

Edited May 14, 2019 by chhorn

Barand · May 14, 2019

18 hours ago, Barand said:

The search value that the user enters will be available to search.php as $_GET['search'].

Your HTML specifies a form method = 'GET' so why are you trying to use $_POST['search'] ?

mahenda · May 14, 2019

8 hours ago, chhorn said:

So your statement is invalid, check for quotes. Also you are building an SQL injection vulnerability, so everybody can easily delete your database.

Prepared Statements would solve both problems, just have a research for it.

how to prevent against sql injection

mahenda · May 14, 2019

7 hours ago, Barand said:

Your HTML specifies a form method = 'GET' so why are you trying to use $_POST['search'] ?

thanks i got it but how can i make good search functionality and prevent from any injectiction i.e sql and script injection .

ginerjm · May 14, 2019

read up on using prepared queries - RTFM!!

mahenda · May 15, 2019

<?php

if(isset($_GET['submit']) && isset($_GET['submit']) !=""){
include_once('conn.php');
$movieName = $_GET['search'];

$escapeString = mysqli_real_escape_string($conn, $movieName );

$resultPerPage = 8;
if (isset($_GET["page"]))
{ $page = $_GET["page"];
} else
{
$page=1;
}
$startPage = ($page-1) * $resultPerPage;

$sql = "SELECT *FROM movies WHERE name LIKE %$escapeString% OR producer LIKE %$escapeString% ";

$result = mysqli_query($conn, $sql);

while($rows = mysqli_fetch_all($result, MYSQLI_ASSOC)){

echo ' <div>

<span>'.$rows['producer'].'</span>

<p>'.$rows['shortDescription'].'</p>

</div> ';

}} ?>

<?php
$cSql = "SELECT COUNT(moviesId) FROM movies";
$cResult = mysqli_query($conn, $cSql);
$row = mysqli_fetch_row($cResult);
$totalRecords = $row[0];
$totatPages = ceil($totalRecords/ $resultPerPage);
$pageLink = "<ul class='pagination'>";
for ($i=1; $i<=$totalPages; $i++) {
$pageLink .= "<li><a href='search.php?page=".$i."'>".$i."</a></li>";
};
echo $pageLink . "</ul>";
?>

why this code give me all record in database when i search for some movies and the page link on pagination is not disabled when there is two or one record and when i clicked on it, it give me blank page just help me with an example

i want the search result appear with pagination like when someone searching on google

ginerjm · May 15, 2019

Once again your code is so flawed. This line:

if(isset($_GET['submit']) && isset($_GET['submit']) !=""){

is bad in mutliple ways. First - there is no element named 'submit'. You have a submit button but it does not have a name, so unless PHP defaults un-named elements to 'submit' the test for isset on $_GET['submit'] will always fail. That and your convoluted logic using a test against a value and the isset function. Think before you code. The call to the isset function will return a true or false (aka, boolean) answer. Testing that against your "" will always return false too. But that is not what you are looking to do, is it?

Later on you write an sql statement - a query. This line:

SELECT *FROM movies WHERE name LIKE %$escapeString% OR producer LIKE %$escapeString% ";

may have multiple problems but the first is that you joined an asterisk (*) with the word "FROM". I think the sql interpreter is going to have difficulty recognizing that. And unless I am mistaken (which I can be) the use of LIKE with your wild-carded php variable probably needs some single quotes around those values. More like this:

SELECT * FROM movies WHERE name LIKE '%$escapeString%' OR producer LIKE '%$escapeString%' ";

Of course the proper way to make a query is to select the specific fields/columns that you need rather than to pull all columns from the table.

mac_gyver · May 15, 2019

writing code that works correctly requires that you have first defined what the code is going to do, so that you don't waste time writing/trying code that has nothing to do with the goal you are trying to achieve.

a search page with pagination involves -

1) a search term input, from a $_GET parameter in the url. you only need a single input for this. it will either be set or it won't. you need to decide what to do when it is not set. will you output a message that the search term is empty and skip running all the database code or do you instead match all data, i.e. have no WHERE term in the sql queries? you should trim() this value before testing it to remove accidental white-space characters and to let you detect if an all white-space value was submitted.

2) a page input, from a $_GET parameter in the url. if this is not set, you would use a default value of 1. you would also limit this value between 1 and the total number of pages. a negative or a zero value are not valid and a page greater than the total number of pages won't match any data.

3) a COUNT(*) query to get the total number of matching rows. the result from this query is used to calculate the total number of pages. this is used to test/limit the requested page number and when producing the pagination links.

4) a data retrieval query to get the logical page of data. both the COUNT(*) query and the data retrieval query must have the same table(s) and any JOIN, WHERE, GROUP BY, and HAVING terms. you should build this common part of the sql queries in a php variable, then use that variable in both of the actual queries. the data retrieval query also has ORDER BY and LIMIT terms. if you retrieve all the data from the data retrieval query into a php variable, it will separate the database specific code from your presentation code, making it easier to test your code or to change the database extension without having to make changes though out your code.

5) code to display the retrieved data.

6) pagination links. the pagination links need to include any existing search term and the page numbers. the easiest way of doing this is to get a copy of any existing $_GET parameters and use http_build_query() to produce the query string part of the urls.

some suggestions for your code -

1) the ....escape_string() functions can still allow sql injection if the character set that php is using isn't the same as your database table(s) and it is rare that anyone sets the character set when they make a database connection. you should use prepared queries when supplying external/unknown values to the sql query statement. a prepared query results in the simplest sql query syntax and the least amount of php code, provided that you use the php PDO database extension.

2) you should have error handling for all the database statements (connection, query, prepare, and execute.) the easiest way of doing this is to use exceptions and in most cases let php catch the exception where it will use its error related settings to control what happens with the actual error information (short-answer database errors will get displayed/logged the same as php errors.)

3) the search form should be on the same page with the search results and you should make the form 'sticky' by re-populating the field value with any existing search term. this will result in the least amount of code and allow the user to see what the existing search term is and to easily modify it if need be.

Edited May 15, 2019 by mac_gyver

mahenda · May 16, 2019

<?php
//I added name="submit" in the form now my cde are 
//let's start with this part before pagination is this correct and secured ?! help me with example

if(isset($_GET['submit'])){
include_once('conn.php');
$movieName = $_GET['search'];
$escapeString = mysqli_real_escape_string($conn,  $movieName );
if($escapeString !=""){
$sql = "SELECT name, producer FROM movies WHERE name LIKE '%".$escapeString."%' OR  producer LIKE '%".$escapeString."%' ";

$result = mysqli_query($conn, $sql);

if($rows = mysqli_fetch_all($result)){

echo '  <div>

<span>'.$rows['name'].'</span>

<span>'.$rows['producer'].'</span>
  </div>  ';

}
else
{
echo "<span> no such term in our database! </span>";
}
else
{
echo "<span> you have to enter search term in the field! </span>";
}
?>

look at that guys

ginerjm · May 16, 2019

NO it is not correct.

You really need to do some kind of learning. You are showing no understanding of anything PHP related.

When you do a fetchall, do you have even a clue what is happening there? I think you have a vague idea since you are using a variable named '$rows' to capture the result of the fetchall. That is good. But then you use that same variable in your output. What is it going to output if there are 2, 3 or 99 movies that come up in your query results? That $rows variable is an ARRAY so you can't just go echoing out one element of that array since there could be multiple rows of data in your $rows array.

The approach you really want IMHO is to do a fetch, not a fetchall as part of a while loop and then use the $rows (or $row) array to echo out the contents of that result row. Try it.

PS - see if this re-working of your query statement makes sense to you and why.

$sql = "SELECT name, producer FROM movies WHERE name LIKE '%$escapeString%' OR  producer LIKE '%$escapeString%'";

PPS - if you haven't realized it yet PHP is a case-sensitive language. That means "$escapeString" is a different variable than "$escapestring". That means when you needlessly add caps to your names you force yourself to remember how you did it on every one of your variables and it really is not necessary. When you have a 200-300 line script (or more!) and you are having a problem and can't figure it out, it is going to be because you mis-typed a name somewhere and things are not happening as you may be imagining they are. Yes - JS uses a special capitalization rule for its vars and most people are in tune with it. PHP isn't setup that way and to adopt your own rule of capitalization is needless. Just my $.02 here but I think it is worth you thinking about.

mahenda · May 16, 2019

3 hours ago, ginerjm said:
NO it is not correct.

You really need to do some kind of learning. You are showing no understanding of anything PHP related.

When you do a fetchall, do you have even a clue what is happening there? I think you have a vague idea since you are using a variable named '$rows' to capture the result of the fetchall. That is good. But then you use that same variable in your output. What is it going to output if there are 2, 3 or 99 movies that come up in your query results? That $rows variable is an ARRAY so you can't just go echoing out one element of that array since there could be multiple rows of data in your $rows array.

The approach you really want IMHO is to do a fetch, not a fetchall as part of a while loop and then use the $rows (or $row) array to echo out the contents of that result row. Try it.

PS - see if this re-working of your query statement makes sense to you and why.
$sql = "SELECT name, producer FROM movies WHERE name LIKE '%$escapeString%' OR  producer LIKE '%$escapeString%'";
PPS - if you haven't realized it yet PHP is a case-sensitive language. That means "$escapeString" is a different variable than "$escapestring". That means when you needlessly add caps to your names you force yourself to remember how you did it on every one of your variables and it really is not necessary. When you have a 200-300 line script (or more!) and you are having a problem and can't figure it out, it is going to be because you mis-typed a name somewhere and things are not happening as you may be imagining they are. Yes - JS uses a special capitalization rule for its vars and most people are in tune with it. PHP isn't setup that way and to adopt your own rule of capitalization is needless. Just my $.02 here but I think it is worth you thinking about.

so i have to use mysqli_fetch_assoc and how t can i highlight the search text using tis codes please i need example i'm not an an experienced developer

ginerjm · May 16, 2019

What do you mean by "highlight the search text"? I didn't cover that in my post. Let's stick to one thing for now.

mahenda · May 18, 2019

//i want to put all result into pagination help me how to structure the php code check the first search php code an the second pagination codes

//search php code
if(isset($_GET['submit'])){
include_once('conn.php');
$movieName = $_GET['search'];
$escapeString = mysqli_real_escape_string($conn,  $movieName );
if($escapeString !=""){
$sql = "SELECT name, producer FROM movies WHERE name LIKE '%.$escapeString.%' OR  producer LIKE '%.$escapeString.%' ";

$result = mysqli_query($conn, $sql);

if($row = mysqli_fetch_assoc($result)){

echo '  <div>

<span>'.$row['name'].'</span>

<span>'.$row['producer'].'</span>
  </div>  ';

}
else
{
echo "<span> no such term in our database! </span>";
}
else
{
echo "<span> you have to enter search term in the field! </span>";
}
?>
//pagination php code

$limit = 8;
if (isset($_GET["page"])) {

$page = $_GET["page"];

} else {

$page=1;

}
$startPage= ($page-1) * $limit;

$sql = "SELECT COUNT(moviesId) FROM movies";

$result = mysqli_query($conn, $sql);
$row = mysqli_fetch_row($result);
$totalRecords = $row[0];
$totalPages = ceil($totalRecords / $limit);
$pageLink = "<ul class='pagination'>";
for ($i=1; $i<=$totalPages; $i++) {
$pageLink .= "<li><a href='search.php?page=".$i."'>".$i."</a></li>";
}
echo $pageLink . "</ul></nav>";

Barand · May 18, 2019

On 5/15/2019 at 3:30 PM, mac_gyver said:

3) a COUNT(*) query to get the total number of matching rows. the result from this query is used to calculate the total number of pages. this is used to test/limit the requested page number and when producing the pagination links.

4) a data retrieval query to get the logical page of data. both the COUNT(*) query and the data retrieval query must have the same table(s) and any JOIN, WHERE, GROUP BY, and HAVING terms. you should build this common part of the sql queries in a php variable, then use that variable in both of the actual queries. the data retrieval query also has ORDER BY and LIMIT terms. if you retrieve all the data from the data retrieval query into a php variable, it will separate the database specific code from your presentation code, making it easier to test your code or to change the database extension without having to make changes though out your code.

I suggest you re-read what mac_gyver has already told you

mahenda · May 18, 2019

1 hour ago, Barand said:

I suggest you re-read what mac_gyver has already told you

//according to mac_gyver i'm trying step 1 here help for any mistakes

<?php
if(isset($_GET['submit'])){
   include_once('conn.php');
   $names = $_GET['search'];
   $escapeString = mysqli_real_escape_string($conn, $names);
       if($escapeString !=""){
           $sql = "SELECT name,poducer FROM movies WHERE name LIKE '%.$escapeString.%' OR producer LIKE '%.$escapeString.%' ";
           $result = mysqli_query($conn, $sql);

           if(mysqli_num_rows($result) >0){
               echo '
               <div class="head">
               <h6 class="ml-0"> RESULT RELATED WITH: <span class="text-success">'.$escapeString.'</span></h6>
               </div>';
                   while($row = mysqli_fetch_assoc($result)){
                   echo '<div>

                       <span>'.$row['producer'].'</span>
                       </div> ';
               }
           }
           else
           {
               echo '
               <div class="head">
               <h6 class="ml-0"> RESULTS REPORT:</h6>
               </div>';
               echo '<span>The term </span><span class="text-warning"> '.$escapeString.' </span><span> is not available in our database </span><br><span> try <a href="#" style="text-decoration:none;">Advanced Search</a> </span>';
           }
           else{
               echo '<span> Mh! You have to Enter search term';
           }
       }
}
?>

mahenda · May 18, 2019

1 hour ago, Barand said:

I suggest you re-read what mac_gyver has already told you

//according to mac_gyver i'm trying step 1 here help for any mistakes

<?php
if(isset($_GET['submit'])){
   include_once('conn.php');
   $names = $_GET['search'];
   $escapeString = mysqli_real_escape_string($conn, $names);
       if($escapeString !=""){
           $sql = "SELECT name,poducer FROM movies WHERE name LIKE '%.$escapeString.%' OR producer LIKE '%.$escapeString.%' ";
           $result = mysqli_query($conn, $sql);

           if(mysqli_num_rows($result) >0){
               echo '
               <div class="head">
               <h6 class="ml-0"> RESULT RELATED WITH: <span class="text-success">'.$escapeString.'</span></h6>
               </div>';
                   while($row = mysqli_fetch_assoc($result)){
                   echo '<div>

                       <span>'.$row['producer'].'</span>
                       </div> ';
               }
           }
else{
echo '
<div class="head">
<h6 class="ml-0"> RESULTS REPORT:</h6>
</div>';
echo '<span>The term </span><span class="text-warning"> '.$escapeString.' </span><span> is not available in our database </span><br><span> try <a href="#" style="text-decoration:none;">Advanced Search</a> </span>';
}
}
else{
echo '<span>you have to enter something</span>';
}
}
?>

//updated

mahenda · May 18, 2019

ginerjm · May 18, 2019

I suggest that you re-read what I gave you on how to write that query statement. You can't even copy code correctly!

mahenda · May 19, 2019

i tried to follow some instruction here but i don't achieve ,i'm getting into trouble I know you helped me on this

it will be better , if someone can show me an example with source code according to mac_gyver

@mac_gyver @Barand @cyberRobot and others pleaseeeeeeeeeee! i cant handle it without some snippet help help me guys

Sign In

how to create search functionality in php and mysql and the search result will be on another page apart from the search page

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information