Customize Advanced Search Drop-Down Menus in Search-form.php

Thanks, Patrick.

This solves the problem of $select being undefined, but throws an SQL error:

Zend_Db_Statement_Mysqli_Exception
Mysqli prepare error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘WHERE (items.public = 1) AND ( AND branch_search.text = ‘’) GROUP BY items.`id’ at line 2

Tried inspecting $params and that didn’t seem to tell me what I needed. I know the elementID is 182, but “branch=#####” is not picking up the Item type metadata that corresponds to this in the DB.

This morning, I thought of what might be an easier solution (though less elegant). Could I just hardcode the dropdown select menu (as discussed above), and have each option value return a set series of values that mimics the regular search feature?

I.e. - I can get the filter I want if I use Omeka’s existing advanced search feature (Narrow by specific fields), selecting “Branch of Philosophy”, query type “is exactly”, keywords “—Astronomy” = this runs the search with the following URL:

https://[...]/omeka/items/browse?search=&advanced%5B0%5D%5Belement_id%5D=182&advanced%5B0%5D%5Btype%5D=is+exactly&advanced%5B0%5D%5Bterms%5D=—Astronomy&type=&branch=0&submit_search=Search+for+items

I realize that in “search-form.php” this URL is being generated by three different form options, is there any way to just generate it with one? In other words, I want to hard-code my Branch of Philosophy select so that when the user selects “—Astronomy” it will pass along exactly the same data as the above search (instead of trying to force elementId, elementText, etc… to pickup in the SQL as we’ve been doing.

This might be a foolhardy workaround, but if it’s at all possible, I think it might just solve this problem.

Thanks for your help, as always.

-B

This might just take some tinkering with building the SQL. If the plugin is far enough along that it’s available on github, I can install it and take a closer look on my machine.

First thing looks like a duplicated AND, so changing the where clause to this might help:

$whereClause = " {$branch_search}.text = $value";

Regarding the alternate solution you’re thinking of, in theory that’d also work – you could hard-code what you need. The arrays of values that the advanced search builds up are complex, but it’s possible, with the caveat that you’d have to add some javascript. Because the advanced search allows you to add more and more terms, you’d have to check how many terms are in the ‘standard’ advanced search, and make sure you don’t conflict with those.

Update: I gave the form a second look, and it _might_be possible to avoid the javascript. I’m not 100% sure, though. Building up the array would still be a little tricky, probably requiring some hidden inputs to mimic the different inputs in the advanced search form.

Hey Patrick,

Thanks for the help. Been trying to work on the SQL syntax, but doesn’t seem to be getting me far. The change you proposed above fixed the earlier error but generates a new one:

Mysqli prepare error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE ( .text = ‘’) GROUP BY items.id ORDER BY items.added DESC, items’ at line 2

I’ve uploaded the current iteration of the plugin to git, though it’s not entirely functional yet. https://github.com/linktozelda/plugins/tree/dev

What do you think? Should we keep tinkering with the SQL? I tried to research syntax for joinLeft, but SQL is not my strong suit. If there would be some way to tinker with the advanced search form directly, I’d be happy to do that.

Stupid question, but where would I find the PHP or JS files that process the inputs submitted in search_form.php ? I feel like seeing these would help me understand a bit better how to build the array for submission…

Thanks, as always, for your continued help! -B

Update: I’ve just tried playing around with some SQL searches in phpmyadmin and managed to get it to spit out items associated with specific branches of philosophy (element id 182 in my plugin). If I want to return all items that have ‘Natural Philosophy’ as their branch of philosophy, phpmyadmin claims that this should be the SQL query I run:

SELECT * FROM `omeka_element_texts` WHERE `element_id` = 182 AND `text` LIKE 'Natural Philosophy' ORDER BY `omeka_element_texts`.`record_id` ASC

It converts this to the following in PHP:

$sql = "SELECT * FROM `omeka_element_texts` WHERE `element_id` = 182 AND `text` LIKE \'Natural Philosophy\'\n"
    . "ORDER BY `omeka_element_texts`.`record_id` ASC";

I imagine we can ignore the ORDER BY section, but what I think has been throwing this whole thing is that I keep assuming there’s a particular id assigned to each element text, when it doesn’t seem like there is - i.e. while the above query returns 252 results (all of which have ‘Natural Philosophy’ as their branch of philosophy), there are no other similarities in the records ( ID and RECORDID are each 252 unique values). Does this help shed any light at all on how we can refine the SQL syntax or play with the joinLeft query?

-B

**Final update for the day: I kept playing around with SQL queries in phpmyadmin and have successfully managed to use JOIN LEFT to spit out the table I need (for now I just have SELECT *). Here’s the SQL syntax:

SELECT * FROM omeka_element_texts LEFT JOIN omeka_items ON omeka_element_texts.record_id = omeka_items.id WHERE `element_id` = 182 AND `text`= 'Natural Philosophy'

And here’s the PHP it generates:

$sql = "SELECT *\n"
    . "FROM omeka_element_texts LEFT JOIN omeka_items\n"
    . "ON omeka_element_texts.record_id = omeka_items.id\n"
    . "WHERE `element_id` = 182 AND `text`= \'Natural Philosophy\'";

How can I pass this to the plugin and have it spit out advanced search results based on this query?

Related question: if we get this working and a user selects a particular branch of philosophy, will this play nicely with other search elements (I.e. if the user uses it in conjunction with the regular advanced search form to further refine their query?).

The PHP that processes the advanced search form is in models/Table/Items.php It’s often helpful to inspect the HTML code on the form to see how the array is put together.

You are right that particular element text ids aren’t necessarily stable, but the element ids are, which should be sufficient for this. I’ll look more closely at the SQL (which is also not my strong point) and see what I can see.

It should when it gets working, with either the SQL or the search array approach.

Hello,

I finally found a workable solution! After struggling for hours with trying to build a proper SQL syntax (I’d recommend this page https://framework.zend.com/manual/1.10/en/zend.db.select.html for anyone looking to do so), I managed to get rid of the error, but the search was STILL not picking up my query.

I then had another look at search-form.php as you suggested, and instead of building up an array with hidden inputs, etc… I just copied the html output to build a custom form with an option select for Branch of Philosophy in place of text input.

This worked, but kept throwing two problems: the first being that if I didn’t allow for a select default value of “” in [element_id], it kept thinking that I wanted to always search for ‘branch of philosophy’. The second issue had to do with the CSS, since class .search-entry links up with the add/remove a field button. I copy-pasted the following lines:

 /* @group ----- Advanced Search Form ----- */
  #advanced-search-form {
    margin-bottom: 40px;
  }
  #advanced-search-form .submit {
    float: right;
  }

  #advanced-search-form .search-entry input[type=text],
  #advanced-search-form .search-entry select {
    width: calc((100% - 2.625em) / 3);
    display: inline-block;
    margin: 0 3px 0 0;
    float: none;
  }

and appended -custom to both classes for my search div, which resolved the issue!

Not exactly an ideal workaround with SQL, but functional and plays nicely with other search inputs.

Here’s what my (working!) search-form.php looks like now:

<?php
if (!empty($formActionUri)):
    $formAttributes['action'] = $formActionUri;
else:
    $formAttributes['action'] = url(array('controller'=>'items',
                                          'action'=>'browse'));
endif;
$formAttributes['method'] = 'GET';
?>

>
    
<?php echo $this->formLabel('keyword-search', __('Search for Keywords')); ?>
<?php echo $this->formText( 'search', @$_REQUEST['search'], array('id' => 'keyword-search', 'size' => '40') ); ?>
<?php echo __('Narrow by Specific Fields'); ?>
<?php // If the form has been submitted, retain the number of search // fields used and rebuild the form if (!empty($_GET['advanced'])) { $search = $_GET['advanced']; } else { $search = array(array('field'=>'','type'=>'','value'=>'')); } //Here is where we actually build the search form foreach ($search as $i => $rows): //The POST looks like => // advanced[0] => //[field] = 'description' //[type] = 'contains' //[terms] = 'foobar' //etc ?>
<?php $htmlfield = ""; $htmlfield .= ""; $htmlfield .= "Select Below "; $htmlfield .= "Author"; $htmlfield .= "Title"; $htmlfield .= "Dedicatee"; $htmlfield .= "Printer"; $htmlfield .= "Date"; $htmlfield .= "City"; $htmlfield .= "Description"; $htmlfield .= "Branch of philosophy"; $htmlfield .= "Related to Aristotle's..."; $htmlfield .= "Type"; $htmlfield .= "Genre"; $htmlfield .= "Shelfmark"; $htmlfield .= "Has Format"; $htmlfield .= "Related Institution"; $htmlfield .= "Work ID"; $htmlfield .= "Manuscript ID"; $htmlfield .= "Edition ID"; echo $htmlfield; echo $this->formSelect( "advanced[$i][type]", @$rows['type'], array( 'title' => __("Search Type"), 'id' => null, 'class' => 'advanced-search-type' ), label_table_options(array( 'contains' => __('contains'), 'does not contain' => __('does not contain'), 'is exactly' => __('is exactly'), 'is empty' => __('is empty'), 'is not empty' => __('is not empty')) ) ); echo $this->formText( "advanced[$i][terms]", @$rows['terms'], array( 'size' => '20', 'title' => __("Search Terms"), 'id' => null, 'class' => 'advanced-search-terms' ) ); ?> <?php echo __('Remove field'); ?>
<?php endforeach; ?>
<?php echo __('Add a Field'); ?>
<?php /*
<?php echo $this->formLabel('range', __('Search by a range of ID#s (example: 1-4, 156, 79)')); ?>
<?php echo $this->formText('range', @$_GET['range'], array('size' => '40') ); ?>
<?php echo $this->formLabel('collection-search', __('Search By Collection')); ?>
<?php echo $this->formSelect( 'collection', @$_REQUEST['collection'], array('id' => 'collection-search'), get_table_options('Collection') ); ?>
*/ ?>
<?php echo $this->formLabel('item-type-search', __('Record Type')); ?>
<?php echo $this->formSelect( 'type', @$_REQUEST['type'], array('id' => 'item-type-search'), get_table_options('ItemType') ); ?>
<?php echo $this->formLabel('item-type-search', __('Branch of Philosophy')); ?>
Select below to specify Branch of Philosophy is Select Below Logic Natural Philosophy —Physics —Biology —Astronomy —Psychology Moral Philosophy —Ethics —Politics —Economics Metaphysics Rhetoric Poetics
<?php if (!isset($buttonText)) $buttonText = __('Search for items'); ?>
<?php echo js_tag('items-search'); ?> |

Thanks again for all of your help and input with this!