simple search engine

[dipika]

I am developing a simple search engine using php.
what is the logic behind searching with "AND" and "parenthesis" advance search

[james438]

Considering you are posting in this forum I will assume that you are also using MySQL as well. The fundamental formula to begin a search engine for your website looks like this:


SELECT column
FROM table
WHERE column LIKE '%term%'
The % acts as a wild card. You can elaborate on this indefinitely


SELECT column
FROM table
WHERE column LIKE '%term%'
AND column LIKE '%word%'
AND (column NOT LIKE '%bad word%' OR column NOT LIKE '%bad term%')
The above is an example that uses both parentheses and AND. I separated the code to different lines for readability. The MySQL words were capitalized merely to set them apart. In this second example we are gathering all of the rows where "column" contains the terms "term" as well as "word" and only one of the following two terms: "bad word" or "bad term", but never all 4 terms together in one field. The only result displayed from each row retrieved will be from the field "column".

I hope this makes sense.

[djr33]

In my opinion there is no such thing as a "simple" search engine. They're always at least a little complicated. If you allow for exactly one term then using LIKE "%term%" is a fairly easy way. But beyond that then it gets complicated.

Is the answer above what you were looking for or were you wondering about user input involving AND and ()? User input is complicated. You will need patience and lots of string functions or regex to parse the input. Then you'll need to generate something like what Traq has above.

A while ago I wrote my own user search to query conversion functions. It was complicated, but they work pretty well. I'm surprised by how fast MySQL is. Depending on what you need, I could share those with you. I think I posted them here while I was working on them and possibly the final versions. Let me know (reply) if that would help.

[james438]

While I agree with your statement djr33, I want to start simple so as not to discourage him if he wants to start down this road. This was how I started; with something simple, which became much more complicated as I came to a better understanding of how I wanted it to work.

The thread you are referring to is here (http://www.dynamicdrive.com/forums/showthread.php?t=54348).

There are other search scripts out there. When I started working on a search script for my site I wanted to have a search script that would pull results in real time, one that would search for precisely what I searched for as opposed to an approximation, one where I could search for phrases or negate phrases or individual terms. I wanted the results to be manageable visibly yet complete. I wanted the user to have some control over which tables were searched, such as anime, news, or articles. I also wanted to have a simple to use admin control panel to make updating how the control operates simple to operate. For these reasons I created my own.

There are other plugins out there, such as a google plugin to search your site if your requirements are not as stringent or another, which I have not tried called sphider. I believe I skipped sphider, because I wanted to be more intimately involved with the security of the script.

I wish I could share my script with you, but I'd rather not until I get a minor, but quite complicated, security upgrade in place, which should take a few more days yet. Basically, when the user is not logged in the results returned from a search will come back and the hidden results will not be retrieved, which is as it should, but instead of saying that there are 4 results and display 4 results it will say that 5 results were retrieved when only 4 are displayed. The reason the number is 5 is that it is counting the hidden results that should only be visible to the admin. This came about after normalizing a few of my database tables.

I'm talkative tonight.

[dipika]

Thanks for the refered thread. but i am still confused in "AND" searching.
suppose user input is "(events And news) or local".
i wrote a sql like this:
SELECT * FROM `page` AS a1 LEFT JOIN `page_tag` AS a2 ON a2.page_id=a1.id WHERE ((Tag_id= 3 and Tag_id= 1) or Tag_id= 5)

i have generated the above where statement using str_replace function and
tag_id are the id of the above keyword.

sice both events and local are in the same table and same field. tag_id can't be two different value at a same time.

How can i get the result of AND operation.

[james438]

I'm not sure if you can pull results from multiple categories using MySQL. I wouldn't be surprised if I am wrong, though, either.

I would perform two queries. The first would pull page_id where Tag_id= 3 or Tag_id= 1 then put these results into an array and retrieve only the duplicate results.

The second query would pull page_id where Tag_id=5. Combine the results from array1 and array2. This should give you an array of the page_id that you want.

AND just means that both events are true.

((a='b' AND a='c') or a='d')

In the above the condition is that col a must = both 'a' and 'c' at the same time. The OR located outside of the parentheses means that if the first condition is not met then see if the second condition is true where a='d'. If neither is valid then do not pull the result.

[dipika]

In my opinion there is no such thing as a "simple" search engine. They're always at least a little complicated. If you allow for exactly one term then using LIKE "%term%" is a fairly easy way. But beyond that then it gets complicated.

Is the answer above what you were looking for or were you wondering about user input involving AND and ()? User input is complicated. You will need patience and lots of string functions or regex to parse the input. Then you'll need to generate something like what Traq has above.

A while ago I wrote my own user search to query conversion functions. It was complicated, but they work pretty well. I'm surprised by how fast MySQL is. Depending on what you need, I could share those with you. I think I posted them here while I was working on them and possibly the final versions. Let me know (reply) if that would help.
yes,it would be really helpful. Actually i want to use user input and i tried to parse the input and finally created a query from that but its not working.
Thanks a lot

[dipika]

I'm not sure if you can pull results from multiple categories using MySQL. I wouldn't be surprised if I am wrong, though, either.

I would perform two queries. The first would pull page_id where Tag_id= 3 or Tag_id= 1 then put these results into an array and retrieve only the duplicate results.

The second query would pull page_id where Tag_id=5. Combine the results from array1 and array2. This should give you an array of the page_id that you want.

AND just means that both events are true.

((a='b' AND a='c') or a='d')

In the above the condition is that col a must = both 'a' and 'c' at the same time. The OR located outside of the parentheses means that if the first condition is not met then see if the second condition is true where a='d'. If neither is valid then do not pull the result.

Actually ,user inputs the search statement. then how do I parse input string .

[dipika]

$len=strlen($word);
for($i=0;$i<=$len;$i++)
{
if($word[$i]=='(' )
{
$bracket[]=$word[$i];
}
if($word[$i]=="+" or $word[$i]=="|" or $word[$i]==$word[$len])
{
$wordes[]=$part;
$part="";

}
if (ctype_alpha($word[$i]))
{
$part.= $word[$i];
}

}
$count=count($wordes);
$j=0;
for($j=0;$j<=$count;$j++)
{
$page=select * from tag where('name=%s ',$wordes[$j])->getXml();

foreach($page->Tag as $tagid)
{
$id[]=$tagid['id'];
}

}
$query=str_replace('+'," and ",$word);
$query1=str_replace('|'," or ",$query);
for($i=0;$i<$count;$i++)
{
$query1=str_replace($wordes[$i], "Tag_id= ".$id[$i], $query1);
}
//echo $query1;
echo $query1;
$page=select *from page LEFT JOIN page_tag where( $query1 );


This is the query i used to parse my input string

[djr33]

See the link to the thread above. If you want to do a full scale search engine, there is a lot of information there and some working code toward the end. I'm not sure that's my newest version but it is working. If you need the finalized version I could send that to you. At the moment I'm a bit busy.
If you want to try something easier, look into using a single term and using LIKE "%TERM%" as explained above. There is no easy way to make a comprehensive accurate search engine. It's just difficult. I don't mean at all to say you shouldn't try, but also don't think you'll find a shortcut. It's a lot of work, but very useful when completed.

Take the time to read and understand (most of) the other thread and if that helps, great. If not, try something simpler at least for now. I've been using PHP for years and that was difficult for me. I'm just being honest-- again, I'm not suggesting you should give up or anything.


It is possible to compare things across tables in MySQL using temporary tables and so forth, but it's complicated and not particularly efficient. Think about ways you might make it more efficient, and if you can't find anything then look into advanced MySQL queries. There's a lot you can do in MySQL beyond the basics, but it's also not needed that often-- and I don't understand most of it. A lot of the time you can get away with using PHP to do the more complicated things, although you lose some efficiency that way as well. The best strategy is a well organized database, and that just takes experience with various data structures to figure out what will best fit your project.

[dipika]

when we will get free please help me. you can mail me at dipika_44@live.com

[djr33]

Please review that thread and post here to let us know if that is the information you need. What I can offer is the newest version of those search functions. I'll try to get those ready soon. I'm away from my computer at the moment, so please remind me on Tuesday to do this. Just post here, or send me a PM. But until then please look through that other thread and see if what is there will help. What I have now is almost the same, just some slight modifications as I've used the scripts.

[james438]

Actually ,user inputs the search statement. then how do I parse input string .I was assuming that you had already parsed the script. I would work on that before what attempting I suggested. If I were to help you with the parsing I would need to know more about what you are looking for in a parsed result.

I would like to agree with djr33 as far as the difficulty of this project. By far the search script was the most complicated thing I have worked on with programming and it integrated most everything I know about scripting. Every time I go back to work updating my search script the task is daunting. I have spent a lot of time rewriting it to be as simple as I can make it with comments throughout and streamlined the code as well. All of that helped tremendously, but the script is quite complicated and spread over 5 different files.

[dipika]

I followed the link above and this thread was really helpful and finally i was able to develop a query like this:
SELECT * FROM `page` AS a1 LEFT JOIN `page_tag` AS a2 ON a2.page_id=a1.id LEFT JOIN `tag` AS a3 ON a3.id=a2.tag_id WHERE (( a3.Name like "%events%" and a3.Name like "%news%" ) or a3.Name like "%local%").


I am getting the answer of the OR part but it is not retriving any value for the AND part.

Do i need to make any changes.

[djr33]

There are two completely separate parts to a search engine: 1. the actual MySQL query; 2. the user input.

The first step is to figure out what your query will look like. The second is to generate a query of that type from the user input. Both are hard.

Your AND and OR logic seems fine. But it's much harder to parse user input to work like that. To find out how it all works just create a test database and play with it. You'll learn quickly that way.

[djr33]

There are two completely separate parts to a search engine: 1. the actual MySQL query; 2. the user input.

The first step is to figure out what your query will look like. The second is to generate a query of that type from the user input. Both are hard.

Your AND and OR logic seems fine. But it's much harder to parse user input to work like that. To find out how it all works just create a test database and play with it. You'll learn quickly that way.

[james438]

This part
( a3.Name like "%events%" and a3.Name like "%news%" )
if I understand your database structure correctly will never be true unless at least one of your a.3Name fields contains both terms "events" and "news".

The reason this will never be true in your case is because your a3.Name field will only ever be composed of one category name. Am I correct in assuming that you're using a normalized table?

[djr33]

My server was down for a couple days but now I have had a chance to look at the latest version of the search script that I have. I'll post it now in the other thread, so please look there for more information. It may or may not answer your current question, but at least it should help. Also, the script may look busy but that's because I comment every line-- that makes it very easy to understand if you go through it slowly.

[dipika]

Thanks a lot for your concern. whats the name of the thread

[djr33]

As posted earlier by James:
http://www.dynamicdrive.com/forums/showthread.php?t=54348

[dipika]

thanks a lot for your help.finally i am able to solve my problem. this thread is really helpful

[dipika]

<?php
class DB{

private $link;
public function DB() {
$db = new DB();
}
public static function connect($host,$user,$passwd,$dbname) {
//$db=new DB();
$db->link = new mysqli($host,$user,$passwd,$dbname);
return $db;
}
public function query($sql) {
return $this->link->query($sql);
}

}
?>

[JShor]

Deja vous?

http://www.dynamicdrive.com/forums/showthread.php?t=63709