[SOLVED] How do I give user a choice of fields to search with Full Text/Boolean?

[HowdeeDoodee]

I misposted this question over in MySql. It is, as Illusion pointed out, a php question.

 

I want to give the user the option of searching a combination of the fields $Topic, $Subtopic, $Theswords in Boolean/Full Text. The problem is if I make any of the fields empty prior to the query, I get a MySql syntax error message. The user has the option of selecting fields via checkboxes, but when the field is not selected the user will get the error message because the variable is empty in the query statemtnt. Are there any code suggestions for avoiding the error message when a field is not selected by the user to be searched? Thank you in advance for any replies.

 

 

$query = "SELECT * FROM `View2_CondFT` WHERE MATCH($TopicFieldSelect, $SubtopicFieldSelect, $TheswordsFieldSelect) AGAINST ('$terms[0]' IN BOOLEAN MODE) 
AND MATCH($Topic, $Subtopic, $Theswords) AGAINST ('$terms[1]' IN BOOLEAN MODE) 
AND MATCH($Topic, $Subtopic, $Theswords) AGAINST ('$terms[2]' IN BOOLEAN MODE) 
AND MATCH($Topic, $Subtopic, $Theswords) AGAINST ('$terms[3]' IN BOOLEAN MODE) 
AND MATCH($Topic, $Subtopic, $Theswords) AGAINST ('$terms[4]' IN BOOLEAN MODE) 
AND MATCH($Topic, $Subtopic, $Theswords) AGAINST ('$terms[5]' IN BOOLEAN MODE) 
ORDER BY `Lnum` ASC ;

[MemphiS]

if (!empty($variable)){

 

..// do code

 

}

[HowdeeDoodee]

Thank you for the response but testing a variable is not the issue. The issue is how do I handle or do I handle field variables in the query that equal nothing. When a field variable in the query equals nothing, a MySql error message is generated.

 

$Topic, $Subtopic, $Theswords are field variables containing field names. When one of these variables is empty, an error message appears from MySql because of the , , appearing where the variablename appeared.

[freakus_maximus]

I think what Memphis was trying to say is that your variables as they come in are just that. You need to check to see if they have a value or not. Then, by knowing what has a value and what does not, you need to build MATCH dynamically.

 

This would be for the first MATCH in your query, should be able to apply/modify for the rest of the matches as well:

$match1 = "";

if (!empty($TopicFieldSelect)){
$match1 = ($TopicFieldSelect . ", ");
}

if (!empty($SubtopicFieldSelect)){
$match1 .= ($SubtopicFieldSelect . ", ");

}
if (!empty($TheswordsFieldSelect)){
$match1 .= ($TheswordsFieldSelect);
}

$query = "SELECT * FROM `View2_CondFT` WHERE MATCH($match1) AGAINST ('$terms[0]' IN BOOLEAN MODE)

[HowdeeDoodee]

Thank you freakus, for the reply.

 

Well, I assembled the code below and I am getting a MySql syntax error message. Do you see anything wrong? Here is the code.

 

$match1 = "";
if (!empty($Topic)){
$match1 = ($Topic . ", ");
}
if (!empty($Subtopic)){
$match1 .= ($Subtopic . ", ");
}
if (!empty($Theswords)){
$match1 .= ($Theswords);
}
$match2 = "";

if (!empty($Topic)){
$match1 = ($Topic . ", ");
}

if (!empty($Subtopic)){
$match1 .= ($Subtopic . ", ");
}
if (!empty($Theswords)){
$match1 .= ($Theswords);
}
$match3 = "";
if (!empty($Topic)){
$match3 = ($Topic . ", ");
}
if (!empty($Subtopic)){
$match3 .= ($Subtopic . ", ");
}
if (!empty($Theswords)){
$match3 .= ($Theswords);
}
$match4 = "";
if (!empty($Topic)){
$match4 = ($Topic . ", ");
}
if (!empty($Subtopic)){
$match4 .= ($Subtopic . ", ");
}
if (!empty($Theswords)){
$match4 .= ($Theswords);
}
$match5 = "";
if (!empty($Topic)){
$match5 = ($Topic . ", ");
}
if (!empty($Subtopic)){
$match5 .= ($Subtopic . ", ");
}
if (!empty($Theswords)){
$match5 .= ($Theswords);
}
$match6 = "";
if (!empty($Topic)){
$match6 = ($Topic . ", ");
}
if (!empty($Subtopic)){
$match6 .= ($Subtopic . ", ");
}
if (!empty($Theswords)){
$match6 .= ($Theswords);
}

$query2 = "SELECT * FROM `View2_ConcordFT` WHERE MATCH($match1) AGAINST ('$terms[0]' IN BOOLEAN MODE) AND MATCH($match2) AGAINST ('$terms[1]' IN BOOLEAN MODE) AND MATCH($match3) AGAINST ('$terms[2]' IN BOOLEAN MODE) AND MATCH($match4) AGAINST ('$terms[3]' IN BOOLEAN MODE) AND MATCH($match5) AGAINST ('$terms[4]' IN BOOLEAN MODE) AND MATCH($match6) AGAINST ('$terms[5]' IN BOOLEAN MODE) ORDER BY `Lnum` ASC LIMIT $startrecord, $display";

 

Here is the error message.

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AGAINST ('peace' IN BOOLEAN MODE) OR MATCH(Topic, Subtopic, Theswords) AGAINST ' at line 1

 

Thank you again for the response.

[freakus_maximus]

Check your $match2, you have it overwritting your $match1 variable

 

$match2 = "";

if (!empty($Topic)){
$match1 = ($Topic . ", ");
}

if (!empty($Subtopic)){
$match1 .= ($Subtopic . ", ");
}
if (!empty($Theswords)){
$match1 .= ($Theswords);
}

 

[HowdeeDoodee]

Thank you freakus, I am getting freaked.

 

I got the code to work, kind of. The query using the OR operator seems to be working appropriately. The query using the AND operator isn't working at all in my script. I can copy and paste the AND and OR queries into phpMyAdmin by using echo $query and copy and pasting. When I copy and paste the AND query into phpMyAdmin the query string works perfectly. Does anyone have any suggestions as to why the OR query is working and the AND query is not working in my script? Thank you in advance for your replies.

 

$query = "SELECT * FROM `View2_ConcordFT` WHERE MATCH($match1) AGAINST ('$terms[0]' IN BOOLEAN MODE) OR MATCH($match2) AGAINST ('$terms[1]' IN BOOLEAN MODE) OR MATCH($match3) AGAINST ('$terms[2]' IN BOOLEAN MODE) OR MATCH($match4) AGAINST ('$terms[3]' IN BOOLEAN MODE) OR MATCH($match5) AGAINST ('$terms[4]' IN BOOLEAN MODE) OR MATCH($match6) AGAINST ('$terms[5]' IN BOOLEAN MODE) ORDER BY `Lnum` ASC";


$query = "SELECT * FROM `View2_ConcordFT` WHERE MATCH($match1) AGAINST ('$terms[0]' IN BOOLEAN MODE) AND MATCH($match2) AGAINST ('$terms[1]' IN BOOLEAN MODE) AND MATCH($match3) AGAINST ('$terms[2]' IN BOOLEAN MODE) AND MATCH($match4) AGAINST ('$terms[3]' IN BOOLEAN MODE) AND MATCH($match5) AGAINST ('$terms[4]' IN BOOLEAN MODE) AND MATCH($match6) AGAINST ('$terms[5]' IN BOOLEAN MODE) ORDER BY `Lnum` ASC";

[HowdeeDoodee]

For a final resolution on this thread, see the following thread here at Freaks.

 

http://www.phpfreaks.com/forums/index.php/topic,148490.0.html