querying a database with keywords

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
DjMonkey
Forum Newbie
Posts: 6
Joined: Sun Oct 12, 2008 7:24 pm

querying a database with keywords

Post 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
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: querying a database with keywords

Post 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.
(#10850)
User avatar
omniuni
Forum Regular
Posts: 738
Joined: Tue Jul 15, 2008 10:50 pm
Location: Carolina, USA

Re: querying a database with keywords

Post 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.
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Re: querying a database with keywords

Post 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
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: querying a database with keywords

Post 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".
DjMonkey
Forum Newbie
Posts: 6
Joined: Sun Oct 12, 2008 7:24 pm

Re: querying a database with keywords

Post 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
User avatar
novice4eva
Forum Contributor
Posts: 327
Joined: Thu Mar 29, 2007 3:48 am
Location: Nepal

Re: querying a database with keywords

Post 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
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: querying a database with keywords

Post 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).
DjMonkey
Forum Newbie
Posts: 6
Joined: Sun Oct 12, 2008 7:24 pm

Re: querying a database with keywords

Post by DjMonkey »

yea thanks, that does help alot, this has been giving me nightmares
DjMonkey
Forum Newbie
Posts: 6
Joined: Sun Oct 12, 2008 7:24 pm

Re: querying a database with keywords

Post 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?
User avatar
novice4eva
Forum Contributor
Posts: 327
Joined: Thu Mar 29, 2007 3:48 am
Location: Nepal

Re: querying a database with keywords

Post 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');
 
    .....
......
 
Post Reply