quick search form

M

meanster99

Well-known member
Hi,

I have attempted to implement a quick search feature as outlined in this post:

Single Search Box

However, I get the following error message:

[08] Error: during query execution.
SELECT `customer_booking_form`.`Date_Of_Event`, `customer_booking_form`.`ID`, `customer_booking_form`.`firstname`, `customer_booking_form`.`surname`, `customer_booking_form`.`telephone`, `customer_booking_form`.`email`, `customer_booking_form`.`service_required`, `customer_booking_form`.`occasion`, `customer_booking_form`.`hr`, `customer_booking_form`.`mn`, `customer_booking_form`.`ampm`, `customer_booking_form`.`venue_name`, `customer_booking_form`.`venue_address`, `customer_booking_form`.`venue_address2`, `customer_booking_form`.`venue_town`, `customer_booking_form`.`venue_county`, `customer_booking_form`.`venue_postcode`, `customer_booking_form`.`venue_telephone`, `customer_booking_form`.`costume_required`, `customer_booking_form`.`act_name`, `customer_booking_form`.`notes`, `customer_booking_form`.`terms_conditions`, `customer_booking_form`.`total_price`, `customer_booking_form`.`booking_fee`, `customer_booking_form`.`balance`, `customer_booking_form`.`payment_type`, `customer_booking_form`.`voucher_code`, `customer_booking_form`.`status`, `customer_booking_form`.`_sfm_form_submision_date_`, `customer_booking_form`.`_sfm_visitor_ip_`, `customer_booking_form`.`user_ID`, `customer_booking_form`.`contact_type`, `customer_booking_form`.`details_sent`, `artiste_contacts__1`.`Stage_Name` AS `artiste_contacts__Stage_Name__1`, `customer_booking_form`.`completed` FROM `customer_booking_form` LEFT JOIN `artiste_contacts` AS `artiste_contacts__1` ON `customer_booking_form`.`act_sent` = `artiste_contacts__1`.`ID` WHERE or 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 or' at line 1

Any ideas anyone?

Thanks,

Matt
 
M

meanster99

Well-known member
OK, having read the entire post referenced above, I now having a semi-working quick search input.

I used the code below, taken from the post referenced above. It doesn't appear to be searching all of those field types, but does manage to return records for some fields.

<?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 don't get any error messages so why would the query not be checking all fields of field type text and text area? Any suggestions?

Thanks,

Matt
 
D

DebbieS

DaDaBIK Guru
Is this located somewhere I can view it?
When viewing the

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

part, not all the fields of text/textarea are displayed?

I'm not sure what to suggest as I've never come across it not bringing in all the fields specified.
 
M

meanster99

Well-known member
Hi Debbie,

No, all fields are showing up in the php echo. But the query clearly isn't returning all the results it should.

Can't show you a working copy but the query from a search for the word 'red' (which should have returned at least one result, but returned zero records), is as follows:

Your SQL query (for debugging purpose): SELECT `customer_booking_form`.`Date_Of_Event`, `customer_booking_form`.`venue_town`, `customer_booking_form`.`hr`, `customer_booking_form`.`mn`, `customer_booking_form`.`ampm`, `customer_booking_form`.`ID`, `customer_booking_form`.`firstname`, `customer_booking_form`.`surname`, `customer_booking_form`.`telephone`, `customer_booking_form`.`email`, `customer_booking_form`.`service_required`, `customer_booking_form`.`occasion`, `customer_booking_form`.`recip_name`, `customer_booking_form`.`venue_name`, `customer_booking_form`.`venue_address`, `customer_booking_form`.`venue_address2`, `customer_booking_form`.`venue_county`, `customer_booking_form`.`venue_postcode`, `customer_booking_form`.`venue_telephone`, `customer_booking_form`.`costume_required`, `customer_booking_form`.`act_name`, `customer_booking_form`.`notes`, `customer_booking_form`.`terms_conditions`, `customer_booking_form`.`total_price`, `customer_booking_form`.`booking_fee`, `customer_booking_form`.`balance`, `customer_booking_form`.`payment_type`, `customer_booking_form`.`voucher_code`, `customer_booking_form`.`status`, `customer_booking_form`.`_sfm_form_submision_date_`, `customer_booking_form`.`_sfm_visitor_ip_`, `customer_booking_form`.`user_ID`, `customer_booking_form`.`contact_type`, `customer_booking_form`.`details_sent`, `artiste_contacts__1`.`Stage_Name` AS `artiste_contacts__Stage_Name__1`, `customer_booking_form`.`completed` FROM `customer_booking_form` LEFT JOIN `artiste_contacts` AS `artiste_contacts__1` ON `customer_booking_form`.`act_sent` = `artiste_contacts__1`.`ID` WHERE `customer_booking_form`.`Date_Of_Event` LIKE '%red%' or `customer_booking_form`.`firstname` LIKE '%red%' or `customer_booking_form`.`surname` LIKE '%red%' or `customer_booking_form`.`telephone` LIKE '%red%' or `customer_booking_form`.`email` LIKE '%red%' or `customer_booking_form`.`status` LIKE '%red%' or `customer_booking_form`.`_sfm_form_submision_date_` LIKE '%red%' or `customer_booking_form`.`details_sent` LIKE '%red%' or `customer_booking_form`.`act_sent` LIKE '%red%' or `customer_booking_form`.`completed` LIKE '%red%'

I don't know enough about this stuff to figure it, but could it be something to do with the LEFT JOIN? I thought the quick search searched ALL fields in ALL tables, but it is only referencing 2 of my tables and I have 3 main ones. Perhaps it has something to do with collation? I am using utf8_general_ci currently.

Any ideas?

Thanks,

Matt
 
D

DebbieS

DaDaBIK Guru
The left join I believe has to do with a select_single field where the data is coming from another table.
The quick search will search in the CURRENT table only. If you want it to search in the other tables, you will need to modify the search paramaters in the first part of the code.
 
M

meanster99

Well-known member
Hi Debbie,

As you will see in the query above, it's only appearing (to me anyway!) to be searching for 'red' in a handful of the fields, even though I've set the code to all fields. Its selecting all fields with the SELECT statement though.

Why don't all fieldnames from 'customer_booking_form' appear after the LEFT JOIN statement, with a LIKE '%red%' part? Also, although it first appeared to me that those fieldnames that were included in the LIKE part were the only fields I could return results from, having checked, even one of those ('status') returns zero records when it shouldn't. I'm not understanding at all!

Is there more info I could provide you that may help?!

Also, how would I code it to search ALL tables (or preferably the tables I specify)? Sorry for being such a noob!
 
D

DebbieS

DaDaBIK Guru
Actually, the line you have uncommented in the php from post dated February 04, 2012 09:55PM looks for fields of type text, textarea and rich_editor - not ALL the fields in the table.

[pre]
// 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')";
[/pre]

If you want ALL field types, you need to comment the line above and uncomment the one with no where clause:

[pre]
//This line searches all fields.
//$sqlcols = "SELECT name_field FROM ".$prefix_internal_table.$table_name."";
[/pre]

The fields that are not showing up with LIKEs are likely not set to text, textarea or rich_editor.

DaDaBIK is set up to manage table by table - not sure how to incorporate a search across multiple tables and output the results. Suspect it to be a huge modification to the existing code.
 
M

meanster99

Well-known member
Hi Debbie,

Sorry, I had already removed the WHERE part of the statement, just hadn't reflected that in the code post quote above. So I am searching for all fields. Either way, some of the fields that are not showing up in the LIKE part are definitely text fields, so still confused!

Currently, the search does work for most of the LIKE fields above, but that is only a very small percentage of the total fields, text or otherwise. I just can't fathom it!

Thanks for all your help anyway Debbie. I shall probably have to leave it as is and hope an answer comes to me when I am more experienced!
 
D

DebbieS

DaDaBIK Guru
I just tried one of my installs with all fields included in the search. It captured all but 4 fields from my database, however, those 4 fields I have set as hidden from view (behind the scenes fields not required for meaningful display). So it did work to search through all fields available in the database table.

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.
 
M

meanster99

Well-known member
Hi Debbie - still at a complete loss over this. Therefore, please find below:

1) The php code to display the search form (I have reverted to ('text','textarea','rich_editor') for the WHERE clause):

[pre]
<?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 specified 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 ('venue_town','firstname','surname,'telephone','venue_postcode','county')";
//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 style="color:#fff; font-size:12px; padding-bottom:5px;" name="searchall" class="noformpad" 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 Name or Tel No. Search:
<input type="hidden" name="operator" value="or"/>
<?php echo $allseltypes; ?>
<input name="allterm" type="text" size="20" 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")
?>

[/pre]

2) The HTML of resulting search form:

[pre]
<pre>ID _sfm_form_submision_time_ _sfm_visitor_ip_ firstname surname telephone email venue_name venue_address venue_address2 venue_town venue_postcode venue_telephone notes total_price booking_fee act_name balance voucher_code recip_name </pre>
<form style="color:#fff; font-size:12px; padding-bottom:5px;" name="searchall" class="noformpad" method="post" action="index.php?table_name=customer_booking_form&page=0&function=search&execute_search=1" enctype="multipart/form-data" accept-charset="Windows-1255" onsubmit="javascript:document.searchall.ID.value = document.searchall.allterm.value;document.searchall._sfm_form_submision_time_.value = document.searchall.allterm.value;document.searchall._sfm_visitor_ip_.value = document.searchall.allterm.value;document.searchall.firstname.value = document.searchall.allterm.value;document.searchall.surname.value = document.searchall.allterm.value;document.searchall.telephone.value = document.searchall.allterm.value;document.searchall.email.value = document.searchall.allterm.value;document.searchall.venue_name.value = document.searchall.allterm.value;document.searchall.venue_address.value = document.searchall.allterm.value;document.searchall.venue_address2.value = document.searchall.allterm.value;document.searchall.venue_town.value = document.searchall.allterm.value;document.searchall.venue_postcode.value = document.searchall.allterm.value;document.searchall.venue_telephone.value = document.searchall.allterm.value;document.searchall.notes.value = document.searchall.allterm.value;document.searchall.total_price.value = document.searchall.allterm.value;document.searchall.booking_fee.value = document.searchall.allterm.value;document.searchall.act_name.value = document.searchall.allterm.value;document.searchall.balance.value = document.searchall.allterm.value;document.searchall.voucher_code.value = document.searchall.allterm.value;document.searchall.recip_name.value = document.searchall.allterm.value;return true;">
  Quick Name or Tel No. Search:
<input type="hidden" name="operator" value="or"/>
<input name="ID__select_type" type="hidden" value="contains"><input name="_sfm_form_submision_time___select_type" type="hidden" value="contains"><input name="_sfm_visitor_ip___select_type" type="hidden" value="contains"><input name="firstname__select_type" type="hidden" value="contains"><input name="surname__select_type" type="hidden" value="contains"><input name="telephone__select_type" type="hidden" value="contains"><input name="email__select_type" type="hidden" value="contains"><input name="venue_name__select_type" type="hidden" value="contains"><input name="venue_address__select_type" type="hidden" value="contains"><input name="venue_address2__select_type" type="hidden" value="contains"><input name="venue_town__select_type" type="hidden" value="contains"><input name="venue_postcode__select_type" type="hidden" value="contains"><input name="venue_telephone__select_type" type="hidden" value="contains"><input name="notes__select_type" type="hidden" value="contains"><input name="total_price__select_type" type="hidden" value="contains"><input name="booking_fee__select_type" type="hidden" value="contains"><input name="act_name__select_type" type="hidden" value="contains"><input name="balance__select_type" type="hidden" value="contains"><input name="voucher_code__select_type" type="hidden" value="contains"><input name="recip_name__select_type" type="hidden" value="contains"><input name="allterm" type="text" size="20" maxlength="100"/>
<input name="ID" type="hidden" value=""><input name="_sfm_form_submision_time_" type="hidden" value=""><input name="_sfm_visitor_ip_" type="hidden" value=""><input name="firstname" type="hidden" value=""><input name="surname" type="hidden" value=""><input name="telephone" type="hidden" value=""><input name="email" type="hidden" value=""><input name="venue_name" type="hidden" value=""><input name="venue_address" type="hidden" value=""><input name="venue_address2" type="hidden" value=""><input name="venue_town" type="hidden" value=""><input name="venue_postcode" type="hidden" value=""><input name="venue_telephone" type="hidden" value=""><input name="notes" type="hidden" value=""><input name="total_price" type="hidden" value=""><input name="booking_fee" type="hidden" value=""><input name="act_name" type="hidden" value=""><input name="balance" type="hidden" value=""><input name="voucher_code" type="hidden" value=""><input name="recip_name" type="hidden" value=""><input type="submit" value="Search" class="submit"/>
</form>
[/pre]

3) But with only the fieldnames I care about being included (the fields in bold are NOT currently being searched by the query, so if I could just get these 3 included, I'd be happy!):

firstname, surname, venue_town, venue_county, telephone, email, notes.

And this is the result from the SQL query:

Your SQL query (for debugging purpose): SELECT `customer_booking_form`.`Date_Of_Event`, `customer_booking_form`.`venue_town`, `customer_booking_form`.`hr`, `customer_booking_form`.`mn`, `customer_booking_form`.`ampm`, `customer_booking_form`.`ID`, `customer_booking_form`.`firstname`, `customer_booking_form`.`surname`, `customer_booking_form`.`telephone`, `customer_booking_form`.`email`, `customer_booking_form`.`service_required`, `customer_booking_form`.`occasion`, `customer_booking_form`.`recip_name`, `customer_booking_form`.`venue_name`, `customer_booking_form`.`venue_address`, `customer_booking_form`.`venue_address2`, `customer_booking_form`.`venue_county`, `customer_booking_form`.`venue_postcode`, `customer_booking_form`.`venue_telephone`, `customer_booking_form`.`costume_required`, `customer_booking_form`.`act_name`, `customer_booking_form`.`notes`, `customer_booking_form`.`terms_conditions`, `customer_booking_form`.`total_price`, `customer_booking_form`.`booking_fee`, `customer_booking_form`.`balance`, `customer_booking_form`.`payment_type`, `customer_booking_form`.`voucher_code`, `customer_booking_form`.`status`, `customer_booking_form`.`_sfm_form_submision_date_`, `customer_booking_form`.`_sfm_visitor_ip_`, `customer_booking_form`.`user_ID`, `customer_booking_form`.`contact_type`, `customer_booking_form`.`details_sent`, `artiste_contacts__1`.`Stage_Name` AS `artiste_contacts__Stage_Name__1`, `customer_booking_form`.`act_gender`, `customer_booking_form`.`completed` FROM `customer_booking_form` LEFT JOIN `artiste_contacts` AS `artiste_contacts__1` ON `customer_booking_form`.`act_sent` = `artiste_contacts__1`.`act_ID` WHERE `customer_booking_form`.`firstname` LIKE '%london%' or `customer_booking_form`.`surname` LIKE '%london%' or `customer_booking_form`.`telephone` LIKE '%london%' or `customer_booking_form`.`email` LIKE '%london%'

It appears to be setting up the search form OK and its only the where clause of the sql statement that isn't including all the fields, and only searching in 4 of them.

Thanks Debbie.
 
D

DebbieS

DaDaBIK Guru
If you go to your regular search page in DaDaBIK and put in one term in each of the text fields and search using the "or" option in the form, will the search go through all fields and not miss any? What is the resulting query from that?

I really don't know what to suggest at this point other than some character limit being imposed somewhere - perhaps a maximum post value being passed via form is being truncated? That is highly unlikely, but it is weird that it stops at the field it does but ignores the first three fields completely.

Does it work if you specify the main fields you want the search to be available in?

Sorry, without seeing it, it is really hard to come up with suggestions.
 
H

hedo

New member
solved problem? I have similar problem...

-----------------------------------------------------------------------------------------------
mocne kartony.
 
M

meanster99

Well-known member
Hi Hedo,

Not sure if it's too late or you have already found a solution to this, but having been helped by Debbie and others, I realised the problem was with the fields I had included in the search form (i.e. the fields set to 'Y' in interface configurator, Search Form). Please follow Debbie's advice in the info below:

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).

Thanks Debbie, as always...
 
You must log in or register to reply here.
Top