Single Search box to search all fields

Hi.
I would like to setup on the main dadabik webpage a search form with a single text box that would search through all fields in the database and return any record that CONTAINS the searched text in any of the fields.

I want the search form to show up right above the main dadabik table so users don't have to click on the Search link.

Can someone please point me to what kind of code do I need to add to the header.php file for such a form?

Thanks
Eli

I made a search box that will search all fields of a certain type that I specify (remove the where part of the sql line if you want to include ALL your fields). This information is retrieved from the dadabik_table of the current table being viewed:

<?
// query to get the fields names to include in the search - specify which types in the where clause
$sqlcols = "SELECT name_field FROM ".$prefix_internal_table.$table_name." WHERE type_field IN ('text','textarea','rich_editor')";
$searchall = execute_db($sqlcols, $conn);

// as long as column names are found meeting criteria above, construct form fields
while ($searchfield = fetch_row_db($searchall)){

// This line not required -- used to get list of column names into array that can be printed to screen to verify columns it has found
$allfields .= $searchfield[0].' ';

// sets the search select type to 'contains' for each column included in search request
$allseltypes .= '<input name="'.$searchfield[0].'_select_type" type="hidden" value="contains">';

// creates a hidden form field for each column included in search request
$fieldterm .= '<input name="'.$searchfield[0].'" type="hidden" value="">';

// writes onSubmit values to form open tag to set the value for each hidden field defined above
$submitline .= 'document.searchall.'.$searchfield[0].'.value = document.searchall.allterm.value;';
} // end while column names are found

// uncomment to display database table columns found which will be included in search form
/*echo '<pre>';
print_r($allfields);
echo '</pre>';*/
?>

<form name="searchall" class="noformpad" style="padding-bottom:5px;" method="POST" action="<?= $dadabik_main_file; ?>?table_name=<?= urlencode($table_name); ?>&page=0&function=search&execute_search=1" enctype="multipart/form-data" onSubmit="javascript:<?= $submitline; ?>return true;">
Quick Search:&nbsp;
<input type="hidden" name="operator" value="or">
<?= $allseltypes; ?>
<input name="allterm" type="text" size="10" maxlength="50">
<?= $fieldterm; ?>
&nbsp;<input type="submit" value="<?= $submit_buttons_ar["search_button"]; ?>" class="submit">
</form> (sorry forgot the closing form tag in original post -- D'oh!)

I did not include other field types (select_single, date types, etc.) because most (if not all) my select_single fields are driven from other database tables.

Hope this helps!



_______________________________
..:: Debbie |\.-./| DaDaBIK Support ::..

Debbie.
I pasted this code on the bottom of header.php file, but now when I load the page I get:

[08] Error: during query execution.

Did I do something wrong or is there a mistake in the code? Do I need to rename something?

I only renamed the field names in the WHERE clause to the names that I have in my database, like this:

$sqlcols = "SELECT name_field FROM ".$prefix_internal_table.$table_name." WHERE type_field IN ('author_english','author_hebrew','title_english,'title_hebrew')";

Thanks
Eli

It seems to crash on this line:

$searchall = execute_db($sqlcols, $conn);

the error is from execute_db function.

But if I paste your code the way it was without any modifications or if I remove the WHERE clause then the page loads fine, however if I search for something then it crashes again and gives the same error.

You can see the page here.

http://www.seforimonline.org/seforimdb/

Thanks for your help.
Eli



Post Edited (06-27-09 21:19)

per the comment just above the sqlcols select line, it says types -- this is looking for field TYPES (eg: text, textarea, etc.). If you want to specify actual column names your sql line needs to read:

$sqlcols = "SELECT name_field FROM ".$prefix_internal_table.$table_name." WHERE name_field IN ('author_english','author_hebrew','title_english,'title_hebrew')";

Have another read through the comments/text I provided in my post above - I was referencing field types and not field names in the where. Using field types instead of actual column names makes it more portable to other installations of DaDaBIK (I've several).



_______________________________
..:: Debbie |\.-./| DaDaBIK Support ::..

I just pasted this new line and it still crashes. But either way, even if I remove the WHERE clause then the page displays fine, but when I click on Search (which by the way does not display on the button for some reason) it gives the same error instead of displaying the results. So something else is wrong here.

My knowledge of PHP is very limited so please give me clearer instructions.

I appreciate all of your help.

Thanks
Eli

Have you tried it using it EXACTLY as I originally posted it using the field type as the generator to gather the field names?

I tried changing mine to gather specific fields by name as you did and it worked fine. I don't believe the problem is with the sql statement.

Have you ensured that your opening and closing PHP tags <? and ?> are in the correct places? All PHP MUST be surrounded by <? and ?>. Another thing to try is changing the <?= $ spots to read <? echo $. Some servers are not set up to allow short echo statements.

To help in working with DaDaBIK, I'd suggest you have a look at a few tutorials on the internet about PHP (http://www.w3schools.com/ is one - a search for php tutorial will yeild lots of results) and also have a look at the PHP website itself (http://www.php.net/).



_______________________________
..:: Debbie |\.-./| DaDaBIK Support ::..

Debbie helped me to get the code to work correctly with Dadabik 4.2. The correct code that needs to be pasted into the bottom of header.php file is posted below. However there is still one last problem. For some reason if I search in any other language besides English then the search crashes with an error. It seems that the foreign language (in my case Hebrew, but I tried it with other characters as well) characters do not get encoded correctly. However the main search in Dadabik works fine with other languages on the same database. If anyone can help with this I would appreciate it.


<?php
if ($_GET["function"] != "show_search_form" { //this if statement prevents the Quick Search form to show up on the Advanced Search page
?>

<?php

// query to get the fields names to include in the search - specify which types in the where clause
// remove the where part of the sql line if you want to include ALL your fields
$sqlcols = "SELECT name_field FROM ".$prefix_internal_table.$table_name." WHERE type_field IN ('text','textarea','rich_editor')";
//This line searches only specific fields in the database that are specifide by the field name. To use it Uncomment it and comment the line above. Change the fields names to the names in your database.
//$sqlcols = "SELECT name_field FROM ".$prefix_internal_table.$table_name." WHERE name_field IN ('author_english','author_hebrew','title_english,'title_hebrew')";
//This line searches all fields.
//$sqlcols = "SELECT name_field FROM ".$prefix_internal_table.$table_name."";

$searchall = execute_db($sqlcols, $conn);

// as long as column names are found meeting criteria above, construct form fields
while ($searchfield = fetch_row_db($searchall)){

// This line not required -- used to get list of column names into array that can be printed to screen to verify columns it has found
$allfields .= $searchfield[0].' ';

// sets the search select type to 'contains' for each column included in search request
$allseltypes .= '<input name="'.$searchfield[0].$select_type_select_suffix.'" type="hidden" value="contains">';

// creates a hidden form field for each column included in search request
$fieldterm .= '<input name="'.$searchfield[0].'" type="hidden" value="">';

// writes onSubmit values to form open tag to set the value for each hidden field defined above
$submitline .= 'document.searchall.'.$searchfield[0].'.value = document.searchall.allterm.value;';
} // end while column names are found

// uncomment to display database table columns found which will be included in search form

echo '<pre>';
print_r($allfields);
echo '</pre>';

?>

<form name="searchall" class="noformpad" style="padding-bottom:5px;" method="POST" action="<?php echo $dadabik_main_file; ?>?table_name=<?php echo urlencode($table_name); ?>&page=0&function=search&execute_search=1" enctype="multipart/form-data" accept-charset="Windows-1255" onSubmit="javascript:<?php echo $submitline; ?>return true;">
Quick Search:
<input type="hidden" name="operator" value="or">
<?php echo $allseltypes; ?>
<input name="allterm" type="text" size="10" maxlength="100">
<?php echo $fieldterm; ?>
<input type="submit" value="<?php echo $submit_buttons_ar["search_short"]; ?>" class="submit">
</form>

<?php
} //closing bracket for the if ($_GET["function"] != "show_search_form"
?>

I figured out my problem with the Hebrew search. It had nothing to do with the code above. The code above is correct. It had to do with the way the Collation was set on the database fields. I have different fields in my database in terms of Collation. Some only had English in them and some only had Hebrew in them. So I set the Collation on the English fields to ascii_general_ci and the Collation on the Hebrew fields to hebrew_general_ci using PHPMyAdmin. That was a mistake. Apparently when Dadabik searches for a non ASCII character inside an ASCII field it crashes. That's a bug that should be fixed int he next version. However there is a simple work around. I set the Collation on ALL of my fields to hebrew_general_ci regardless if they have any Hebrew or not in them. Now Dadabik searches fine all fields and does not crash. I even tried searching the Hebrew fields with Russian characters and that works too. So that implies that the Collation setting to a Non-ASCII charcater set works with all charcter sets and not just the specific one it is set to. My advice based on this is that even if you don't use any other languages in your database besides English you should still NOT set the Collation to ASCII, because if a user will type in a Non-Ascii character Dadabik will crash. But setting it to another character set still works with English just fine and works with other languages as well.

Hope this help.
Eli

For some reason this only works in Chrome. I have tested it in Firefox and IE as well to no luck. The result is as if nothing was searched for at all.

<?php
if ($_GET["function"] != "show_search_form"{ //this if statement prevents the Quick Search form to show up on the Advanced Search page
?>

<?php

// query to get the fields names to include in the search - specify which types in the where clause
// remove the where part of the sql line if you want to include ALL your fields
//$sqlcols = "SELECT name_field FROM ".$prefix_internal_table.$table_name." WHERE type_field IN ('text','textarea','rich_editor')";
//This line searches only specific fields in the database that are specifide by the field name. To use it Uncomment it and comment the line above. Change the fields names to the names in your database.
//$sqlcols = "SELECT name_field FROM ".$prefix_internal_table.$table_name." WHERE name_field IN ('author_english','author_hebrew','title_english,'title_hebrew')";
//This line searches all fields.
$sqlcols = "SELECT name_field FROM ".$prefix_internal_table.$table_name."";

$searchall = execute_db($sqlcols, $conn);

// as long as column names are found meeting criteria above, construct form fields
while ($searchfield = fetch_row_db($searchall)){

// This line not required -- used to get list of column names into array that can be printed to screen to verify columns it has found
$allfields .= $searchfield[0].' ';

// sets the search select type to 'contains' for each column included in search request
$allseltypes .= '<input name="'.$searchfield[0].$select_type_select_suffix.'" type="hidden" value="contains">';

// creates a hidden form field for each column included in search request
$fieldterm .= '<input name="'.$searchfield[0].'" type="hidden" value="">';

// writes onSubmit values to form open tag to set the value for each hidden field defined above
$submitline .= 'document.searchall.'.$searchfield[0].'.value = document.searchall.allterm.value;';
} // end while column names are found

// uncomment to display database table columns found which will be included in search form

echo '<pre>';
print_r($allfields);
echo '</pre>';

?>

<form name="searchall" class="noformpad" style="padding-bottom:5px;" method="POST" action="<?php echo $dadabik_main_file; ?>?table_name=<?php echo urlencode($table_name); ?>&page=0&function=search&execute_search=1" enctype="multipart/form-data" accept-charset="Windows-1255" onSubmit="javascript:<?php echo $submitline; ?>return true;">
Quick Search: 
<input type="hidden" name="operator" value="or">
<?php echo $allseltypes; ?>
<input name="allterm" type="text" size="10" maxlength="100">
<?php echo $fieldterm; ?>
<input type="submit" value="<?php echo $submit_buttons_ar["search_short"]; ?>" class="submit">
</form>

<?php
} //closing bracket for the if ($_GET["function"] != "show_search_form"
?>

Any ideas?

Dunno ... I've been using that in FF and IE and have had no problems.
Have you implemented any other mods to your installation?

_______________________________
..:: Debbie |\.-./| DaDaBIK Support ::..

Hi DebbieS

I used the original code from your first post. The form appears but searches do not give back any results but just show all data sets.

Trying to remove the "WHERE" statement or adding other field types like 'select_single' results in the same error message mentioned above:

[08] Error: during query execution

Where is my mistake?

Thanks for your advise,

JM

When using debug the query results in the following error:

SELECT `categories`.`CategoryID`, `categories`.`CategoryName` FROM `categories` WHERE or
DBMS server said: 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 'or' at line 1

Does anything from the newer thread on the same topic help?

_______________________________
..:: Debbie |\.-./| DaDaBIK Support ::..

Hi Debbie

Yes, I did that already but it did not help at all. When using the regular search form (using OR) the search runs fine and shows correct results.


That is the code I used:

---

<?
// query to get the fields names to include in the search - specify which types in the where clause
$sqlcols = "SELECT name_field FROM ".$prefix_internal_table.$table_name." WHERE type_field IN ('text','textarea','rich_editor')";
$searchall = execute_db($sqlcols, $conn);

// as long as column names are found meeting criteria above, construct form fields
while ($searchfield = fetch_row_db($searchall)){

// This line not required -- used to get list of column names into array that can be printed to screen to verify columns it has found
$allfields .= $searchfield[0].' ';

// sets the search select type to 'contains' for each column included in search request
$allseltypes .= '<input name="'.$searchfield[0].'_select_type" type="hidden" value="contains">';

// creates a hidden form field for each column included in search request
$fieldterm .= '<input name="'.$searchfield[0].'" type="hidden" value="">';

// writes onSubmit values to form open tag to set the value for each hidden field defined above
$submitline .= 'document.searchall.'.$searchfield[0].'.value = document.searchall.allterm.value;';
} // end while column names are found

// uncomment to display database table columns found which will be included in search form
echo '<pre>';
print_r($allfields);
echo '</pre>';
?>

<form name="searchall" class="noformpad" style="padding-bottom:5px;" method="POST" action="<?= $dadabik_main_file; ?>?table_name=<?= urlencode($table_name); ?>&page=0&function=search&execute_search=1" enctype="multipart/form-data" onSubmit="javascript:<?= $submitline; ?>return true;">
Quick Search:&nbsp;
<input type="hidden" name="operator" value="or">
<?= $allseltypes; ?>
<input name="allterm" type="text" size="10" maxlength="50">
<?= $fieldterm; ?>
&nbsp;<input type="submit" value="<?= $submit_buttons_ar["search_button"]; ?>" class="submit">
</form>

---

I noticed that at meanster99 the form works perfectly. It would be great to get in running on my sites as well ;-).

If required I would grant admin access to you in order to check the config.

Actually meanster99 never did reply to my last post there, so we can only assume it is working.

I will ask you to provide the same info as I asked him:
Would you send me (or post here):

1. The exact code you are using to display the quick search in your install (the PHP)
2. The resulting HTML from your web page for the entire quick search form.
3. A list of the fields in the database which you believe should be included in the search.

I will have a look at the code and resulting HTML and see if there is something that stands out.

_______________________________
..:: Debbie |\.-./| DaDaBIK Support ::..

Hi DebbieS

Thanks a lot. I have sent you a PM.

Regards

Juergen

Juergen

I may have found the issue. In the one line of code below, you have left in the hard-coded _select_type part - BUT - your install is not looking for a single underscore at the beginning, it is looking for two (__select_type).

So, try replacing this line:

$allseltypes .= '<input name="'.$searchfield[0].'_select_type" type="hidden" value="contains">';

With one of these (depending on whether the $select_type_select_suffix variable is in your config.php or not):

$allseltypes .= '<input name="'.$searchfield[0].$select_type_select_suffix.'" type="hidden" value="contains">';
OR
$allseltypes .= '<input name="'.$searchfield[0].'__select_type" type="hidden" value="contains">';

Let me know if this resolves the issue.

_______________________________
..:: Debbie |\.-./| DaDaBIK Support ::..

Hi Debbie

Thanks a lot! This helped: second option with double underscore... ;-)

However, I realized that fields have to be published in the search form in order to be included into full text search. Is there a way to avoid this?

Example: I have a search form with two select_single options. The quick search, however, should also search in other fileds not being present (visible) in the search form.

Is this possible?

Thanks again for your support,

Juergen

FIRST ... to anyone who is using the $select_type_select_suffix variable to create the names of the fields using the quick search form. In order for this to work properly, you must ensure that everywhere in business_logic.php that refers to this variable, that it also appears in the global options line at the beginning of the function.

EG: in function build_form, if anywhere in the function the $select_type_select_suffix is referenced OR the function build_where_clause, etc., you must ensure that the variable is included in the $globals line near the top of the function!

Basically to use the quick search functionality, one must always remember to check the select type suffix in use in their own installation and change the quick search code accordingly (and add variables to global lines where appropriate).

***** ***** ***** ***** *****

NEXT ... Juergen ... My quick search on my installs does search through all fields that I've got in my database so long as they are listed as searchable fields in my config. If you want to change this, remove the following part from the SQL statement in your quick search:

 AND present_search_form_field != 0

That should do it.

_______________________________
..:: Debbie |\.-./| DaDaBIK Support ::..