Boolean select statements

[egnjohn]

I'm building an online Bible with a search. I do not know what I have done wrong with my coding. The search for all keywords doesnt work. I get the same results as the any keyword search.

 

I am fairly new to coding, so it is probably a very simple fix. Please let me know what I did wrong. Below is the form and the mysql select statements.

 

echo "<form id=\"form4\" name=\"form4\" method=\"get\" action=\"modules.php?\">";
echo "<input value=\"Bible\" name=\"name\" type=\"hidden\" />";
echo "<input value=\"search_results\" name=\"call\" type=\"hidden\" />";
echo "<input size=\"50\" name=\"query\" title=\"Search Keywords\"/>";
echo "<input value=\"$version\" type=\"hidden\" name=\"version\" />";
echo "<br />";
echo "<input value=\"entire_bible\" checked=\"checked\" type=\"radio\" name=\"criteria\" title=\"Search Entire Bible\"/>";
echo " Entire Bible";
echo "  <input value=\"old_testament\" type=\"radio\" name=\"criteria\" title=\"Search Old Testament\"/>";
echo " Old Testament";
echo "  <input value=\"new_testament\" type=\"radio\" name=\"criteria\" title=\"Search New Testament\"/>";
echo " New Testament";
echo "<br /><br />";
echo "<input value=\"any\" checked=\"checked\" type=\"radio\" name=\"search_type\" title=\"Match Any Word\"/>";
echo " Match Any Word";
echo "  <input value=\"all\" type=\"radio\" name=\"search_type\" title=\"Match All Words\"/>";
echo " Match All Words";
echo "<br />";
echo "<input value=\"exact\" type=\"radio\" name=\"search_type\" title=\"Match Exact Phrase\"/>";
echo " Match Exact Phrase";
echo "  <input value=\"verse\" type=\"radio\" name=\"search_type\" title=\"Match Book Chapter:Verse\"/>";
echo " Match Book Chapter:Verse";
echo "<br><br>";
echo "<input value=\"Submit\" type=\"submit\" />";
echo "     ";
echo "<input value=\"Reset\" type=\"reset\" />";
echo "</form>"; 

global $db;
if($search_type=='any'){
if($criteria=='entire_bible'){
$result4 = $db->sql_query("select * FROM Bible$version WHERE MATCH (scripture) AGAINST ('*$query*' IN BOOLEAN MODE)");
}
if($criteria=='old_testament'){
$result4 = $db->sql_query("select * FROM Bible$version WHERE MATCH (scripture) AGAINST ('*$query*' IN BOOLEAN MODE) && testament='old'");
}
if($criteria=='new_testament'){
$result4 = $db->sql_query("select * FROM Bible$version WHERE MATCH (scripture) AGAINST ('*$query*' IN BOOLEAN MODE) && testament='new'");
}
}//if any

if($search_type=='all'){
$query = ereg_replace(" ", " +", "$query");
if($criteria=='entire_bible'){
$result4 = $db->sql_query("select * FROM Bible$version WHERE MATCH (scripture) AGAINST ('{$query}' IN BOOLEAN MODE)");
}
if($criteria=='old_testament'){
$result4 = $db->sql_query("select * FROM Bible$version WHERE MATCH (scripture) AGAINST ('{$query}' IN BOOLEAN MODE) && testament='old'");
}
if($criteria=='new_testament'){
$result4 = $db->sql_query("select * FROM Bible$version WHERE MATCH (scripture) AGAINST ('{$query}' IN BOOLEAN MODE) && testament='new'");
}
}//if all

if($search_type=='exact'){
if($criteria=='entire_bible'){
$result4 = $db->sql_query("select * FROM Bible$version WHERE MATCH (scripture) AGAINST ('\"{$query}\"' IN BOOLEAN MODE)");
}
if($criteria=='old_testament'){
$result4 = $db->sql_query("select * FROM Bible$version WHERE MATCH (scripture) AGAINST ('\"{$query}\"' IN BOOLEAN MODE) && testament='old'");
}
if($criteria=='new_testament'){
$result4 = $db->sql_query("select * FROM Bible$version WHERE MATCH (scripture) AGAINST ('\"{$query}\"' IN BOOLEAN MODE) && testament='new'");
}
}//if exact

if($search_type=='verse'){    
$i = strrpos($query, " ");
$query=substr_replace($query, ":", $i, 1);
$query2 = explode(":", $query);
$bookquery = $query2[0];
$chapterquery = $query2[1];
$versequery = $query2[2];
$result4 = $db->sql_query("select * FROM Bible$version WHERE book = '$bookquery' AND chapter= '$chapterquery' AND verse ='$versequery'");
}//if verse

while ($row = $db->sql_fetchrow($result4)) {
$testament = $row['testament'];
$book = $row['book'];
$chapter = $row['chapter'];
$verse = $row['verse'];
$scripture = $row['scripture'];    

echo "<b><a href=\"modules.php?name=Bible&call=chapter&viewbook=$book&viewchapter=$chapter&version=$version\">$book</a> $chapter:$verse</b>";
echo "<br>";
echo "$scripture";
echo "<hr>";
}//result 4

if(!$query){
echo "<b>You did NOT enter any keywords.</b><br>";    
}
if(!$scripture){
echo "<b>No results found.</b>";    
}  

[fenway]

I don't see SQL statements -- I see php code.

[egnjohn]

mysql statements: they are lines that include "sql_query("select * FROM......"

 

iglobal $db;
if($search_type=='any'){
if($criteria=='entire_bible'){
$result4 = $db->sql_query("select * FROM Bible$version WHERE MATCH (scripture) AGAINST ('*$query*' IN BOOLEAN MODE)");
}
if($criteria=='old_testament'){
$result4 = $db->sql_query("select * FROM Bible$version WHERE MATCH (scripture) AGAINST ('*$query*' IN BOOLEAN MODE) && testament='old'");
}
if($criteria=='new_testament'){
$result4 = $db->sql_query("select * FROM Bible$version WHERE MATCH (scripture) AGAINST ('*$query*' IN BOOLEAN MODE) && testament='new'");
}
}//if any

if($search_type=='all'){
$query = ereg_replace(" ", " +", "$query");
if($criteria=='entire_bible'){
$result4 = $db->sql_query("select * FROM Bible$version WHERE MATCH (scripture) AGAINST ('{$query}' IN BOOLEAN MODE)");
}
if($criteria=='old_testament'){
$result4 = $db->sql_query("select * FROM Bible$version WHERE MATCH (scripture) AGAINST ('{$query}' IN BOOLEAN MODE) && testament='old'");
}
if($criteria=='new_testament'){
$result4 = $db->sql_query("select * FROM Bible$version WHERE MATCH (scripture) AGAINST ('{$query}' IN BOOLEAN MODE) && testament='new'");
}
}//if all

if($search_type=='exact'){
if($criteria=='entire_bible'){
$result4 = $db->sql_query("select * FROM Bible$version WHERE MATCH (scripture) AGAINST ('\"{$query}\"' IN BOOLEAN MODE)");
}
if($criteria=='old_testament'){
$result4 = $db->sql_query("select * FROM Bible$version WHERE MATCH (scripture) AGAINST ('\"{$query}\"' IN BOOLEAN MODE) && testament='old'");
}
if($criteria=='new_testament'){
$result4 = $db->sql_query("select * FROM Bible$version WHERE MATCH (scripture) AGAINST ('\"{$query}\"' IN BOOLEAN MODE) && testament='new'");
}
}//if exact

if($search_type=='verse'){    
$i = strrpos($query, " ");
$query=substr_replace($query, ":", $i, 1);
$query2 = explode(":", $query);
$bookquery = $query2[0];
$chapterquery = $query2[1];
$versequery = $query2[2];
$result4 = $db->sql_query("select * FROM Bible$version WHERE book = '$bookquery' AND chapter= '$chapterquery' AND verse ='$versequery'");
}//if verse

while ($row = $db->sql_fetchrow($result4)) {
$testament = $row['testament'];
$book = $row['book'];
$chapter = $row['chapter'];
$verse = $row['verse'];
$scripture = $row['scripture'];    

echo "<b><a href=\"modules.php?name=Bible&call=chapter&viewbook=$book&viewchapter=$chapter&version=$version\">$book</a> $chapter:$verse</b>";
echo "<br>";
echo "$scripture";
echo "<hr>";
}//result 4

if(!$query){
echo "<b>You did NOT enter any keywords.</b><br>";    
}
if(!$scripture){
echo "<b>No results found.</b>";    
}  

[fenway]

That's still php code.

 

A statement is a raw string -- you'll need to show us those.

[egnjohn]

I figured out what was wrong i just needed to add a + to this line in front of ('{+$query}'

 

$result4 = $db->sql_query("select * FROM Bible$version WHERE MATCH (scripture) AGAINST ('{$query}' IN BOOLEAN MODE)");

 

Sorry for confusion, but I thought if I posted this in PHP section it would be wrong place since I needed help with a mysql select.

[fenway]

The only problem was that, still, you have NOT posted a single mysql statement.

[egnjohn]

Fenway, I tried to take the high road with you, I even appoligized. You don't need to keep giving me a hard time.

 

Your right I did post php code, but I also posted mysql query code. Here is a copy/paste straight from here http://dev.mysql.com/doc/refman/5.6/en/fulltext-boolean.html 

 

mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE);  ............

 

Keep in mind that although MATCH() AGAINST() is case-insensitive, it also is basically **accent-insensitive**. In other words, if you do not want _mangé_ to match with _mange_ (this example is in French), you have no choice but to use the BOOLEAN MODE with the double quote operator. This is the only way that MATCH() AGAINST() will make accent-sensitive matches.

 

E.g.:

 

SELECT * FROM quotes_table WHERE MATCH (quote) AGAINST ('"mangé"' IN BOOLEAN MODE)

 

For multiword searches:

 

SELECT * FROM quotes_table MATCH (quote) AGAINST ('"mangé" "pensé"' IN BOOLEAN MODE)

 

SELECT * FROM quotes_table MATCH (quote) AGAINST ('+"mangé" +"pensé"' IN BOOLEAN MODE)

 

This sure look pretty damn close to mine

 

$result4 = $db->sql_query("select * FROM Bible$version WHERE MATCH (scripture) AGAINST ('{$query}' IN BOOLEAN MODE)");

 

The only difference is I am assigning a php variable $results4 to the query.  The $db is a global variable that is the connection to the database.

 

So in conclusion You are correct in that there was php in my post, but there also was mysql queries in my post also. I included the php so the sql code would make sense to what I was asking.

 

You did not need to give me a hard time at all about my post. If you believed this thread should have been in the php section you should have moved it to that section.

[fenway]

I don't believe it should be in the php section.

 

I believe that you should post SQL statements, not PHP that produces mysql statements, with variables that need to be interpolated.

 

For all we know, your $query could have started with a dollar sign.