MATCH() AGAINST() error

[xcandiottix]

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /html/ajax/appsearchcriteria.php on line 60

 

Not sure why I am getting this error. I have a search page that either uses a drop down to search for a string or you can type in a keyword. The drop down method works fine but the keyword section is giving me this error. Any ideas?

 

Any $_GET var is passed from the search page:

 

<?php
$counter = 0;

$con = mysql_connect("");
mysql_select_db("");

if($_GET['chk'] == "x"){
$order ="ORDER BY db_Id DESC"; 
}
else{
$order="";
}

if($_GET['cat'] !== "VOID"){
$cat = $_GET['cat'];
$counter+=.1;
}
if($_GET['lev'] !== "VOID"){
$lev = $_GET['lev'];
$counter+=.01;
}
if($_GET['rat'] !== "VOID"){
$rat = $_GET['rat'];
$counter+=.001;
}
if($_GET['pop'] !== "VOID"){
$pop = $_GET['pop'];
$counter+=.0001;
}

if($counter == .1000){$sql="SELECT * FROM application_directory WHERE db_Appcategory = '$cat' $order";}
if($counter == .0100){$sql="SELECT * FROM application_directory WHERE db_Applevel = '$lev' $order";}
if($counter == .0010){$sql="SELECT * FROM application_directory WHERE db_Apprating = '$rat' $order";}
if($counter == .0001){$sql="SELECT * FROM application_directory WHERE db_Apppopularity = '$pop'";}
if($counter == .1100){$sql="SELECT * FROM application_directory WHERE db_Appcategory = '$cat' AND db_Applevel = '$lev' $order";}
if($counter == .1010){$sql="SELECT * FROM application_directory WHERE db_Appcategory = '$cat' AND db_Apprating = '$rat' $order";}
if($counter == .1001){$sql="SELECT * FROM application_directory WHERE db_Appcategory = '$cat' AND db_Apppopularity = '$pop' $order";}
if($counter == .0110){$sql="SELECT * FROM application_directory WHERE db_Applevel = '$lev' AND db_Apprating = '$rat' $order";}
if($counter == .0011){$sql="SELECT * FROM application_directory WHERE db_Apprating = '$rat' AND db_Apppopularity = '$pop' $order";}
if($counter == .1110){$sql="SELECT * FROM application_directory WHERE db_Appcategory = '$cat' AND db_Applevel = '$lev' AND db_Apprating = '$rat' $order";}
if($counter == .0111){$sql="SELECT * FROM application_directory WHERE db_Applevel = '$lev' AND db_Apprating = '$rat' AND db_Apppopularity = '$pop' $order";}
if($counter == .1111){$sql="SELECT * FROM application_directory WHERE db_Appcategory = '$cat' AND db_Applevel = '$lev' AND db_Apprating = '$rat' AND db_Apppopularity = '$pop' $order";}

<!-- everything above works fine, the problem starts here 

if($counter == 0){
$key = $_GET['key'];
    $sql = "SELECT * FROM application_directory WHERE MATCH(db_Appname, db_Appabout) AGAINST('$key')";
echo "ok".$key;
$counter = 1;
}

<!-- Above produces the error at line 60 below which is "while($row = mysql_fetch_array($result)){"

echo '<table border="0" cellspacing="0" cellpadding="5" class="smallfontforcontents">
<tr bgcolor="#ffffff">
<th>App ID</th>
<th>App Name</th>
<th>App Category</th>
</tr>';

if($counter !== 0){
$result = mysql_query($sql);
while($row = mysql_fetch_array($result)){
echo "<tr "; if($changecolor == 1){echo 'bgcolor="#ffffff"';} echo ">";
echo "<td>" . $row['db_Appid'] . "</td>";
echo "<td>" . $row['db_Appname'] . "</td>";
  	echo "<td>" . $row['db_Appcategory'] . "</td>";
	$value = $row["db_Appid"];
  	echo "<td>" . '<form action=""><input name="'.$value.'" type="button" value="More Info" onClick="showInformation(this.name)"></form>' . "</td>";
  	echo "</tr>";
  	if($changecolor == 0){$changecolor = 1;}else{$changecolor = 0;}
}
}
echo "</table>";
$counter = 0
?>

 

Mysql is set with each column as a Full Text index with Cardinality showing so I believe it should be working on the MySql side.

[Pikachu2000]

What errors are produced?

$result = mysql_query($sql) or die( '<br />Query string: ' . $sql . '<br />Produced error: ' . mysql_error() . '<br />' );

[xcandiottix]

With the code like this it just started working:

if($counter == 0){
$key = $_GET['key'];
    $sql = "SELECT * FROM application_directory WHERE MATCH(db_Appabout) AGAINST('$key')";
echo "ok".$key;
$counter = 1;

 

Seems to be an issue that MATCH(X,Y) is causing a problem because $key is found in either/or and not both.

 

Pik: Query string: SELECT * FROM application_directory WHERE MATCH(db_Appname, db_Appabout) AGAINST('general')

Produced error: Can't find FULLTEXT index matching the column list

 

Seems I need some sort of OR statement?

[Pikachu2000]

I'd have to look it up to be certain, but if I recall correctly,  you'll need to establish a FULLTEXT index that spans both fields . . .

[xcandiottix]

Is there a way I could query

$sql = "SELECT * FROM application_directory WHERE MATCH(db_Appname) AGAINST('$key')";

&

$sql = "SELECT * FROM application_directory WHERE MATCH(db_Appabout) AGAINST('$key')";

 

Then use JOIN to combine the results? I ask because I've never used JOIN before.

[Pikachu2000]

MATCH (col1, col2) AGAINST ($keyword) should work fine as long as the index spans both columns.