echo htmlspecialchars($keyword); ?> " size="80" /></p> <input type="submit" /> </form> <pre> <?php require_once 'parse_model.php'; // Required for the PDO::quote() function. $o_db = new PDO('pgsql:host=localhost;dbname=mydb', 'user', 'pass'); $o_parse = new parse_model(); $o_parse->debug = true; $o_parse->upper_op_only = $upper_op_only; $o_parse->use_prepared_sql = $use_prepared_sql; $o_parse->set_default_op($op); if ($keyword != '') { if ($o_parse->parse($keyword, $field_name) == false) { echo "Message to user: [{$o_parse->error_msg}]\n\n"; } else { $query = "SELECT * FROM some_table WHERE\n"; if ($o_parse->tsearch != '') { $query .= "fulltext @@ to_tsquery(" . $o_db->quote($o_parse->tsearch) . ")\n"; } if ($o_parse->ilike != '') { $query .= ($o_parse->tsearch != '' ? "AND " : '') . "\n({$o_parse->ilike})"; } echo "\nSAMPLE QUERY:\n\n{$query}\n\n"; } } ?>
function createTextQuery($searchFields, $additionalFields) { SpotTiming::start(__CLASS__ . '::' . __FUNCTION__); /* * Initialize some basic values which are used as return values to * make sure always return a valid set */ $filterValueSql = array(); $sortFields = array(); foreach ($searchFields as $searchItem) { $searchValue = trim($searchItem['value']); $field = $searchItem['fieldname']; /* * if we get multiple textsearches, we sort them per order * in the system */ $tmpSortCounter = count($additionalFields); # Prepare the to_tsvector and to_tsquery strings $ts_vector = "to_tsvector('Dutch', " . $field . ")"; /* * Inititialize Digital Stratum's FTS2 parser so we can * give the user somewhat normal search query parameters */ $o_parse = new parse_model(); $o_parse->debug = false; $o_parse->upper_op_only = true; $o_parse->use_prepared_sql = false; $o_parse->set_default_op('AND'); /* * Do some preparation for the searchvalue, test cases: * * +"Revolution (2012)" +"Season 2" */ $searchValue = $this->prepareFtsQuery($searchValue); /* * First try to the parse the query using this library, * if that fails, fall back to letting PostgreSQL crudely * parse it */ if ($o_parse->parse($searchValue, $field) === false) { $ts_query = "plainto_tsquery('Dutch', " . $this->_db->safe(strtolower($searchValue)) . ")"; $filterValueSql[] = " " . $ts_vector . " @@ " . $ts_query; $additionalFields[] = " ts_rank(" . $ts_vector . ", " . $ts_query . ") AS searchrelevancy" . $tmpSortCounter; } else { $queryPart = array(); if (!empty($o_parse->tsearch)) { $ts_query = "to_tsquery('Dutch', " . $this->_db->safe($o_parse->tsearch) . ")"; $queryPart[] = " " . $ts_vector . " @@ " . $ts_query; $additionalFields[] = " ts_rank(" . $ts_vector . ", " . $ts_query . ") AS searchrelevancy" . $tmpSortCounter; } # if if (!empty($o_parse->ilike)) { $queryPart[] = $o_parse->ilike; } # if /* * Add the textqueries with an AND per search term */ if (!empty($queryPart)) { $filterValueSql[] = ' (' . implode(' AND ', $queryPart) . ') '; } # if } # else $sortFields[] = array('field' => 'searchrelevancy' . $tmpSortCounter, 'direction' => 'DESC', 'autoadded' => true, 'friendlyname' => null); } # foreach SpotTiming::stop(__CLASS__ . '::' . __FUNCTION__, array($filterValueSql, $additionalFields, $sortFields)); return array('filterValueSql' => $filterValueSql, 'additionalTables' => array(), 'additionalFields' => $additionalFields, 'sortFields' => $sortFields); }