Using MySQL for search queries

[pratik_learner]

Hi, I am working on a PHP-search script (http://www.webdeveloper.com/forum/history/topic.php/89707-1.html) that uses MySQL as a backend.
I am thinking of using the MySQL function to search using MySQL and then calculate relevancy using PHP by getting the results. I want to know how does one implement AND OR and NOT searches in MySQL. I am not familiar with the MySQL RLIKE syntax and it seems LIKE is not upto the task.
By the way I am a MySQL dweeb I don't a lot about it.
So can anybody help me out?

[NogDog]

You might want to look into the MySQL Full Text Search Functions (http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html). Note that they only work with MyISAM tables.

[chazzy]

could you give an example of the column type and what you want to search for?

[pratik_learner]

Path text
Body mediumtext
Title mediumtext
Descr mediumtext
Keywords text
Author text
Title2 mediumtext

HI here's a list of tables.
How do i get all the AND terms to be searched?? I am trying things out here.

[pratik_learner]

Hey does anybody know how that search fn works in phpMyAdmin. Can somebody tell me where it exactly is( i mean which file ) and how I can get to know the working of it?

[jonra]

What's going wrong with LIKE? I've used it quite a bit in our search pages and it generally does the trick. Sometimes you have to check for things like % and other special charactars being passed into the query, but I have never run into major problems with it. You should be able to use AND along with it... "WHERE searchText LIKE '%$searchVar%' AND searchText2 LIKE '%$searchVar2%' ... etc. Is there an algorithmic problem I'm missing with this method maybe?

[chazzy]

You still haven't said what you're trying to search for...

[pratik_learner]

nothing really
I don't know what really has to be searched by the way I am thro' getting the AND-terms and now NOT and OR don't seem difficult as I mentioned in the above post the names of the columns.

The thing is that the MySQL would first get through an ACID test to get relevant results then PHP would load em and use all those relevancy algorithms and get the results. Also I am looking at the ORDER syntax for further possibilites.

Yeah and do the query results from PHP point to the MySQL fields in the DB (as in a pointer to a particular field/row whatever) or do they load it freshly into memory like normal variables. :confused:

Yes i'd like if somebody comes up to help wid the proj.

[pratik_learner]

Yes there aren't any 'algorithmic' problems with LIKE ( don't know for efficiency and speed but otherwise OK ).

Is this following piece of supposed to be ok?

//Sample AND array
$ANDarray=array("Home","Package","Trade");
//Sample OR array
$ORarray=Array ("dos theirry","catos onry","buel braca");
//Sample NOT array
$NOTarray=array("brugge", "bayenr", "mnuich");

//Description of Columns
$columns=array("Body","Title","Path","Descr","Keywords","Author","Title2");

//Start initial query syntax
$query="SELECT * FROM table1 WHERE (";

//AND keyword to MySQL query converter
for($i=0;$i<$cnt=count($ANDarray);$i++)
{
$query.=$columns[0]." LIKE \"% ".$ANDarray[$i]." %\"";
if($i!==$cnt-1)
$query.=" AND ";
}

//End Body AND parantheses
$query.=") OR (";

//Repeat identical process for OR array
for($i=0;$i<$cnt=count($ORarray);$i++)
{
$ORterms=explode(" ",$ORarray[$i]);
$query.=$columns[0]." RLIKE \" ".$ORterms[0]." | ".$ORterms[1]." \"";
if($i!==$cnt-1)
$query.=" OR ";
}

//End Body OR parantheses
$query.=") NOT (";

//Repeat identical process for OR array
for($i=0;$i<$cnt=count($NOTarray);$i++)
{
$query.=$columns[0]." LIKE \" ".$NOTarray[$i]." \"";
if($i!==$cnt-1)
$query.=" NOT ";
}
$query.=" )";
//End query with semi-colon
$query.=";";
print_r($query);

[chazzy]

are you trying to find only the fields that the input parameters are LIKE the search term, but not exact terms?