querying a database with keywords
Moderator: General Moderators
querying a database with keywords
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
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
Re: querying a database with keywords
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)
Re: querying a database with keywords
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.
- aceconcepts
- DevNet Resident
- Posts: 1424
- Joined: Mon Feb 06, 2006 11:26 am
- Location: London
Re: querying a database with keywords
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
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.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 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
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
let me know what you think
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"> </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>
- novice4eva
- Forum Contributor
- Posts: 327
- Joined: Thu Mar 29, 2007 3:48 am
- Location: Nepal
Re: querying a database with keywords
It would roughly look like this:
Hope it helps
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');
......................
.......
}
Re: querying a database with keywords
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
If you're doing a lot of SQL work imploding arrays of statements works better (in my opinion).
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);
}
Re: querying a database with keywords
yea thanks, that does help alot, this has been giving me nightmares
Re: querying a database with keywords
Ok so i have it building the arrays, but its just dumping the arrays into my database, and not returning my results
any suggestions?
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";
}
}
?>- novice4eva
- Forum Contributor
- Posts: 327
- Joined: Thu Mar 29, 2007 3:48 am
- Location: Nepal
Re: querying a database with keywords
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');
.....
......