Complex qujery - MATCH AGAINST

altlprsn

I am trying to create a search page that processes the php form on the same page, then displays the results (on the same page). The user can then click on one of the result links and another page opens with the specific details (is supposed to) of the recipe...

PHP Code

<?php
error_reporting(E_all);
function search_form () {
echo '<form action="/recipes/recipe_search_main.php3?action=submit" method="post">'.
      // '<input type="text" name="search_value" size="15"><br>'.
       '<P>Food Main Ingredient: <SELECT NAME="fcat1">'.
      '<OPTION VALUE="null" selected> </OPTION>'.
      '<OPTION VALUE="vegetarian">Vegetarian</OPTION>'.
      '<OPTION VALUE="beef">Beef</OPTION>'.
      '<OPTION VALUE="poultry">Poultry</OPTION>'.
      '<OPTION VALUE="pork">Pork</OPTION>'.
      '<OPTION VALUE="seafood">Seafood</OPTION>'.
      '<OPTION VALUE="bacon">Bacon</OPTION>'.
      '<OPTION VALUE="sausage">Sausage</OPTION>'.
      '<OPTION VALUE="ethnic">Ethnic</OPTION></SELECT></P>'.
      '<P>by Meal: <SELECT NAME="wm">'.
      '<OPTION VALUE="null" selected> </OPTION>'.
      '<OPTION VALUE="breakfast">Breakfast</OPTION>'.
      '<OPTION VALUE="lunch">Lunch</OPTION>'.
      '<OPTION VALUE="appetizer">Appetizer</OPTION>'.
      '<OPTION VALUE="dinner">Dinner</OPTION>'.
      '<OPTION VALUE="dessert">Dessert</OPTION></SELECT></P>'.
      '<P>by Dish type: <SELECT NAME="fcat2">'.
      '<OPTION VALUE="null" selected> </OPTION>'.
      '<OPTION VALUE="entree">Entree</OPTION>'.
      '<OPTION VALUE="side">Side</OPTION>'.
      '<OPTION VALUE="salad">Salad</OPTION></SELECT></P>'.
      '<input type="submit" name="text_submit" value="Submit1">'.
       '</form>';
}

function submit_form ($submit) {

mysql_connect("", "name", "pass") or die("Could not connect: ". mysql_error());
echo "connected successfully";
mysql_select_db("recipes") or die("Could not select database"); 

$query = 'SELECT name, description'
        . ' FROM main'
        . ' MATCH ( fc )' // refers to column in db which contains values corresponding to both fcat1 & fcat2
        . ' AGAINST ( \'fcat1, fcat2 \' )' //this ONLY works if there is just one 'fcat1' valu [like poultry- doesn't work w/ fcat1 or wm] - refers to options in search form
        . ' WHERE MATCH ( w_meal )' // this line producing error - refers to column in db
        . ' AGAINST ( \'wm \')'  // this line also producing error - refers to option in search form
        . ' LIMIT 0 , 30';

$result = mysql_query($query);
if ($result) {

echo '<table width=90% align=center border=0><tr> 
         <td align=center bgcolor=#DAD9C5><B>Recipe Name</B></td> 
         <td align=center bgcolor=#DAD9C5><B>Recipe Description</B></td></tr>'; 

    while($current_recipe_found = mysql_fetch_assoc($result)) {
            extract ($current_recipe_found);
echo "<tr><td><b><a href=\"recipes/selected_recipe.php3?recipe=" . $current_recipe_found["name"]. "\">" . $current_recipe_found["name"] . "</a></b><BR /></td><td>" .  $current_recipe_found["description"];
 }
echo "</table>";
  } else { 
     echo "<br><b>Problems:</b> ". mysql_error(); 
 } 
}
switch ($_GET['action']) {
    default:
        search_form();
    break;

case 'submit';
    submit_form();
break;
}
?>

the page where this is located:
http://www.thepastaboard.com/recipes/recipe_search_main.php3

Can someone help with complex MATCH AGAINST, WHERE MATCH AGAINST sql syntax? Or~ is there a better way to construct the query??

rachel2004

Originally posted by altlprsn

$query = 'SELECT name, description'
        . ' FROM main'
        . ' MATCH ( fc )' // refers to column in db which contains values corresponding to both fcat1 & fcat2
        . ' AGAINST ( \'fcat1, fcat2 \' )' //this ONLY works if there is just one 'fcat1' valu [like poultry- doesn't work w/ fcat1 or wm] - refers to options in search form
        . ' WHERE MATCH ( w_meal )' // this line producing error - refers to column in db
        . ' AGAINST ( \'wm \')'  // this line also producing error - refers to option in search form
        . ' LIMIT 0 , 30';

From what I know about MATCH and AGAINST, you have to use it in a WHERE clause. It looks like you are using it once BEFORE the WHERE clause.

Fix that part and post again on your results.

altlprsn

I switched the query around as you suggested, but now I get a:

Problems: You have an error in your SQL syntax near 'MATCH ( fc ) AGAINST ('fcat2') LIMIT 0 , 30'

<?
        $query = 'SELECT name, description'
        . ' FROM main'
        . ' WHERE MATCH ( fc )'
        . ' AGAINST (\'fcat1\')'
        . ' MATCH ( fc )'
        . ' AGAINST (\'fcat2\')'
        . ' LIMIT 0 , 30';
?>

I guess my confusion is whether or not I can MATCH AGAINST multiple criteria? (I also need to match those results against the w_meal column in the db) Also, should I be using $fcat1? Is the error because I didn't define the variable?

Is this the best way to set up the query?? Or should I use a different approach altogether?

I'm out of ideas at this point...

rachel2004

If you can MATCH/AGAINST against multiple criteria, I would think you need to use multiple arguments in the functions:

Maybe try:

$query = "SELECT name, description
                 FROM main
                 WHERE MATCH ( column1, column2, column3 )
                 AGAINST ('fcat1', 'fcat2', 'fcat3')
                 LIMIT 0 , 30";

Also, what is the reason for using so many single quotes and dots in your query statement? Use doulbe quotes to begin and end your statement and use single quotes to quote. There are various of actually doing that, but just do that for now.
Post back your results.

altlprsn

The query was so concantenated as suggested by another guru on another php site... it was worth a try! 🙂

Ok, I tried your suggestion and I'm no longer getting any kind of error but also no results...

<?
$query = "SELECT name, description
	FROM main
	WHERE MATCH ( fc, w_meal )
	AGAINST ('fcat1', 'fcat2', 'wm')
	LIMIT 0 , 30";
	?>

Note that both fcat1 & fcat2 both reference the same column (fc) in the db... is that a problem?

rachel2004

Hmm, you may have the idea of MATCH and AGAINST backwards. MATCH are your indexed columns. And AGAINST are the words you want to look for.

Can you try your query with just one column and one keyword? If we get that working, then we can try multiple columns, keywords. Let's just go over this one step at a time.

Also, just so I know I am correctly referring to column names correctly, which ones are your columns that we are interested in searching in? Is it fc?

Right now, your query is basicaly saying:

Select the fields name and description from the table main where the indexed columns fc and w_meal contain the words 'fcat1', fcat2', and 'wm'. Limit search to the first 30 records.

altlprsn

Ok, I see where the problem lies. I've got the MATCH & AGAINST right in my mind, but what I want to do is not search for the words 'fcat1, fcat2, wm' within the columns 'fc' and 'w_meal' but the individual values of the drop-down lists.

In my drop-down lists I have values that are stored in those columns. I was trying to make the value of each of the separate drop-down lists as this:

main ingredient
<SELECT NAME="fcat1"> (refers to column 'fc'):
{blank}
vegetarian <-- these are the actual words in the column fc
beef
poultry
etc.

by meal
<SELECT NAME="wm"> (refers to column 'w_meal'):
{blank}
breakfast <-- these are the actual words in the column w_meal
lunch
etc

dish type
<SELECT NAME="fcat2"> (also refers to column 'fc'):
{blank}
entree <-- these are the actual words in the column fc
side

salad

so that I could use whichever words were chosen to search and match that against the 2 columns ...

There are no actual instances of the word 'fcat1' 'fcat2' owr 'wm' in the fc or w_meal columns...that's why I wondered if I should use $fcat1... but I have tried that & even used
AGAINST (\'$fcat1\') and also (\'$\fcat1\') with no luck. I even tried with no single quotes... Also I haven't specifically defined the variables $fcat1, $fcat2 or $wm...