[Resolved] MySQL SELECT and advanced searching q's

phence

I am trying to create some advanced searching options for my users to search the database.

As of now they can only search one field of the database, which is the 'title' field. i would like to expand the searching capabilities to the 'description1' and 'description2' fields.

Here is my current Query Statement:
"SELECT * FROM mytable WHERE MATCH (title) AGAINST ('$searchValue') AND status = ('on') AND exp > curdate() ORDER BY (exp) ASC"

I have tried things such as:
"(SELECT FROM mytable WHERE MATCH (title) AGAINST ('$searchValue') AND status = ('on') AND exp > curdate())
UNION
(SELECT FROM mytable WHERE MATCH (description1) AGAINST ('$searchValue') AND status = ('on') AND exp > curdate())
ORDER BY (exp) ASC"

I don't get a syntax error, but no results are ever returned.

Also i noticed if i search for 'dog' and in the database there are results like 'black dog' or 'big dog'. The query returns with no results.
But if i search for 'black' the query returns 1 result, or any other results that would contain a title that begins with 'black'

How can I have my queries return results where if any word that is searched for by a user can be found within either the title, description1, or description2 fields is returned as a result?

Hope all that is clear... Thank-you!

Kudose

I found this tutorial rather helpful when I needed to create a search.

You'll need to exand on it a little though.

Personally, I just concatenated my strings into one, then exploded them into an array. My code will look a little confusing until your read the tutorial.

http://moskalyuk.com/php/yourownsearchengine.htm

$results = array();
          $arr = strtolower($name." ".$csz." ".$pro);
          $qarr1 = explode(" ", $arr);
          $qnum = count($qarr1);
          for($i=0;$i<$qnum;$i++){
            if(empty($qarr1[$i]))
              unset($qarr1[$i]);
          }
          $qarr = array();
          foreach($qarr1 as $key => $val){
            $qarr[] = $val;
          }
          $qnum = count($qarr);

      if($pro == "")
        $query = mysql_query("SELECT id, categorykey, name, csz FROM members");
      if($pro !== "")
        $query = mysql_query("SELECT id, categorykey, name, csz FROM members WHERE categorykey='".$pro."'");
      $tot_rows = mysql_num_rows($query);

      for($c=0; $c<$tot_rows; $c++){
         $relevancy = 0;
         $row = mysql_fetch_array($query);
         $mid = $row['id'];
         $rarr = strtolower($row['name']." ".str_replace($replace, "", $row['csz'])." ".str_replace($replace, "", $row['categorykey']));

           for($d=0; $d<$qnum; $d++){
            $relevancy += substr_count($rarr, $qarr[$d]);
           }

         if($relevancy > 0){
          $results["$mid"] = $relevancy;
        }

        if(count($results) > 0)
          arsort($results);

      }//end for $c<=$tot_rows

The results will be sorting in ascending order. Just use a foreach statement to get when you need.

Please be advised that my code is just an example of how to do it, it won't work for perfectly for your needs.

phence

alrightly... Well Thank-you Very much.
I read that tutorial, and it helped.... BUT then, i found this tutorial which is posibly the best tutorial i ever read.

phpFreak Tutorial: MySQL Full-Text Searching with PHP

Now, i am still left a little confused.
Question: if I have FULLTEXT indexed the fields i want to search then do i have to breakup the $searchValue still?

In other words if i follow the tutorial that i posted above will i still need the advice that you gave? to use the 'explode' and 'strtolower' expressions?

phence

Well praise-a-lulia!

I answered my own question in my last post. It works magnificiently!

That tutorial from phpfreak is quite awsome and very informative!
I hope that it helps others who were stuggling with this also.

// This here is the ticket!
$sql = "SELECT lst_id, trading, for_, usr_id, exp,  

               MATCH(trading, details, for_) 
               AGAINST ('$searchValue') AS score FROM listings 
               WHERE MATCH(trading, details, for_) 
               AGAINST ('$searchValue') AND status = ('on') AND exp > curdate()
			   ORDER BY exp ASC, score DESC";

the score attribute is what was hanging me up, i did not know that it was a mysql keyword. Nor, did i realize what it's function was.