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"> </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');
.....
......