Jump to content
mahenda

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

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

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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['name'].'</span>

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

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

  </div>  ';

} ?>

 

 

 

 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
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>
<!--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['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['name'].'</span>

<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

Share this post


Link to post
Share on other sites
Posted (edited)

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 by chhorn

Share this post


Link to post
Share on other sites
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'] ?

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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 .

Share this post


Link to post
Share on other sites
  • read up on using prepared queries - RTFM!!
  • Like 1

Share this post


Link to post
Share on other sites

<?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['name'].'</span>

<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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
Posted (edited)

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 by mac_gyver

Share this post


Link to post
Share on other sites
<?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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
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

 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
//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>"; 

 

 

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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['name'].'</span>

                        <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';    
            }
        }
}
?>    

 

Share this post


Link to post
Share on other sites
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['name'].'</span>

                        <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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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  

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.