Jump to content

Boolean select statements

egnjohn

By egnjohn
in MySQL Help

 Share

Recommended Posts

egnjohn Newbie

egnjohn

Posted
Posted

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>";    
} 

Link to comment
Share on other sites
egnjohn Newbie

egnjohn

Posted
  • Author
Posted

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>";    
} 

Link to comment
Share on other sites
egnjohn Newbie

egnjohn

Posted
  • Author
Posted

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.

Link to comment
Share on other sites
egnjohn Newbie

egnjohn

Posted
  • Author
Posted

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.

Link to comment
Share on other sites
fenway Advanced Member

fenway

Posted
Posted

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.

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.