We launched new forums in March 2019—join us there. In a hurry for help with your website? Get Help Now!
    • 45118
    • 123 Posts
    I have a simple search box that looks in two database fields for search terms. One field contains an item number and the second one keywords. The keywords are separated by comma and space, and can also be keyword phrases.

    Search form HTML:
    Search Our Site:
    <form method="get" action="[[~25]]">
        <input type="text" name="search" value="" />
        <input type="submit" value="Go" />
    </form>

    In the snippet:
    if (isset($_GET['search']) && (! empty($_GET['search']))) {
        $term = $_GET['search'];
    } else {
        $term = "";
    }

    and
    $c = $modx->newQuery('Products');
    $c->where(array('num:LIKE' => '%'.$term.'%'));
    $c->where(array('keyw:LIKE' => '%'.$term.'%'),xPDOQuery::SQL_OR);


    This works great for the item numbers, you can find complete numbers or with the help of % as a wildcard you can search with only a part of an item number.
    For the keywords it gets tricky. You can search for a single keyword just fine, but if you're searching with two it only works when you separate them with the wildcard %. Plus it only works when you enter the keywords in the order they're in in the keyword field.
    For example, the content of the keywords field is: apple, orange, grape, melon, green, red, orange, yellow
    When you search: apple%yellow it finds all yellow apples. But when you search yellow%apple, it gives no result.

    I would like this keyword part to work better, any help is very much appreciated!
      • 3749
      • 24,544 Posts
      Try making this the only where. Notice that the two term variables are different.

       
      $c->where(array(
          'num:LIKE' => '%' . $term1 . '%',
          'keyw:LIKE' => '%' . $term2 . '%', xPDOQuery::SQL_OR,
      ));
      


      It that doesn't work, try this:

      $c->where(array(
          'num:LIKE' => '%' . $term1 . '%',
          'OR:keyw:LIKE' => '%' . $term2 . '%',
      ));
        Did I help you? Buy me a beer
        Get my Book: MODX:The Official Guide
        MODX info for everyone: http://bobsguides.com/modx.html
        My MODX Extras
        Bob's Guides is now hosted at A2 MODX Hosting
        • 45118
        • 123 Posts
        Quote from: BobRay at Apr 10, 2017, 09:13 PM
        Try making this the only where. Notice that the two term variables are different.

         
        $c->where(array(
            'num:LIKE' => '%' . $term1 . '%',
            'keyw:LIKE' => '%' . $term2 . '%', xPDOQuery::SQL_OR,
        ));
        


        It that doesn't work, try this:

        $c->where(array(
            'num:LIKE' => '%' . $term1 . '%',
            'OR:keyw:LIKE' => '%' . $term2 . '%',
        ));
        The first one doesn't work and the second one gave the same results as I had before; I have to separate two keywords with % and they have to be in the same order as in the keyword field for a result to show up...
          • 3749
          • 24,544 Posts
          Hmm. I really thought that would work. Here's something else to try:


          $query->where(array(
             array(
                'num:LIKE' => '%' . $term1 . '%',
             ),
             array(
                'OR:keyw:LIKE:' => '%' . $term2 . '%',
             )
          ));
            Did I help you? Buy me a beer
            Get my Book: MODX:The Official Guide
            MODX info for everyone: http://bobsguides.com/modx.html
            My MODX Extras
            Bob's Guides is now hosted at A2 MODX Hosting