Advanced Search

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
birrd
Forum Newbie
Posts: 6
Joined: Mon Dec 01, 2008 10:47 am

Advanced Search

Post by birrd »

Briefly: I am doing an advanced search page for a movie review website.

Objective: To reflect results according to selected specific parameter(s) with a single search button.

Question: How do I condition the search process page to enable 'searchby' specific parameters? Include array? Set boolean??

E.g. If I fill in the blank for title and select a date (leaving the rest blank), I will see the result of similar titles and dates. Conversely, if i leave the title column blank, select the date range and a rating (5); I will see only the result of movies rated 5 and range between the stated dates.

Currently:

I have a main page - Search.php and four other process pages which should be combined into one.. but I can't do it without errors...

1. p_search.php which searches by title or director
2. p_searchdate.php which searches by release date between --- to ---
3. p_searchrate.php which searches for movie by star ratings 1 - 5
4. p_searchur.php which searches for movie by average user ratings 1 -5)

HELP!

This is search.php ...

Code: Select all

 
<H1><center> Advanced Search</H1>
<table width="800" border="1" align="center" cellpadding='5' cellspacing='5'>
<form name="search" method="POST" action="p_search.php">
<tr>
<td align="left">
Looking for <input name="search" type="text"><select name="searchby" size="1" id="searchby">
                    <option value="title" selected> Title </option>
                    <option value="Director"> Director </option>
                    </td>
</tr>
 
 
<form name="Release_date" method="POST" action="p_searchdate.php">
<SCRIPT LANGUAGE="JavaScript" SRC="CalendarPopup.js"></SCRIPT>
    <SCRIPT LANGUAGE="JavaScript">
    var cal = new CalendarPopup();
    </SCRIPT>
<tr>
<td align="left">
Release Date between 
<input name="Release_date" id="Release_date" type="text"  value="" align="right"> <A HREF="#"
   onClick="cal.select(document.forms['Release_date'].Release_date,'anchor1','yyyy/MM/dd'); return false;"
   NAME="anchor1" ID="anchor1"><font size=1.5>[Date]</font></A> --- <input name="Release_date2" id="Release_date2" type="text"  value="" align="right"> <A HREF="#"
   onClick="cal.select(document.forms['Release_date'].Release_date2,'anchor2','yyyy/MM/dd'); return false;"
   NAME="anchor2" ID="anchor2"><font size=1.5>[Date]</font></A>
</td>
</tr>
 
 
<form name="rating" method="POST" action="p_searchrate.php">
<tr>
<td  align="left">
Official Rating 
 
<input type="radio" name="rating"  value="1"><img src='images/star2.gif'>
 
<input type="radio" name="rating"  value="2"><img src='images/star2.gif'><img src='images/star2.gif'>
 
<input type="radio" name="rating"  value="3"><img src='images/star2.gif'><img src='images/star2.gif'><img src='images/star2.gif'>
 
<input type="radio" name="rating"  value="4"><img src='images/star2.gif'><img src='images/star2.gif'><img src='images/star2.gif'><img src='images/star2.gif'>
 
<input type="radio" name="rating"  value="5"><img src='images/star2.gif'><img src='images/star2.gif'><img src='images/star2.gif'><img src='images/star2.gif'><img src='images/star2.gif'>
</tr>
 
<tr>
</tr>
 
<form name="userrating" method="POST" action="p_searchur.php">
<tr>
<td  align="left">
Average User Rating 
 
<input type="radio" name="userrating"  value="1"><img src='images/star2.gif'>
 
<input type="radio" name="userrating"  value="2"><img src='images/star2.gif'><img src='images/star2.gif'>
 
<input type="radio" name="userrating"  value="3"><img src='images/star2.gif'><img src='images/star2.gif'><img src='images/star2.gif'>
 
<input type="radio" name="userrating"  value="4"><img src='images/star2.gif'><img src='images/star2.gif'><img src='images/star2.gif'><img src='images/star2.gif'>
 
<input type="radio" name="userrating"  value="5"><img src='images/star2.gif'><img src='images/star2.gif'><img src='images/star2.gif'><img src='images/star2.gif'><img src='images/star2.gif'>
</tr>
<tr>
<td  align="left">
<input type="submit" name="Submit" value="search">
</tr>
</form>
</table>
 
This is p_search.php

Code: Select all

 
$searchQur = trim($_POST['search']);
$var=$searchQur;
$searchby =$_POST['searchby'];
 
if (empty($var)) 
        {
            echo '<p align="center">';
            echo '<meta http-equiv="Refresh" content="1;url=search.php">';
            echo 'Invalid Search. Please fill in the blank.';
            echo '</p>';
        }
 
    elseif($searchby=='title')
                {
                    $gettitle= "SELECT distinct * from movie WHERE publish=1 AND title LIKE '%".$searchQur."%'ORDER BY movie_id";
                    $result = mysql_query($gettitle) or die (mysql_error());
            
                    if(mysql_num_rows($result)>0)
                    {
                    echo "Search results:<br><br>";
                    echo "<table border='1'>";
                    echo "<tr>";
                    echo "<td width='150'>Title</td>";
                    echo "<td width='200'>Image</td>";
                    echo "</tr>";  
                    
                    while($row = mysql_fetch_array($result)){
                    $movie_id=$row['movie_id'];
                    $title =$row['title'];
                    $imageurl =$row['imageurl'];
                    
                    echo "<tr bgcolor=#ddeeff>";
                    echo"<td><a href='detail2.php?id=$movie_id'>$title</td>";
                    echo"<td><img src='picture/$imageurl' width='200'/></td>";
                    echo "</tr>";
                    }
                    echo "</table>";
                    }
                    
                    else
                    {
                    echo '<p align="center">';
                    echo '<meta http-equiv="Refresh" content="1;url=search.php">';
                    echo "T: No result founds. Please try again.";
                    echo '</p>';
                    }
                    }
                    
        elseif($searchby=='Director')
                    {
                    $getDirector="SELECT distinct * from movie WHERE publish=1 AND Director LIKE '%".$searchQur."%'ORDER BY movie_id";
                    
                    $result2 = mysql_query($getDirector) or die (mysql_error());
                    
                    if(mysql_num_rows($result2)>0)
                    {
                    echo "Search results:<br><br>";
                    echo "<table border='1'>";
                    echo "<tr>";
                    echo "<td width='150'>Director</td>";
                    echo "<td width='150'>Title</td>";
                    echo "<td width='200'>Image</td>";
                    echo "</tr>";  
                    
                    while($row = mysql_fetch_array($result2)){ 
                    $movie_id=$row['movie_id'];
                    $Director=$row['Director'];
                    $title =$row['title'];
                    $imageurl =$row['imageurl'];
                    
                    echo "<tr bgcolor=#ddeeff>";
                    echo"<td>$Director</td>";
                    echo"<td><a href='detail2.php?id=$movie_id'>$title</td>";
                    echo"<td><img src='picture/$imageurl' width='200'/></td>";
                    echo "</tr>";
                    }
                    echo "</table>";
                    }
 
                    else
                    {
                    echo '<p align="center">';
                    echo '<meta http-equiv="Refresh" content="1;url=search.php">';
                    echo "D: No result found. Please try again";
                    echo '</p>';
                    }
                    }
 
This is p_searchdate.php

Code: Select all

 
$searchQur = trim($_POST['Release_date']);
$Release_date=date("Y/m/d",strtotime($searchQur));
 
$searchQur2 = trim($_POST['Release_date2']);
$Release_date2=date("Y/m/d",strtotime($searchQur2));
 
if($Release_date!='' || $Release_date2!='')
        {
 
        $result3 = mysql_query("Select * from movie WHERE (publish=1) AND (Release_date <='$searchQur2') AND (Release_date >'$searchQur') order by Release_date ASC")or die(mysql_error());
 
        if(mysql_num_rows($result3)>0)
                    {
                    echo "Search results:<br><br>";
                    echo "<table border='1'>";
                    echo "<tr>";
                    echo "<td width='150'>Director</td>";
                    echo "<td width='90'>Release Date</td>";
                    echo "<td width='150'>Title</td>";
                    echo "<td width='200'>Image</td>";
                    echo "</tr>";  
                    
                    while($row = mysql_fetch_assoc($result3)){ 
                    $movie_id=$row['movie_id'];
                    $Director=$row['Director'];
                    $Release_date = $row['Release_date'];
                    $title =$row['title'];  
                    $imageurl =$row['imageurl'];
                    
                    echo"<tr bgcolor=#ddeeff>";
                    echo"<td><center>$Director</td>";
                    echo"<td><center>".date("d/m/Y",strtotime($Release_date))."</td>";
                    echo"<td><center><a href='detail2.php?id=$movie_id'>$title</td>";
                    echo"<td><img src='picture/$imageurl' width='200'/></td>";
                    echo "</tr>";
                    }
                    echo "</table>";
                    }
                    
                    else
                    {
                    echo '<p align="center">';
                    echo '<meta http-equiv="Refresh" content="1;url=search.php">';
                    echo "Date: No results found.";
                    echo '</p>';
                    }   
                    }
 
This is p_searchrate.php

Code: Select all

 
$rating = $_POST['rating'];
 
$ratingStarr = "";
   For ($i = 4; $i >= $rating; $i--){
     $ratingStarr = $ratingStarr."<img src='images/star1.gif'>";
     }
     
$ratingStar = "";
   For ($i = 1; $i <= $rating; $i +=1){
     $ratingStar = $ratingStar."<img src='images/star2.gif'>";
     }
   
   
if ($rating==1)
        {
            $get1="SELECT * from movie WHERE publish=1 AND rating=1 ORDER BY movie_id";
                    
            $result3 = mysql_query($get1) or die (mysql_error());
                    
                echo "Search results:<br><br>";
                echo "<table border='1'>";
                echo "<tr>";
                echo "<td width='150'>Director</td>";
                echo "<td width='150'>Title</td>";
                echo "<td width='200'>Image</td>";
                echo "<td width='150'>Release Date</td>";
                echo "<td width='200'>Rating</td>";
                echo "</tr>";  
                    
                while($row = mysql_fetch_assoc($result3)){ 
                $movie_id=$row['movie_id'];
                $Director=$row['Director'];
                $title =$row['title'];
                $imageurl =$row['imageurl'];
                $Release_date=$row['Release_date'];
                $rating =$row['rating'];
                    
                echo"<tr bgcolor=#ddeeff>";
                echo"<td>$Director</td>";
                echo"<td><a href='detail2.php?id=$movie_id'>$title</td>";
                echo"<td><img src='picture/$imageurl' width='200'/></td>";
                echo"<td>". date("d/m/Y",strtotime($Release_date))."</td>";
                echo"<td><img src='images/star1.gif'></td>";
                echo"</tr>";
                }
                echo"</table>";
                }
    
    else if ($rating==2)
    {
       $get2="SELECT * from movie WHERE publish=1 AND rating=2 ORDER BY movie_id";
                    
                    $result4 = mysql_query($get2) or die (mysql_error());
                    
                    echo "Search results:<br><br>";
                    echo "<table border='1'>";
                    echo "<tr>";
                    echo "<td width='150'>Director</td>";
                    echo "<td width='150'>Title</td>";
                    echo "<td width='200'>Image</td>";
                    echo "<td width='150'>Release Date</td>";
                    echo "<td width='200'>Rating</td>";
                    echo "</tr>";  
                    
                    while($row = mysql_fetch_assoc($result4)){ 
                    $movie_id=$row['movie_id'];
                    $Director=$row['Director'];
                    $title =$row['title'];
                    $imageurl =$row['imageurl'];
                    $Release_date=$row['Release_date'];
                    $rating =$row['rating'];
                    
                    echo"<tr bgcolor=#ddeeff>";
                    echo"<td>$Director</td>";
                    echo"<td><a href='detail2.php?id=$movie_id'>$title</td>";
                    echo"<td><img src='picture/$imageurl' width='200'/></td>";
                    echo"<td>". date("d/m/Y",strtotime($Release_date))."</td>";
                    echo"<td>$ratingStar$ratingStarr</td>";
                    echo"</tr>";
                    }
                    echo"</table>";
                    }
         
    elseif ($rating==3)  
    {
       $get3="SELECT * from movie WHERE publish=1 AND rating=3 ORDER BY movie_id";
                    
                    $result5 = mysql_query($get3) or die (mysql_error());
                    
                    echo "Search results:<br><br>";
                    echo "<table border='1'>";
                    echo "<tr>";
                    echo "<td width='150'>Director</td>";
                    echo "<td width='150'>Title</td>";
                    echo "<td width='200'>Image</td>";
                    echo "<td width='150'>Release Date</td>";
                    echo "<td width='200'>Rating</td>";
                    echo "</tr>";  
                    
                    while($row = mysql_fetch_assoc($result5)){ 
                    $movie_id=$row['movie_id'];
                    $Director=$row['Director'];
                    $title =$row['title'];
                    $imageurl =$row['imageurl'];
                    $Release_date=$row['Release_date'];
                    $rating =$row['rating'];
                    
                    echo"<tr bgcolor=#ddeeff>";
                    echo"<td>$Director</td>";
                    echo"<td><a href='detail2.php?id=$movie_id'>$title</td>";
                    echo"<td><img src='picture/$imageurl' width='200'/></td>";
                    echo"<td>". date("d/m/Y",strtotime($Release_date))."</td>";
                    echo"<td>$ratingStar$ratingStarr</td>";
                    echo"</tr>";
                    }
                    echo"</table>";
                    }
 
    else if ($rating==4)
    {
       $get4="SELECT * from movie WHERE publish=1 AND rating=4 ORDER BY movie_id";
                    
                    $result6 = mysql_query($get4) or die (mysql_error());
 
                    echo "Search results:<br><br>";
                    echo "<table border='1'>";
                    echo "<tr>";
                    echo "<td width='150'>Director</td>";
                    echo "<td width='150'>Title</td>";
                    echo "<td width='200'>Image</td>";
                    echo "<td width='150'>Release Date</td>";
                    echo "<td width='200'>Rating</td>";
                    echo "</tr>";  
                    
                    while($row = mysql_fetch_assoc($result6)){ 
                    $movie_id=$row['movie_id'];
                    $Director=$row['Director'];
                    $title =$row['title'];
                    $imageurl =$row['imageurl'];
                    $Release_date=$row['Release_date'];
                    $rating =$row['rating'];
                    
                    echo"<tr bgcolor=#ddeeff>";
                    echo"<td>$Director</td>";
                    echo"<td><a href='detail2.php?id=$movie_id'>$title</td>";
                    echo"<td><img src='picture/$imageurl' width='200'/></td>";
                    echo"<td>". date("d/m/Y",strtotime($Release_date))."</td>";
                    echo"<td>$ratingStar$ratingStarr</td>";
                    echo"</tr>";
                    }
                    echo"</table>";
                    }
    
    else if ($rating==5)
    {
       $get5="SELECT distinct * from movie WHERE publish=1 AND rating=5 ORDER BY movie_id";
                    
                    $result7 = mysql_query($get5) or die (mysql_error());
 
                    echo "Search results:<br><br>";
                    echo "<table border='1'>";
                    echo "<tr>";
                    echo "<td width='150'>Director</td>";
                    echo "<td width='150'>Title</td>";
                    echo "<td width='200'>Image</td>";
                    echo "<td width='150'>Release Date</td>";
                    echo "<td width='200'>Rating</td>";
                    echo "</tr>";  
                    
                    while($row = mysql_fetch_assoc($result7)){ 
                    $movie_id=$row['movie_id'];
                    $Director=$row['Director'];
                    $title =$row['title'];
                    $imageurl =$row['imageurl'];
                    $Release_date=$row['Release_date'];
                    $rating =$row['rating'];
                    
                    echo"<tr bgcolor=#ddeeff>";
                    echo"<td>$Director</td>";
                    echo"<td><a href='detail2.php?id=$movie_id'>$title</td>";
                    echo"<td><img src='picture/$imageurl' width='200'/></td>";
                    echo"<td>". date("d/m/Y",strtotime($Release_date))."</td>";
                    echo"<td>$ratingStar$ratingStarr</td>";
                    echo"</tr>";                    
                    }
                    echo"</table>";
                    }
 
This is p_searchur.php

Code: Select all

$x=0;
$total=0;
$getratingvalue = $_POST['userrating'];
    
if($getratingvalue!='') //make sure they select something Ex: 1 2 3 4 5
{
    $getrating="SELECT * from movie"; //select all movies in database
    $result = mysql_query($getrating) or die (mysql_error());
    
    if(mysql_num_rows($result)>0)// if there's movie in database,
    {
        //echo the table heading.
        echo "Search results:<br><br>";
        echo "<table border='1'>";
        echo "<tr>";
        echo "<td width='150'>Movie Title</td>";
        echo "<td width='150'>User Average Rating</td>";
        echo "<td width='200'>Image</td>";
        echo "</tr>";  
        
        while($row = mysql_fetch_assoc($result))//looping each movie in database. Start from 1, then 2, then so on.
        {
            $movie_id= $row['movie_id'];
            $tl=$row['title'];
            $imageurl = $row['imageurl'];
            $getaverage = "Select * from usercomments where movie_id='".$movie_id."'";//select all usercoments for this movie.
            $averageresult = mysql_query($getaverage) or die (mysql_error());
            $x=0;
            $total=0;
            $avarage=0;
            while($row=mysql_fetch_assoc($averageresult))//we get all the usercomment for this movie. now we loop and count avrage
            {
                $rating = $row['userrating'];
                $x = $x+1; 
                $total = $total+$rating;
                if($x!=0)
                {
                    $avarage = $total/$x;}
                else
                {
                    $avarage = 0;
                }
                
            }
            
            $avarage = round($avarage);//round off
            if ($getratingvalue==$avarage)//if user click 3 and average is 3. then show.
            {
                echo"<tr bgcolor=#ddeeff>";
                //below is star
                echo"<td><a href='detail2.php?id=".$movie_id."'>".$tl."</a></td>";
                if($avarage==1)
                {
                    echo"<td><img src='images/star2.gif'></td>";
                }
                else if($avarage==2)
                {
                    echo"<td><img src='images/star2.gif'><img src='images/star2.gif'></td>";
                }
                else if($avarage==3)
                {
                    echo"<td><img src='images/star2.gif'><img src='images/star2.gif'><img src='images/star2.gif'></td>";
                }
                else if($avarage==4)
                {
                    echo"<td><img src='images/star2.gif'><img src='images/star2.gif'><img src='images/star2.gif'><img src='images/star2.gif'></td>";
                }
                else if($avarage==5)
                {
                    echo"<td><img src='images/star2.gif'><img src='images/star2.gif'><img src='images/star2.gif'><img src='images/star2.gif'><img src='images/star2.gif'></td>";
                }
                //top is star       
                echo"<td><img src='picture/$imageurl' width='200'/></td>";
                echo "</tr>";
            }
        }
    }
}
    Attachments
    movie.sql.zip
    Database
    (4.7 KiB) Downloaded 32 times
    User avatar
    novice4eva
    Forum Contributor
    Posts: 327
    Joined: Thu Mar 29, 2007 3:48 am
    Location: Nepal

    Re: Advanced Search

    Post by novice4eva »

    I don't think most of the people will go through all that code...from what i have read, i think you want more elegant way to set the where parameters for your query. You could do something like

    Code: Select all

     
    function getMyMovies($postParams)
    {
    $Sql="SELECT * FROM some_movie_table WHERE /* JOINS IF NEEDED */1=1 ";
    if(isset($postParams['rating']) && trim($postParams['rating'])!='')
    $Sql.=" AND rating = '".$postParams['rating']."'";
    if(isset($postParams['launch_date']) && trim($postParams['launch_date'])!='')
    $Sql.=" AND launch_date= '".$postParams['launch_date']."'";
    ...
    ..
    }
     
    and you can call this function on submit where $postParams = $_POST !!

    Hope this helps :wink:
    Post Reply