Php scritps for exact text search

valera · 11-22-2012, 03:40 PM

Hi, I am trying to revise the following php script so that it can do a "exact full text" search mySQL table. The exact text is passed from another php scripts. Could someone give me a hint? I am a very very beginner for php programming and I bought this scripts.

PHP Code:


			
<?
    session_start();

    include("includes/config.inc.php");
    include("includes/config.php");
    
$sqlsettings = "SELECT * from admin";
$resultsettings = mysql_query($sqlsettings);
$rowsettings = @mysql_fetch_array($resultsettings);
    
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title><?PHP echo $rowsettings['Title']?> - Products</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link href="style.css" rel="stylesheet" type="text/css">
<style type="text/css">
<!--
body {
    margin-left: 0px;
    margin-top: 0px;
    margin-right: 0px;
    margin-bottom: 0px;
}
-->
</style></head>
<body>


    <table border="0" cellspacing="0" width="1000" align="center">
        
        <Tr>
            <Td colspan="2">
                <?
                    include("header.php");
                    include("searching_products.php");
                ?>

<p align="center">
<?
if(isset($_REQUEST['IndustryID']) && isset($_REQUEST['CategoryID']))
{
                $sql_topbanner = "select * from topbanners where page='products' and CategoryID=".$_REQUEST['IndustryID']." and SubCategoryID=".$_REQUEST['CategoryID'];
                $result_topbanner = mysql_query($sql_topbanner);
                if(@mysql_num_rows($result_topbanner) > 0)
                {
                    $row_topbanner = @mysql_fetch_array($result_topbanner);
                    $TopBanner = str_replace("../", "", $row_topbanner['Banner']);
                    $checkflash = substr($row_topbanner['Banner'], -3);
                ?>
                <?PHP
if(strtolower($checkflash) == "swf")
{
?>
 
<?PHP
}
else
{
?>
<a href="<?PHP echo $row_topbanner['link']?>" target="_blank"><img src="<?PHP echo $TopBanner;?>" width="900" height="100" border="0"></a>
<?PHP
}
?>
                <?
                }
                else
                {
                ?>                
                <a href="contactus.php"><img src="images/topbanner.gif" width="900" height="100" border="0"></a>
                <?
                }
}
else
{
                if($_REQUEST["IndustryID"] == "")
                {
                //if industry id search done with in all categories then banner of home page...
                
                $sql_topbanner = "select * from topbanners where page='home'";
                $result_topbanner = mysql_query($sql_topbanner);
                if(@mysql_num_rows($result_topbanner) > 0)
                {
                    $row_topbanner = @mysql_fetch_array($result_topbanner);
                    $TopBanner = str_replace("../", "", $row_topbanner['Banner']);
                    $checkflash = substr($row_topbanner['Banner'], -3);
                ?>
                <?PHP
if(strtolower($checkflash) == "swf")
{
?>
 
<?PHP
}
else
{
?>
<a href="<?PHP echo $row_topbanner['link']?>" target="_blank"><img src="<?PHP echo $TopBanner;?>" width="900" height="100" border="0"></a>
<?PHP
}
?>
                <?
                }
                else
                {
                ?>                
                <a href="contactus.php"><img src="images/topbanner.gif" width="900" height="100" border="0"></a>
                <?
                }

                
                
                }
                else
                {
                
                $sql_topbanner = "select * from topbanners where page='products_search' and CategoryID=".$_REQUEST['IndustryID'];
                $result_topbanner = mysql_query($sql_topbanner);
                if(@mysql_num_rows($result_topbanner) > 0)
                {
                    $row_topbanner = @mysql_fetch_array($result_topbanner);
                    $TopBanner = str_replace("../", "", $row_topbanner['Banner']);
                    $checkflash = substr($row_topbanner['Banner'], -3);
                ?>
                <?PHP
if(strtolower($checkflash) == "swf")
{
?>
<object classid="clsid:D27CDB6E-AE6D-11cf-96B8-444553540000" codebase="http://download.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=6,0,29,0" width="900" height="100">
  <param name="movie" value="<?PHP echo $TopBanner?>">
  <param name="quality" value="high">
  <embed src="<?PHP echo $TopBanner?>" quality="high" pluginspage="http://www.macromedia.com/go/getflashplayer" type="application/x-shockwave-flash" width="900" height="100"></embed>
</object>
<?PHP
}
else
{
?>
<a href="<?PHP echo $row_topbanner['link']?>" target="_blank"><img src="<?PHP echo $TopBanner;?>" width="900" height="100" border="0"></a>
<?PHP
}
?>
                <?
                }
                else
                {
                ?>                
                <a href="contactus.php"><img src="images/topbanner.gif" width="900" height="100" border="0"></a>
                <?
                }
                
                }

}
?>
</p>

<table border="0"  width="1000" cellspacing="0" cellpadding="5" align="center">
    <tr style="background-image:url(images/itemBoxTitleBg.gif); height:20px;">
                    <td class="adminvalues" width="13%">Product Image</td>
                    <td class="adminvalues" width="35%">Description </td>
                    <td class="adminvalues" width="35%">Supplier Name</td>
                    <td width="17%"></td>
    </tr>
    
        
    
    <? 
$page_name="s_products.php"; //  If you use this code with a different page ( or file ) name then change this 

if(!isset($_REQUEST["start"])) {                         // This variable is set to zero for the first page
$start = 0;
}
else
$start = $_REQUEST["start"];

$eu = ($start - 0); 
$limit = 20;                                 // No of records to be shown per page.
$this1 = $eu + $limit; 
$back = $eu - $limit; 
$next = $eu + $limit; 
    
             if(isset($_REQUEST["searching"]))
       {
           if($_REQUEST["IndustryID"] == "" && $_REQUEST["Country"] == "")
        {           
            $sqlProduct="select * from products,seller,seller_profile,industry,categories where products.SellerID=seller.SellerID and seller.SellerID=seller_profile.SellerID and products.CategoryID=categories.CategoryID and categories.IndustryID=industry.IndustryID and (products.ProductName LIKE '%".$_REQUEST['search']."%' or products.ProductKeyword     LIKE '%".$_REQUEST['search']."%' or products.ProductDescription LIKE '%".$_REQUEST['search']."%' or products.PlaceOfOrigin LIKE '%".$_REQUEST['search']."%' or products.BrandName LIKE '%".$_REQUEST['search']."%') and productstatus=1 order by GoldMember desc limit $eu, $limit";
            
            $sqlProducttot="select * from products,seller,seller_profile,industry,categories where products.SellerID=seller.SellerID and seller.SellerID=seller_profile.SellerID and products.CategoryID=categories.CategoryID and categories.IndustryID=industry.IndustryID and (products.ProductName LIKE '%".$_REQUEST['search']."%' or products.ProductKeyword     LIKE '%".$_REQUEST['search']."%' or products.ProductDescription LIKE '%".$_REQUEST['search']."%' or products.PlaceOfOrigin LIKE '%".$_REQUEST['search']."%' or products.BrandName LIKE '%".$_REQUEST['search']."%') and productstatus=1 order by GoldMember desc";
        }
        else if($_REQUEST["IndustryID"] == "" && $_REQUEST["Country"] != "")
        {           
            $sqlProduct="select * from products,seller,seller_profile,industry,categories where products.SellerID=seller.SellerID and seller.SellerID=seller_profile.SellerID and products.CategoryID=categories.CategoryID and categories.IndustryID=industry.IndustryID and seller.Country='".$_REQUEST['Country']."' and (products.ProductName LIKE '%".$_REQUEST['search']."%' or products.ProductKeyword    LIKE '%".$_REQUEST['search']."%' or products.ProductDescription LIKE '%".$_REQUEST['search']."%' or products.PlaceOfOrigin LIKE '%".$_REQUEST['search']."%' or products.BrandName LIKE '%".$_REQUEST['search']."%') and productstatus=1 order by GoldMember desc limit $eu, $limit";
            
            $sqlProducttot="select * from products,seller,seller_profile,industry,categories where products.SellerID=seller.SellerID and seller.SellerID=seller_profile.SellerID and products.CategoryID=categories.CategoryID and categories.IndustryID=industry.IndustryID and seller.Country='".$_REQUEST['Country']."' and (products.ProductName LIKE '%".$_REQUEST['search']."%' or products.ProductKeyword    LIKE '%".$_REQUEST['search']."%' or products.ProductDescription LIKE '%".$_REQUEST['search']."%' or products.PlaceOfOrigin LIKE '%".$_REQUEST['search']."%' or products.BrandName LIKE '%".$_REQUEST['search']."%') and productstatus=1 order by GoldMember desc";
        }
        else if($_REQUEST["IndustryID"] != "" && $_REQUEST["Country"] == "")
        {
            $sqlProduct="select * from products,seller,seller_profile,industry,categories where products.SellerID=seller.SellerID and seller.SellerID=seller_profile.SellerID and products.CategoryID=categories.CategoryID and categories.IndustryID=industry.IndustryID and Industry.IndustryID=".$_REQUEST['IndustryID']." and (products.ProductName LIKE '%".$_REQUEST['search']."%' or products.ProductKeyword     LIKE '%".$_REQUEST['search']."%' or products.ProductDescription LIKE '%".$_REQUEST['search']."%' or products.PlaceOfOrigin LIKE '%".$_REQUEST['search']."%' or products.BrandName LIKE '%".$_REQUEST['search']."%') and productstatus=1 order by GoldMember desc limit $eu, $limit";
            
            $sqlProducttot="select * from products,seller,seller_profile,industry,categories where products.SellerID=seller.SellerID and seller.SellerID=seller_profile.SellerID and products.CategoryID=categories.CategoryID and categories.IndustryID=industry.IndustryID and Industry.IndustryID=".$_REQUEST['IndustryID']." and (products.ProductName LIKE '%".$_REQUEST['search']."%' or products.ProductKeyword     LIKE '%".$_REQUEST['search']."%' or products.ProductDescription LIKE '%".$_REQUEST['search']."%' or products.PlaceOfOrigin LIKE '%".$_REQUEST['search']."%' or products.BrandName LIKE '%".$_REQUEST['search']."%') and productstatus=1 order by GoldMember desc";
        }
        else if($_REQUEST["IndustryID"] != "" && $_REQUEST["Country"] != "")
        {
            $sqlProduct="select * from products,seller,seller_profile,industry,categories where products.SellerID=seller.SellerID and seller.SellerID=seller_profile.SellerID and products.CategoryID=categories.CategoryID and categories.IndustryID=industry.IndustryID and Industry.IndustryID=".$_REQUEST['IndustryID']." and seller.Country='".$_REQUEST['Country']."' and (products.ProductName LIKE '%".$_REQUEST['search']."%' or products.ProductKeyword    LIKE '%".$_REQUEST['search']."%' or products.ProductDescription LIKE '%".$_REQUEST['search']."%' or products.PlaceOfOrigin LIKE '%".$_REQUEST['search']."%' or products.BrandName LIKE '%".$_REQUEST['search']."%') and productstatus=1 order by GoldMember desc limit $eu, $limit";
            
            $sqlProducttot="select * from products,seller,seller_profile,industry,categories where products.SellerID=seller.SellerID and seller.SellerID=seller_profile.SellerID and products.CategoryID=categories.CategoryID and categories.IndustryID=industry.IndustryID and Industry.IndustryID=".$_REQUEST['IndustryID']." and seller.Country='".$_REQUEST['Country']."' and (products.ProductName LIKE '%".$_REQUEST['search']."%' or products.ProductKeyword    LIKE '%".$_REQUEST['search']."%' or products.ProductDescription LIKE '%".$_REQUEST['search']."%' or products.PlaceOfOrigin LIKE '%".$_REQUEST['search']."%' or products.BrandName LIKE '%".$_REQUEST['search']."%') and productstatus=1 order by GoldMember desc";
        }
       }
       else
       {      
       $sqlProduct="select * from products,seller,seller_profile where products.SellerID=seller.SellerID and seller.SellerID=seller_profile.SellerID and CategoryID=".$_GET['CategoryID']." and productstatus=1 order by GoldMember desc limit $eu, $limit";
       
       $sqlProducttot="select * from products,seller,seller_profile where products.SellerID=seller.SellerID and seller.SellerID=seller_profile.SellerID and CategoryID=".$_GET['CategoryID']." and productstatus=1 order by GoldMember desc";
       }
       
       $resultSellingLeadstot=mysql_query($sqlProducttot);
      $nume=@mysql_num_rows($resultSellingLeadstot);

       $resultSellingLeads=mysql_query($sqlProduct);
       if(@mysql_num_rows($resultSellingLeads)!=0)
            {
             while($selling_row=@mysql_fetch_array($resultSellingLeads))
                 {
                echo"
                 <tr>
                        <td valign='top' width='120' class='bottomborder'>";
                         if($selling_row['thumb1']=="")
                         {
                            echo "<a href='supplier/product.php?ProductID=".$selling_row['ProductID']."&SellerID=".$selling_row['SellerID']."&CategoryID=".$selling_row['CategoryID']."' target='_blank'><img src='images/no_photo.gif' width='100' height='100' style='border:1px solid #cccccc;'/></a>";
                            }
                        else
                        {
                            echo "<a href='supplier/product.php?ProductID=".$selling_row['ProductID']."&SellerID=".$selling_row['SellerID']."&CategoryID=".$selling_row['CategoryID']."' class='subject' target='_blank'><img src='".$selling_row['thumb1']."' style='border:1px solid #cccccc;'/></a>";
                        }
                        
                        echo "</td>
                        <td valign='top' class='bottomborder'><a href='supplier/product.php?ProductID=".$selling_row['ProductID']."&SellerID=".$selling_row['SellerID']."&CategoryID=".$selling_row['CategoryID']."' class='subject' target='_blank'>".ucfirst($selling_row['ProductName'])."</a>
                        <span class='content'>&nbsp;&nbsp;-&nbsp;&nbsp;".date("M j, Y",strtotime($selling_row['AddedDate']))."</span></div>
                        <div class='briefdescription'><br>
                        ".substr($selling_row['ProductDescription'],0,50)."</div>
                        <div id='fulltext'><a href='supplier/product.php?ProductID=".$selling_row['ProductID']."&SellerID=".$selling_row['SellerID']."&CategoryID=".$selling_row['CategoryID']."' class='topcap' target='_blank'>...Full Details</a></td>
                        <td valign='top' class='bottomborder'><a href='supplier/index.php?ProductID=".$selling_row['ProductID']."&SellerID=".$selling_row['SellerID']."&CategoryID=".$selling_row['CategoryID']."' class='supplier' target='_blank'>".$selling_row['CompanyName']."</a><br><br><img src='images/countries/".$selling_row['Country'].".gif' align='left'>&nbsp;<strong>".$selling_row['Country']."</strong><br>";
                         
                            if($selling_row['GoldMember']==1)
                             {
                             echo "<br>
    <span><img src='images/gold_supplier.jpg'/></span>";    
                             }
                             else
                             {
                             echo "<br>
    <span><img src='images/free_supplier.jpg'/></span>";    
                             }
                    echo " </td><td valign='top' class='bottomborder'><br>

                            <img src='images/contact.gif' border=0 align='top'><a href='supplier/contact_now.php?SellerID=".$selling_row['SellerID']."' class='contactnow' target='_blank'>Contact Now</a></td>
                          </tr>";
                            
                }
            
            }
            else
            {
                echo "<tr><td colspan='4'>Sorry, no results found...</td></tr>";
            }
    ?>
    
</table>
<?
/////////////// Start the buttom links with Prev and next link with page numbers /////////////////
$industryid = $_REQUEST['IndustryID'];
$country = mysql_escape_string($_REQUEST['Country']);
$searching = $_REQUEST['searching'];
$search = mysql_escape_string($_REQUEST['search']);
echo "<table align = 'center' width='50%'><tr><td  align='left' width='30%'>";
//// if our variable $back is equal to 0 or more then only we will display the link to move back ////////
if($back >=0) { 
print "<a href='$page_name?start=$back&industryid=$industryid&country=$country&searching=$searching&search=$search'><font face='Verdana' size='2'>PREV</font></a>"; 
} 
//////////////// Let us display the page links at  center. We will not display the current page as a link ///////////
echo "</td><td align=center width='30%'>";
$i=0;
$l=1;
for($i=0;$i < $nume;$i=$i+$limit){
if($i <> $eu){
echo " <a href='$page_name?start=$i&industryid=$industryid&country=$country&searching=$searching&search=$search'><font face='Verdana' size='2'>$l</font></a> ";
}
else { echo "<font face='Verdana' size='4' color=red>$l</font>";}        /// Current page is not displayed as link and given font color red
$l=$l+1;
}


echo "</td><td  align='right' width='30%'>";
///////////// If we are not in the last page then Next link will be displayed. Here we check that /////
if($this1 < $nume) { 
print "<a href='$page_name?start=$next&industryid=$industryid&country=$country&searching=$searching&search=$search'><font face='Verdana' size='2'>NEXT</font></a>";} 
echo "</td></tr></table>";
?>
</td>
</Tr>
<?
    include("footer.php");
?>

</table>

</body>
</html>

Fou-Lu · 11-22-2012, 05:24 PM

In the future please make sure you wrap your code in [php][/php] tags as it preserves the formatting.
This script is uh. . . not very good and is horrendously insecure. I hope you didn't pay a lot for it. Everything that needs to change in order to make exact versus partial searches is in here:

PHP Code:


			
             if(isset($_REQUEST["searching"]))
       {
           if($_REQUEST["IndustryID"] == "" && $_REQUEST["Country"] == "")
        {           
            $sqlProduct="select * from products,seller,seller_profile,industry,categories where products.SellerID=seller.SellerID and seller.SellerID=seller_profile.SellerID and products.CategoryID=categories.CategoryID and categories.IndustryID=industry.IndustryID and (products.ProductName LIKE '%".$_REQUEST['search']."%' or products.ProductKeyword     LIKE '%".$_REQUEST['search']."%' or products.ProductDescription LIKE '%".$_REQUEST['search']."%' or products.PlaceOfOrigin LIKE '%".$_REQUEST['search']."%' or products.BrandName LIKE '%".$_REQUEST['search']."%') and productstatus=1 order by GoldMember desc limit $eu, $limit";
            
            $sqlProducttot="select * from products,seller,seller_profile,industry,categories where products.SellerID=seller.SellerID and seller.SellerID=seller_profile.SellerID and products.CategoryID=categories.CategoryID and categories.IndustryID=industry.IndustryID and (products.ProductName LIKE '%".$_REQUEST['search']."%' or products.ProductKeyword     LIKE '%".$_REQUEST['search']."%' or products.ProductDescription LIKE '%".$_REQUEST['search']."%' or products.PlaceOfOrigin LIKE '%".$_REQUEST['search']."%' or products.BrandName LIKE '%".$_REQUEST['search']."%') and productstatus=1 order by GoldMember desc";
        }
        else if($_REQUEST["IndustryID"] == "" && $_REQUEST["Country"] != "")
        {           
            $sqlProduct="select * from products,seller,seller_profile,industry,categories where products.SellerID=seller.SellerID and seller.SellerID=seller_profile.SellerID and products.CategoryID=categories.CategoryID and categories.IndustryID=industry.IndustryID and seller.Country='".$_REQUEST['Country']."' and (products.ProductName LIKE '%".$_REQUEST['search']."%' or products.ProductKeyword    LIKE '%".$_REQUEST['search']."%' or products.ProductDescription LIKE '%".$_REQUEST['search']."%' or products.PlaceOfOrigin LIKE '%".$_REQUEST['search']."%' or products.BrandName LIKE '%".$_REQUEST['search']."%') and productstatus=1 order by GoldMember desc limit $eu, $limit";
            
            $sqlProducttot="select * from products,seller,seller_profile,industry,categories where products.SellerID=seller.SellerID and seller.SellerID=seller_profile.SellerID and products.CategoryID=categories.CategoryID and categories.IndustryID=industry.IndustryID and seller.Country='".$_REQUEST['Country']."' and (products.ProductName LIKE '%".$_REQUEST['search']."%' or products.ProductKeyword    LIKE '%".$_REQUEST['search']."%' or products.ProductDescription LIKE '%".$_REQUEST['search']."%' or products.PlaceOfOrigin LIKE '%".$_REQUEST['search']."%' or products.BrandName LIKE '%".$_REQUEST['search']."%') and productstatus=1 order by GoldMember desc";
        }
        else if($_REQUEST["IndustryID"] != "" && $_REQUEST["Country"] == "")
        {
            $sqlProduct="select * from products,seller,seller_profile,industry,categories where products.SellerID=seller.SellerID and seller.SellerID=seller_profile.SellerID and products.CategoryID=categories.CategoryID and categories.IndustryID=industry.IndustryID and Industry.IndustryID=".$_REQUEST['IndustryID']." and (products.ProductName LIKE '%".$_REQUEST['search']."%' or products.ProductKeyword     LIKE '%".$_REQUEST['search']."%' or products.ProductDescription LIKE '%".$_REQUEST['search']."%' or products.PlaceOfOrigin LIKE '%".$_REQUEST['search']."%' or products.BrandName LIKE '%".$_REQUEST['search']."%') and productstatus=1 order by GoldMember desc limit $eu, $limit";
            
            $sqlProducttot="select * from products,seller,seller_profile,industry,categories where products.SellerID=seller.SellerID and seller.SellerID=seller_profile.SellerID and products.CategoryID=categories.CategoryID and categories.IndustryID=industry.IndustryID and Industry.IndustryID=".$_REQUEST['IndustryID']." and (products.ProductName LIKE '%".$_REQUEST['search']."%' or products.ProductKeyword     LIKE '%".$_REQUEST['search']."%' or products.ProductDescription LIKE '%".$_REQUEST['search']."%' or products.PlaceOfOrigin LIKE '%".$_REQUEST['search']."%' or products.BrandName LIKE '%".$_REQUEST['search']."%') and productstatus=1 order by GoldMember desc";
        }
        else if($_REQUEST["IndustryID"] != "" && $_REQUEST["Country"] != "")
        {
            $sqlProduct="select * from products,seller,seller_profile,industry,categories where products.SellerID=seller.SellerID and seller.SellerID=seller_profile.SellerID and products.CategoryID=categories.CategoryID and categories.IndustryID=industry.IndustryID and Industry.IndustryID=".$_REQUEST['IndustryID']." and seller.Country='".$_REQUEST['Country']."' and (products.ProductName LIKE '%".$_REQUEST['search']."%' or products.ProductKeyword    LIKE '%".$_REQUEST['search']."%' or products.ProductDescription LIKE '%".$_REQUEST['search']."%' or products.PlaceOfOrigin LIKE '%".$_REQUEST['search']."%' or products.BrandName LIKE '%".$_REQUEST['search']."%') and productstatus=1 order by GoldMember desc limit $eu, $limit";
            
            $sqlProducttot="select * from products,seller,seller_profile,industry,categories where products.SellerID=seller.SellerID and seller.SellerID=seller_profile.SellerID and products.CategoryID=categories.CategoryID and categories.IndustryID=industry.IndustryID and Industry.IndustryID=".$_REQUEST['IndustryID']." and seller.Country='".$_REQUEST['Country']."' and (products.ProductName LIKE '%".$_REQUEST['search']."%' or products.ProductKeyword    LIKE '%".$_REQUEST['search']."%' or products.ProductDescription LIKE '%".$_REQUEST['search']."%' or products.PlaceOfOrigin LIKE '%".$_REQUEST['search']."%' or products.BrandName LIKE '%".$_REQUEST['search']."%') and productstatus=1 order by GoldMember desc";
        }
       }
       else
       {      
       $sqlProduct="select * from products,seller,seller_profile where products.SellerID=seller.SellerID and seller.SellerID=seller_profile.SellerID and CategoryID=".$_GET['CategoryID']." and productstatus=1 order by GoldMember desc limit $eu, $limit";
       
       $sqlProducttot="select * from products,seller,seller_profile where products.SellerID=seller.SellerID and seller.SellerID=seller_profile.SellerID and CategoryID=".$_GET['CategoryID']." and productstatus=1 order by GoldMember desc";
       }

All of these queries are issuing LIKE commands. To issue exact you would use = comparisons without the use of wildcards %. You could do both via checkboxes as well to determine if its supposed to be an exact or partial match. Currently the SQL won't make use of indexing since it matches a like within a string instead of beginning the string, so as you increase the number of records, so will the time to search.

This code is such a mess that it would IMO be easier to rewrite it completely than to fix it.