SQL Search script help

Question

valonesal 0 Junior Poster

14 Years Ago

I would like to make it so that no matter what the client enters it will match words in the database.

Example:
they type http://awebsite.com but only awebsite.com exists it still matches it and any combination such as they search awebsite.com and http://awebsite.com is in the database it matches.

Here is the script we are using how can we write this to accomplish this?

Thank you.

<?php
$dbHost = ''; // localhost will be used in most cases
// set these to your mysql database username and password.
$dbUser = ''; 
$dbPass = '';
$dbDatabase = ''; 

// the database you put the table into.
$con = mysql_connect($dbHost, $dbUser, $dbPass) or die("Failed to connect to MySQL Server. Error: " . mysql_error());

mysql_select_db($dbDatabase) or die("Failed to connect to database {$dbDatabase}. Error: " . mysql_error());

// Set up our error check and result check array
$error = array();
$results = array();

// First check if a form was submitted. 
// Since this is a search we will use $_GET
if (isset($_GET['search'])) {
   $searchTerms = trim($_GET['search']);
   $searchTerms = strip_tags($searchTerms); // remove any html/javascript.
   
   if (strlen($searchTerms) < 5) {
      $error[] = "Search terms must be longer than 5 characters.";
   }else {
      $searchTermDB = mysql_real_escape_string($searchTerms); // prevent sql injection.
   }
   
   // If there are no errors, lets get the search going.
   if (count($error) < 1) {
      $searchSQL = "SELECT value FROM tblcustomfieldsvalues WHERE ";
      
      // grab the search types.
      $types = array();
      $types[] = isset($_GET['value'])?"`value` LIKE '%{$searchTermDB}%'":'';
      
      $types = array_filter($types, "removeEmpty"); // removes any item that was empty (not checked)
      
      if (count($types) < 1)
         $types[] = "`value` LIKE '%{$searchTermDB}%'"; // use the body as a default search if none are checked
      
          $andOr = isset($_GET['matchall'])?'AND':'OR';
      $searchSQL .= implode(" {$andOr} ", $types) . " ORDER BY `value`"; // order by title.

      $searchResult = mysql_query($searchSQL) or die("There was an error.<br/>" . mysql_error() . "<br />SQL Was: {$searchSQL}");
      
      if (mysql_num_rows($searchResult) < 1) {
         $error[] = "The search term provided {$searchTerms} yielded no results.";
      }else {
         $results = array(); // the result array
         $i = 1;
         while ($row = mysql_fetch_assoc($searchResult)) {
            $results[] = "{$i}: {$row['This']}";
            $i++;
         }
      }
   }
}

function removeEmpty($var) {
   return (!empty($var)); 
}
?>
<html>
   <title>My Simple Search Form</title>
   <style type="text/css">
      #error {
         color: red;
      }
   </style>
   <body>
      <?php echo (count($error) > 0)?"The following had errors:<br /><span id=\"error\">" . implode("<br />", $error) . "":""; ?>
      <form method="GET" action="<?php echo $_SERVER['PHP_SELF'];?>" name="searchForm">
         Search For: <input type="text" name="search" value="<?php echo isset($searchTerms)?$searchTerms:''; ?>" /><br />

         <br />
         <input type="submit" name="submit" value="Search!" />
      </form>
      <?php echo (count($results) > 0)?"Your search term: {$searchTerms} returned:<br /><br />" . implode("", $results):""; ?>
   </body>
</html>

php

2 Contributors
7 Replies
132 Views
11 Hours Discussion Span
Latest Post 14 Years Ago Latest Post by fwdean

All 7 Replies

fwdean 1 Newbie Poster

14 Years Ago

Why not normalize the database so there are no http:// or https:// in your search field? Once that is done, do a

str_replace("http://","",$searchTerm) && str_replace("https://","",$searchTerm)

on the user input.

The above would then match. You could take it a step further and check for the existance of www. in front of the domain (both in the database and in user input) to match even better.

fwdean 1 Newbie Poster

14 Years Ago

Skip the str_replace then and use something like this. This should also work for domains like http://www.example.co.uk

<?php
function tld_domain($host)
{
    if (preg_match('/([a-z0-9][a-z0-9\-]{1,63})\.([a-z]{3}|[a-z]{2}\.[a-z]{2})$/i', $host, $regs))
    {
        return $regs;
    }
    return false;
}

$fullDomain = "http://www.example.com";
$domainPieces = tld_domain($fullDomain);
$domain = $domainPieces[0];
echo $domain . "\n";
?>

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

valonesal 0 Junior Poster · Answer 1 · 2009-10-03T19:56:55+00:00

Ok better yet how would I do the latter and add add the check for existence of www. and http://

valonesal 0 Junior Poster · Answer 2 · 2009-10-03T20:37:34+00:00

How would I emplament that code as the code I use is calling from the database to return the results.

fwdean 1 Newbie Poster · Answer 3 · 2009-10-03T20:44:17+00:00

$searchTermDB = mysql_real_escape_string($searchTerms); // prevent sql injection

gets replaced by

$domainPieces = tld_domain($searchTerms);
$searchTermDB = mysql_real_escape_string($domainPieces[0]);

as well as putting that function at the bottom of your page

valonesal 0 Junior Poster · Answer 4 · 2009-10-03T21:16:22+00:00

When I insert your code as such

$dbHost = ''; // localhost will be used in most cases
// set these to your mysql database username and password.
$dbUser = ''; 
$dbPass = '*';
$dbDatabase = ''; // the database you put the table into.
$con = mysql_connect($dbHost, $dbUser, $dbPass) or die("Failed to connect to MySQL Server. Error: " . mysql_error());

mysql_select_db($dbDatabase) or die("Failed to connect to database {$dbDatabase}. Error: " . mysql_error());

// Set up our error check and result check array
$error = array();
$results = array();

// First check if a form was submitted. 
// Since this is a search we will use $_GET
if (isset($_POST['search'])) {
   $searchTerms = trim($_POST['search']);
   $searchTerms = strip_tags($searchTerms); // remove any html/javascript.
   
   if (strlen($searchTerms) < 5) {
      $error[] = "Search terms must be longer than 5 characters.";
   }else {
      $domainPieces = tld_domain($searchTerms);
$searchTermDB = mysql_real_escape_string($domainPieces[0]);
   }
   
   // If there are no errors, lets get the search going.
   if (count($error) < 1) {
      $searchSQL = "SELECT value FROM tblcustomfieldsvalues WHERE ";
      
      // grab the search types.
      $types = array();
      $types[] = isset($_GET['value'])?"`value` = '{$searchTermDB}'":'';
      
      $types = array_filter($types, "removeEmpty"); // removes any item that was empty (not checked)
      
      if (count($types) < 1)
         $types[] = "`value` = '{$searchTermDB}'"; // use the body as a default search if none are checked
      
          $andOr = isset($_GET['matchall'])?'AND':'OR';
      $searchSQL .= implode(" {$andOr} ", $types) . " ORDER BY `value`"; // order by title.

      $searchResult = mysql_query($searchSQL) or die("There was an error.<br/>" . mysql_error() . "<br />SQL Was: {$searchSQL}");
      
      if (mysql_num_rows($searchResult) < 1) {
         $error[] = "Sorry but we could not find <strong><u>{$searchTerms}</u></strong> in our database ";
      }else {
         $results = array(); // the result array
         $i = 1;
         while ($row = mysql_fetch_assoc($searchResult)) {
            $results[] = "{$i}: {$row['value']}";
            $i++;
         }
      }
   }
}

function removeEmpty($var) {
   return (!empty($var)); 
}
function tld_domain($host)
{
    if (preg_match('/([a-z0-9][a-z0-9\-]{1,63})\.([a-z]{3}|[a-z]{2}\.[a-z]{2})$/i', $host, $regs))
    {
        return $regs;
    }
    return false;
}

$fullDomain = "http://www.example.com";
$domainPieces = tld_domain($fullDomain);
$domain = $domainPieces[0];
echo $domain . "\n";
?>
<html>
<title>My Simple Search Form</title>
   <style type="text/css">
      #error {
         color: red;
      }
   </style>
<body>
      <form method="POST" action="simplesearch.php" name="searchForm">
         Search For: <input type="text" name="search" value="<?php echo isset($searchTerms)?$searchTerms:''; ?>" /><br />

         <br />
         <input type="submit" name="submit" value="Search!" />
      </form>
           <div> <?php echo (count($error) > 0)?"<span id=\"error\">" . implode("<br />", $error) . "</span><br /><br />Please feel free to report this site <a href=\"http://websiteguardian.org/contact.html\">here</a>.":""; ?></div>
      <?php echo (count($results) > 0)?"Your search term '{$searchTerms}' returned:<br /><br />{$searchTerms} is authorized to use 1 or more of our seals" :""; ?>
   </body>
</html>
<?php
function tld_domain($host)
{
    if (preg_match('/([a-z0-9][a-z0-9\-]{1,63})\.([a-z]{3}|[a-z]{2}\.[a-z]{2})$/i', $host, $regs))
    {
        return $regs;
    }
    return false;
}

$fullDomain = "http://www.example.com";
$domainPieces = tld_domain($fullDomain);
$domain = $domainPieces[0];
echo $domain . "\n";

It returns everything I type in including random numbers and letters as valid. I eventried adding your code to the very bottom and not where it is here.

fwdean 1 Newbie Poster · Answer 5 · 2009-10-03T22:14:20+00:00

I believe your original question was how to match http://awebsite.com to http://www.awebsite.com or awebsite.com.

I accomplished that, but it appears you have user input that you are not validating. Your original post said the user was entering a domain name. You must check that they actually did enter a domain name or web address.

I am simply removing the subdomain and protocol if it exists. You must also go through your database and update the rows to have the shortened domain as well.

Maybe a few rows of the relevant database table as well as the structure would help as well as a few search terms that you entered?

SQL Search script help

Recommended Answers Collapse Answers

All 7 Replies

Recommended Answers