#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2013
    Posts
    5
    Rep Power
    0

    Search with multiply words


    I'm about to update my search script. The search engine searches in my database where I ask it to search. What is new is I want do some search words, that the users can check in a checkbox, if he wants to use the word in the search.

    As for now my search engine works, the only problem is that it only searches the last word and not all of the checked words. My formula looks like this:

    PHP Code:
    <form method="POST" action="<?=$_SERVER["PHP_SELF"]?>"> 
    <p>Search for: 
    </p> 
    Books: <input type="checkbox" name='search' value="books"> 
    Movies: <input type="checkbox" name='search' value="movies"> 
    Outdoor: <input type="checkbox" name='search' value="outdoor"> 
    Indore: <input type="checkbox" name='search' value="indore"> 
    </p> 
    <p><input type='submit'  value='Search'></p> 
    </form>
    The php code looks like:
    PHP Code:
    <?php 
    if(isset($_POST['search'])) 

      
    $connx mysql_connect('localhost''*******'',*********') or die("connx"); 
      
    $db mysql_select_db('*********') or die(mysql_error()); 
      
      
    # convert to upper case, trim it, and replace spaces with "|": 
      
    $search mysql_real_escape_string($search); 
      
    $search strtoupper(preg_replace('/\s+/''|', ($_POST['search']))); 
      
      
    # create a MySQL REGEXP for the search: 
      
    $regexp "REGEXP '[[:<:]]($search)[[:>:]]'"
      
    $query "SELECT * FROM `keywords` WHERE UPPER(`keywords01`) $regexp OR "
               
    "`keywords02` $regexp OR "
               
    "`keywords03` $regexp OR "
               
    "`keywords04` $regexp"
      
      
    $result mysql_query($query) or die($query " - " mysql_error()); 
      
    echo 
    "<table>\n"
    while(
    $row mysql_fetch_assoc($result)) 


        echo 
    "<tr>"
        echo 
    "<td><img src=../{$row['type']}/{$row['folder']}/{$row['date']}-{$row['num']}/{$row['thumbimage']} border=1></td>";
        echo 
    "<td>{$row['name']}</td>"
        echo 
    "<td>{$row['date']}</td>"
        echo 
    "<td><a href=../view.php?id={$row['id']} target=blank>VIEW</a></td>"
        echo 
    "</tr>\n"
      }  

     else {
      echo 
    "<p>Sorry, no results matched your search.</p>";
    }
    ?>
    Are there someone, who can figure out, why it is only the last marked checkboxs word that are searched and not all marked words and how do I get it to search for all marked words?

    Hope someone can help.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2009
    Posts
    667
    Rep Power
    6
    You only submit 1 value. Because you named every input box the same, each will start to submit, but as it makes its way, the next one over-writes the previous because of this. You can only submit 1 item like this. You will need to rename, or set these to an array.
    He who knows not and knows not he knows not: he is a fool - shun him. He who knows not and knows he knows not: he is simple - teach him. He who knows and knows not he knows: he is asleep - wake him. He who knows and knows he knows: he is wise - follow him
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2013
    Posts
    5
    Rep Power
    0
    I understand, But how do I add the codes to my PHP for an array I how no idea of how that codes look like:

    PHP Code:
    <?php 
    if(isset($_POST['search'])) 

      
    $connx mysql_connect('localhost''*******'',*********') or die("connx"); 
      
    $db mysql_select_db('*********') or die(mysql_error()); 
      
      
    # convert to upper case, trim it, and replace spaces with "|": 
      
    $search mysql_real_escape_string($search); 
      
    $search strtoupper(preg_replace('/\s+/''|', ($_POST['search']))); 
      
      
    # create a MySQL REGEXP for the search: 
      
    $regexp "REGEXP '[[:<:]]($search)[[:>:]]'"
      
    $query "SELECT * FROM `keywords` WHERE UPPER(`keywords01`) $regexp OR "
               
    "`keywords02` $regexp OR "
               
    "`keywords03` $regexp OR "
               
    "`keywords04` $regexp"
      
      
    $result mysql_query($query) or die($query " - " mysql_error()); 
      
    echo 
    "<table>\n"
    while(
    $row mysql_fetch_assoc($result)) 


        echo 
    "<tr>"
        echo 
    "<td><img src=../{$row['type']}/{$row['folder']}/{$row['date']}-{$row['num']}/{$row['thumbimage']} border=1></td>";
        echo 
    "<td>{$row['name']}</td>"
        echo 
    "<td>{$row['date']}</td>"
        echo 
    "<td><a href=../view.php?id={$row['id']} target=blank>VIEW</a></td>"
        echo 
    "</tr>\n"
      }  

     else {
      echo 
    "<p>Sorry, no results matched your search.</p>";
    }
    ?>
    I know my form will be like:
    PHP Code:
    <form method="POST" action="<?=$_SERVER["PHP_SELF"]?>"> 
    <p>Search for: 
    </p> 
    Books: <input type="checkbox" name='search[]' value="books"> 
    Movies: <input type="checkbox" name='search[]' value="movies"> 
    Outdoor: <input type="checkbox" name='search[]' value="outdoor"> 
    Indore: <input type="checkbox" name='search[]' value="indore"> 
    </p> 
    <p><input type='submit'  value='Search'></p> 
    </form>
    Can you please advice?
  6. #4
  7. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,911
    Rep Power
    1045
    Hi,

    your database model is weird. So you stuff all keywords in a single SQL string and then use that regex hack to separate them again? This is wrong and violates the first normal form. SQL values are supposed to be atomic. No lists, no sets, no compositions.

    That "keywords01, keywords02, ..." stuff also very cumbersome and hard to understand. I guess keywords01 holds the keywords for the books, keywords02 contains the movie keywords etc.?

    You really need to fix this. I suggest you make two tables: keyword_categories and keywords. The first contains all possible categories, i. e. "book", "movie", ... And the letter contains a single keyword per row together with the corresponding category.

    Like this:

    Code:
    keyword_categories
    - category_id INT, primary key
    - name VARCHAR
    Code:
    keywords
    - keyword_id INT, primary key
    - category INT, references keyword_categories (category_id)
    - keyword VARCHAR
    Now your query will be trivial, because you can do a simple string comparison instead of fumbling with regexes.

    There are also several issues with your PHP code. First of all, you really need to read up on security. You have no HTML escaping at all, and you happily show your internal database errors to anybody who happens to visit your site.

    Secondly, those mysql_* functions are ancient. They've been replaced with modern database extensions almost a decade ago, and they're about to be removed completely. So it's about time. Unless you're dealing with 100,000 lines of legacy code, stop it and rewrite your scripts using either PDO or MySQLi.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Dec 2004
    Posts
    2,868
    Rep Power
    368
    something like:

    $where = array();
    foreach ( $_POST['search'] as $value ) {
    $regexp = "REGEXP '[[:<:]]($value )[[:>:]]'";
    $where[] = ''column = $regexp";
    }

    $query = "blah blah". implode(" AND ", $where );
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Posts
    49
    Rep Power
    0
    just use where column %like this
  12. #7
  13. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,911
    Rep Power
    1045
    Originally Posted by p0rt
    just use where column %like this
    So "ham" should be found in "hamster"? Yeah.

    But I guess you're only here for spamming.
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Posts
    49
    Rep Power
    0
    theres abit more to %like, then just partial matches, regex might be good for math and science sites, its seems a waste of time for just words

    http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html
  16. #9
  17. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,911
    Rep Power
    1045
    Apart from the fact that you obviously haven't read #4 which makes this whole discussion obsolete, I'd be very interested in seeing how you separate words only using LIKE.

    In particular, the pattern must find "ham" in

    'ham'
    'ham foo'
    'foo ham bar'
    'foo ham'

    but not in

    'hamster'

    Please don't tell me you simply gonna enumerate all four valid cases.



    Originally Posted by p0rt
    theres abit more to %like, then just partial matches
    So what is that?
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Posts
    49
    Rep Power
    0
    use and instead of or, most searches use %like for all terms or exact match
  20. #11
  21. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,911
    Rep Power
    1045
    So where's your query? And what's the secret feature of LIKE apart from finding substrings?

IMN logo majestic logo threadwatch logo seochat tools logo