Page 1 of 2
Best tutorials to help create a search engine for database?
Posted: Mon Feb 28, 2011 3:57 pm
by peachiness
Please help refer me to some!
Or help me answer how do I fix it so it can display results for search like 'AUTHOR 2009' instead of just for 'AUTHOR' or '2009'?
Current code:
Code: Select all
$query = "SELECT *
FROM publications
WHERE author1 LIKE '$trimmed'
OR year LIKE '$trimmed'";
Re: Best tutorials to help create a search engine for databa
Posted: Mon Feb 28, 2011 5:56 pm
by pickle
Dude - we need a lot more code to be able to help.
Re: Best tutorials to help create a search engine for databa
Posted: Wed Mar 02, 2011 8:46 am
by peachiness
pickle wrote:Dude - we need a lot more code to be able to help.
here is the rest!
Code: Select all
<?php
// Get the search variable from URL
$var = @$_GET['q'] ;
$trimmed = trim($var); //trim whitespace from the stored variable
// rows to return
$limit=10;
// check for an empty string and display a message.
if ($trimmed == "")
{
echo "<p>Please enter a search...</p>";
exit;
}
// check for a search parameter
if (!isset($var))
{
echo "<p>We dont seem to have a search parameter!</p>";
exit;
}
//connect to your database ** EDIT REQUIRED HERE **
mysql_connect("localhost", "", ""); //(host, username, password)
//specify database ** EDIT REQUIRED HERE **
mysql_select_db ("a2288820_data") or die("Unable to select database"); //select which database we're using
// Build SQL Query
$query = "SELECT *
FROM publications
WHERE author1 LIKE '$trimmed'
OR year LIKE '$trimmed'";
// EDIT HERE and specify your table and field names for the SQL query
$numresults=mysql_query($query);
$numrows=mysql_num_rows($numresults);
// If we have no results, offer a google search as an alternative
if ($numrows == 0)
{
echo "<h4>Results</h4>";
echo "<p>Sorry, your search: "" . $trimmed . "" returned zero results</p>";
// google
echo "<p><a href=\"http://www.google.com/search?q="
. $trimmed . "\" target=\"_blank\" title=\"Look up
" . $trimmed . " on Google\">Click here</a> to try the
search on google</p>";
}
// next determine if s has been passed to script, if not use 0
if (empty($s)) {
$s=0;
}
// get results
$query .= " limit $s,$limit";
$result = mysql_query($query) or die("Couldn't execute query");
// display what the person searched for
echo "<p>You searched for: "" . $var . ""</p>";
// begin to show results set
echo "Results";
$count = 1 + $s ;
// now you can display the results returned
while ($row= mysql_fetch_array($result)) {
echo "<tr>\n";
echo '<td>'.$row['year'].'<br /></td><td>'.$row['id'].'</td>';
echo "</tr>\n";
$s++;
}
$currPage = (($s/$limit) + 1);
//break before paging
echo "<br />";
// next we need to do the links to other results
if ($s>=1) { // bypass PREV link if s is 0
$prevs=($s-$limit);
print " <a href=\"$PHP_SELF?s=$prevs&q=$var\"><<
Prev 10</a>  ";
}
// calculate number of pages needing links
$pages=intval($numrows/$limit);
// $pages now contains int of pages needed unless there is a remainder from division
if ($numrows%$limit) {
// has remainder so add one page
$pages++;
}
// check to see if last page
if (!((($s+$limit)/$limit)==$pages) && $pages!=1) {
// not last page so give NEXT link
$news=$s+$limit;
echo " <a href=\"$PHP_SELF?s=$news&q=$var\">Next 10 >></a>";
}
$a = $s + ($limit) ;
if ($a > $numrows) { $a = $numrows ; }
$b = $s + 1 ;
echo "<p>Showing results $b to $a of $numrows</p>";
?>
Re: Best tutorials to help create a search engine for databa
Posted: Wed Mar 02, 2011 9:36 am
by pickle
It looks like you got this from a tutorial or pre-built script. As a result, it will be difficult for us to give you good help, as you likely don't have the skill level to be able to apply it (no offense intended).
I can't recommend any tutorials beyond anything you could find yourself.
Re: Best tutorials to help create a search engine for databa
Posted: Wed Mar 02, 2011 10:04 am
by peachiness
pickle wrote:It looks like you got this from a tutorial or pre-built script. As a result, it will be difficult for us to give you good help, as you likely don't have the skill level to be able to apply it (no offense intended).
I can't recommend any tutorials beyond anything you could find yourself.
Why don't you give it a shot and see what I can make of it.
Re: Best tutorials to help create a search engine for databa
Posted: Wed Mar 02, 2011 10:09 am
by pickle
You'll need to tokenize your search string so you can give each word it's own clause in the query. If you're just searching for the author and year, you should be able to compare non-integer words with your author field and any integer words with your year field.
If you're searching for other stuff as well, you'll pretty much need to search every field for every value. If this is the case, look into doing a FULLTEXT search instead.
Re: Best tutorials to help create a search engine for databa
Posted: Wed Mar 02, 2011 12:17 pm
by peachiness
pickle wrote:You'll need to tokenize your search string so you can give each word it's own clause in the query. If you're just searching for the author and year, you should be able to compare non-integer words with your author field and any integer words with your year field.
If you're searching for other stuff as well, you'll pretty much need to search every field for every value. If this is the case, look into doing a FULLTEXT search instead.
what do you think about this?
Code: Select all
$searchPieces = explode(" ",$search);
$year = NULL;
$searchTerm = "SELECT id,author1,keyword1,full reference,link FROM publications WHERE author1 LIKE '$search' AND keyword1 LIKE '$search'";
$searchTerm2 = "SELECT id,author1,keyword1,full reference,link FROM publications WHERE author1 LIKE '$search' OR keyword1 LIKE '$search'";
foreach($searchPieces as $val){
$val = trim($val);
if(strlen($val) == 4 && is_numeric($val)){
$searchTerm = $searchTerm . " AND year >= '$val'";
$searchTerm2 = $searchTerm . " AND year >= '$val'";
break;
}
}
$indexList = NULL;
$query2 = mysql_db_query("a2288820_data",$searchTerm);
while($row = mysql_fetch_row($query)){
$authorList = explode(",",$row[5]); //This is the array of authors
$keywordList = explode(",",$row[25]); //This is the array of keywords
$indexList[] = $row[0]; //This adds the index to an array to check later on for results that "aren't as good."
$fullReference = $row[3]; //This is the full reference string, not split into an array
$link = $row[4]; //This is the pdf link
/*
//Use this to iterate through the data if you need to
foreach($authorList as $val){
if(trim(strtolower($val)) == trim((strtolower($search)))) {
//result found
}
}*/
}
$query2 = mysql_db_query("a2288820_data","SELECT id,authors,keywords FROM publications WHERE authors LIKE '$search' OR keywords LIKE '$search'");
while($row = mysql_fetch_row($query2)){
if(array_search($row[0],$indexList) === FALSE){ //We haven't returned the results of this already
$authorList = explode(",",$row[5]);
$keywordList = explode(",",$row[25]);
$fullReference = $row[3]; //This is the full reference string, not split into an array
$link = $row[4]; //This is the pdf link
if(strlen($row[0]) > 0){
//Authors exists
}
else{
//Keywords exists
}
}
}
Re: Best tutorials to help create a search engine for databa
Posted: Wed Mar 02, 2011 12:20 pm
by pickle
Does it do what you want?
Re: Best tutorials to help create a search engine for databa
Posted: Wed Mar 02, 2011 1:31 pm
by peachiness
pickle wrote:Does it do what you want?
I keep getting an error, but I think it's SO CLOSE to working..
Re: Best tutorials to help create a search engine for databa
Posted: Wed Mar 02, 2011 2:04 pm
by pickle
And that error would be....?
Re: Best tutorials to help create a search engine for databa
Posted: Wed Mar 02, 2011 2:25 pm
by peachiness
pickle wrote:And that error would be....?
Sorry, your search: "rister 2002" returned zero results.
Here is the full set of code
Code: Select all
<?php
// Get the search variable from URL
$var = @$_GET['q'] ;
$trimmed = trim($var); //trim whitespace from the stored variable
// rows to return
$limit=10;
// check for an empty string and display a message.
if ($trimmed == "")
{
echo "<p>Please enter a search...</p>";
exit;
}
// check for a search parameter
if (!isset($var))
{
echo "<p>We dont seem to have a search parameter!</p>";
exit;
}
//connect to your database ** EDIT REQUIRED HERE **
mysql_connect("localhost", "", ""); //(host, username, password)
//specify database ** EDIT REQUIRED HERE **
mysql_select_db ("a2288820_data") or die("Unable to select database"); //select which database we're using
// Build SQL Query
$searchPieces = explode(" ",$trimmed);
$query = "SELECT *
FROM publications
WHERE author1 LIKE '$trimmed' OR year LIKE '$trimmed'";
$query2 = "SELECT *
FROM publications
WHERE author1 LIKE '$trimmed' and year LIKE '$trimmed'";
// EDIT HERE and specify your table and field names for the SQL query
foreach($searchPieces as $val){
$val = trim($val);
if(strlen($val) == 4 && is_numeric($val)){
$query = $query . " AND year >= '$val'";
$query2 = $query2 . " AND year >= '$val'";
break;
}
}
$indexList = NULL;
$query2 = mysql_db_query("a2288820_data",$query);
while($row = mysql_fetch_row($query)){
$authorList = explode(",",$row[5]); //This is the array of authors..but how do I get this from a different table??
$keywordList = explode(",",$row[25]); //This is the array of keywords
$indexList[] = $row[0]; //This adds the index to an array to check later on for results that "aren't as good."
$fullReference = $row[3]; //This is the full reference string, not split into an array
$link = $row[4]; //This is the pdf link
/*
//Use this to iterate through the data if you need to
foreach($authorList as $val){
if(trim(strtolower($val)) == trim((strtolower($search)))) {
//result found
}
}*/
}
$numresults=mysql_query($query);
$numrows=mysql_num_rows($numresults);
// If we have no results, offer a google search as an alternative
if ($numrows == 0)
{
echo "<h4>Results</h4>";
echo "<p>Sorry, your search: "" . $trimmed . "" returned zero results</p>";
// google
echo "<p><a href=\"http://www.google.com/search?q="
. $trimmed . "\" target=\"_blank\" title=\"Look up
" . $trimmed . " on Google\">Click here</a> to try the
search on google</p>";
}
// next determine if s has been passed to script, if not use 0
if (empty($s)) {
$s=0;
}
// get results
$query .= " limit $s,$limit";
$result = mysql_query($query) or die("Couldn't execute query");
// display what the person searched for
echo "<p>You searched for: "" . $var . ""</p>";
// begin to show results set
echo "Results";
$count = 1 + $s ;
// now you can display the results returned
while ($row= mysql_fetch_array($result)) {
echo "<tr>\n";
echo '<td>'.$row['year'].'<br /></td><td>'.$row['id'].'</td>';
echo "</tr>\n";
$s++;
}
$currPage = (($s/$limit) + 1);
//break before paging
echo "<br />";
// next we need to do the links to other results
if ($s>=1) { // bypass PREV link if s is 0
$prevs=($s-$limit);
print " <a href=\"$PHP_SELF?s=$prevs&q=$var\"><<
Prev 10</a>  ";
}
// calculate number of pages needing links
$pages=intval($numrows/$limit);
// $pages now contains int of pages needed unless there is a remainder from division
if ($numrows%$limit) {
// has remainder so add one page
$pages++;
}
// check to see if last page
if (!((($s+$limit)/$limit)==$pages) && $pages!=1) {
// not last page so give NEXT link
$news=$s+$limit;
echo " <a href=\"$PHP_SELF?s=$news&q=$var\">Next 10 >></a>";
}
$a = $s + ($limit) ;
if ($a > $numrows) { $a = $numrows ; }
$b = $s + 1 ;
echo "<p>Showing results $b to $a of $numrows</p>";
?>
Re: Best tutorials to help create a search engine for databa
Posted: Wed Mar 02, 2011 2:28 pm
by pickle
Output the query to the screen & examine it - that usually gives a good idea why it's not working. Paste the resulting query here as well.
Re: Best tutorials to help create a search engine for databa
Posted: Wed Mar 02, 2011 2:36 pm
by peachiness
pickle wrote:Output the query to the screen & examine it - that usually gives a good idea why it's not working. Paste the resulting query here as well.
what do you mean output to the screen?
Re: Best tutorials to help create a search engine for databa
Posted: Wed Mar 02, 2011 2:40 pm
by pickle
echo() or print()
Re: Best tutorials to help create a search engine for databa
Posted: Wed Mar 02, 2011 3:10 pm
by peachiness
pickle wrote:echo() or print()
Right, I had the results echoed here
Code: Select all
while ($row= mysql_fetch_array($result)) {
echo "<tr>\n";
echo '<td>'.$row['year'].'<br /></td><td>'.$row['id'].'</td>';
echo "</tr>\n";
$s++;
}
When I search for rister
I get
You searched for: "popp"
You searched for: "popp"
Results 2004
30 2003
32 2003
103
<< Prev 10
Showing results 4 to 3 of 3
When I search for 2003
I get
You searched for: "2003"
Results 2003
32 2003
33 2003
66 2003
67 2003
68 2003
69 2003
70 2003
71 2003
72 2003
73
<< Prev 10
Showing results 11 to 18 of 18
When I search for popp 2003
I get
Results
Sorry, your search: "popp 2003" returned zero results
Click here to try the search on google
You searched for: "popp 2003"
Results
Next 10 >>
Showing results 1 to 0 of 0
Evidently, I should be getting ID 32 since it matches both searchs