Page 1 of 1

querying a database with keywords

Posted: Mon Oct 20, 2008 11:26 pm
by DjMonkey
hey, i am writing a database that i want someone to be able to search within it from a search box, and display the results. i have my table set up, and one of my fields is a discription field, so thats the field that i want to be search in when a search phrase is entered, now will i have to strtok the intput to the database, or can if i put peter piper picked a pepper in the database, and some searched for peter, or piper, or picked, or a, or pepper, it would display that. thanks

Re: querying a database with keywords

Posted: Mon Oct 20, 2008 11:57 pm
by Christopher
You can use "description LIKE '%peter%' to find all the records that contain the word 'peter' in the description column. Remember to filter and escape your search string before using it in SQL.

Re: querying a database with keywords

Posted: Tue Oct 21, 2008 12:00 am
by omniuni
Fetch the results and form an array. Use a function like substr_count() to see if there is an occurrence, or several occurrences. Put these results into an array as you find them. Order by number of occurrences, and display results.

Re: querying a database with keywords

Posted: Tue Oct 21, 2008 5:07 am
by aceconcepts
Not sure how far you want to go with the search feature, but take a look at http://dev.mysql.com/doc/refman/5.0/en/ ... earch.html

Re: querying a database with keywords

Posted: Tue Oct 21, 2008 5:14 am
by onion2k
DjMonkey wrote:hey, i am writing a database that i want someone to be able to search within it from a search box, and display the results. i have my table set up, and one of my fields is a discription field, so thats the field that i want to be search in when a search phrase is entered, now will i have to strtok the intput to the database, or can if i put peter piper picked a pepper in the database, and some searched for peter, or piper, or picked, or a, or pepper, it would display that. thanks
You'll have to break the terms up, but you don't need to go as far as using strtok(). explode() on a space will work just as well... then you can use the array of search terms to build up your SQL query.

You only need to resort to something like strtok() for things like quoted terms ... eg a search for "peter piper" picked a "pickle pepper" searching for "peter piper", or "picked", or "a", or "pickled pepper".

Re: querying a database with keywords

Posted: Tue Oct 21, 2008 11:23 pm
by DjMonkey
how would i use the explode() to search the table, do you know of any sample code?

im still fairly new to php, but heres my code that i have so far

Code: Select all

 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Coupon Database</title>
</head>
<body>
<?php
include_once "opendb.php";
 
?>
<form name="CouponSearch" action = "<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
  <table width="550" border="0" cellpadding="2" cellspacing="1">
    <tr>
      <td width="100">Search</td>
      <td><input name="txtSearch" type="text" size="30" value="<?php if (isset($_POST['txtSearch'])) { echo $_POST['txtSearch']; } else { echo '';} ?>" /></td>
    </tr>
    
    <?php
    /*  $words = $_POST['txtSearch'];
 
        $tok = strtok($words, " " . ",");
 
        while ($tok !== false) {
            echo "$tok<br />";
            $tok = strtok(" " . ",");
        } */
    ?>
 
    
    <tr>
      <td width="100">&nbsp;</td>
      <td><p class="submit"> <input name="btnSearch" type="submit" value="Search" /></td>
      </p>
    </tr>
  </table>
</form>
</body>
<html>
<?php
if(isset($_POST['btnSearch'])) {
    $query = "SELECT `Coupon_offer`, `Release_Date`, `Expires_on`, `Value`, `Source` FROM `Coupons`, `CouponSources` where `Coupons`.`Coupon_source_ID` = `CouponSources`.`ID`";
 
    $result = mysql_query($query) or die('Error, query failed');
 
    if(mysql_num_rows($result) == 0) { ?>
<p><br>
  <br>
  Database is empty</p>
    <?php }  else {
        echo "<table width=\"100%\" cellspacing=\"0\" border=\"1\" cellpadding=\"2\">\n";
        while($row = mysql_fetch_array($result)) {
        
            $tablerow = "<tr><td>" . $row['Coupon_offer'] . "</td><td align=\"center\">" . $row['Release_Date'] . "</td><td align=\"center\">" . $row['Expires_on'] . "</td><td align=\"right\">" . sprintf("$%01.2f", $row['Value']);
            $tablerow.= "</td><td>" . $row['Source'] . "</td></tr>\n";
            
            echo $tablerow;
        }
        echo "</table>\n";
    }
}
 
?>
</td>
</tr>
</table>
<br>
</body>
</html>
 
let me know what you think

Re: querying a database with keywords

Posted: Wed Oct 22, 2008 12:02 am
by novice4eva
It would roughly look like this:

Code: Select all

 
if(isset($_POST['btnSearch'])) 
{
$query='Select .... FROM ... WHERE ';
$searhArray = explode(' ',$_POST['txtSearch']);
$searchArrayCount = count($searhArray);
foreach($searhArray as $key=>$val)
{
   $query.="tableColumnName LIKE '%".$val."'";
   if($key!=($searchArrayCount-1))
      $query.=" OR ";
}
$result = mysql_query($query) or die('Error, query failed');
......................
.......
}
 
Hope it helps

Re: querying a database with keywords

Posted: Wed Oct 22, 2008 3:03 am
by onion2k
Rather than all that string concatenation and checking to see if the value is the first one or not you can just chuck everything into an array and then implode it.. eg

Code: Select all

$sql  = "SELECT * FROM `table` WHERE 1 ";
if (!empty($_POST['terms'])) {
  $arr_where = array();
  $terms = explode(" ", $_POST['terms']);
  foreach ($terms as $t) {
    $arr_where[] = "`column` LIKE '".mysql_real_escape_string($t)."'";
  }
  $sql .= "AND ".implode(" OR ", $arr_where);
}
 
If you're doing a lot of SQL work imploding arrays of statements works better (in my opinion).

Re: querying a database with keywords

Posted: Wed Oct 22, 2008 11:31 am
by DjMonkey
yea thanks, that does help alot, this has been giving me nightmares

Re: querying a database with keywords

Posted: Fri Oct 24, 2008 6:05 pm
by DjMonkey
Ok so i have it building the arrays, but its just dumping the arrays into my database, and not returning my results

Code: Select all

<?php
if(isset($_POST['btnSearch'])) {
    $query = "SELECT `Coupon_offer`, `Release_Date`, `Expires_on`, `Value`, `Source` FROM `Coupons`, `CouponSources` where `Coupons`.`Coupon_source_ID` = `CouponSources`.`ID`";
    
if (!empty($_POST['terms'])) {
   $arr_where = array();
   $terms = explode(" ", $_POST['terms']);
   foreach ($terms as $t) {
     $arr_where[] = "`column` LIKE '".mysql_real_escape_string($t)."'";
   }
   $sql .= "AND ".implode(" OR ", $arr_where);
 }
 
    $result = mysql_query($query) or die('Error, query failed');
 
    if(mysql_num_rows($result) == 0) { ?>
<p><br>
  <br>
  Database is empty</p>
    <?php }  else {
        echo "<table width=\"100%\" cellspacing=\"0\" border=\"1\" cellpadding=\"2\">\n";
        while($row = mysql_fetch_array($result)) {
        
            $tablerow = "<tr><td>" . $row['Coupon_offer'] . "</td><td align=\"center\">" . $row['Release_Date'] . "</td><td align=\"center\">" . $row['Expires_on'] . "</td><td align=\"right\">" . sprintf("$%01.2f", $row['Value']);
            $tablerow.= "</td><td>" . $row['Source'] . "</td></tr>\n";
            
            echo $tablerow;
        }
        echo "</table>\n";
    }
}
 
?>
any suggestions?

Re: querying a database with keywords

Posted: Wed Nov 05, 2008 6:18 am
by novice4eva

Code: Select all

 
<?php
if(isset($_POST['btnSearch'])) {
    $query = "SELECT `Coupon_offer`, `Release_Date`, `Expires_on`, `Value`, `Source` FROM `Coupons`, `CouponSources` where `Coupons`.`Coupon_source_ID` = `CouponSources`.`ID`";
   
if (!empty($_POST['terms'])) {
   $arr_where = array();
   $terms = explode(" ", $_POST['terms']);
   foreach ($terms as $t) {
 
     /* $arr_where[] = "`column` LIKE '".mysql_real_escape_string($t)."'"; AND IS column THE ACTUAL COLUMN NAME YOU WANT TO SEARCH IN??? */
     $arr_where[] = "`column` LIKE '%".mysql_real_escape_string($t)."'%";
   }
   $sql .= "AND ".implode(" OR ", $arr_where);
 }
 /* ADD THIS LINE */
   $query.=$sql;
 
    $result = mysql_query($query) or die('Error, query failed');
 
    .....
......