Thread: Search function

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

    Join Date
    Jun 2013
    Posts
    120
    Rep Power
    2

    Search function


    Hey, I have a search function that I have set up to search 4 tables in the database by 4 columns in each table, name, description, keywords and url. I was hoping to get an exact search with only the rows relating to the keyword or keywords that some enters into the search bar but instead it pulls up all rows in all 4 tables based on whatever keyword(s) I enter. I've posted my code below and I'd really appreciate any help to find a better way of accomplishing this! Thanks for your time.

    search.func.php
    PHP Code:
    <?php

    function search_results($keywords) {
        
    $returned_results = array();
        
    $where '';
        
        
    $keywords preg_split('/[\s]+/'$keywords);
        
    $total_keywords count($keywords);
        
        foreach (
    $keywords as $key=>$keyword) {
            
    $where .= "`keywords` LIKE '%$keyword%'";
            if (
    $key != ($total_keywords 1)) {
                
    $where .= "AND";
            }
        }
        
        
    $results "
        SELECT `name`, LEFT(`description`, 300) as `description`, `keywords`, `url` FROM `trailers` 
        UNION
        SELECT `name`, LEFT(`description`, 300) as `description`, `keywords`, `url` FROM `parts`
        UNION
        SELECT `name`, LEFT(`description`, 300) as `description`, `keywords`, `url` FROM `events`
        UNION
        SELECT `name`, LEFT(`description`, 300) as `description`, `keywords`, `url` FROM `jobs`   
        WHERE 
    $where
        "
    ;
        
    $results_num = ($results mysql_query($results)) ? mysql_num_rows($results): 0;
        
        if (
    $results_num === 0) {
            return 
    false;
        } else {
            
            while (
    $results_row mysql_fetch_assoc($results)) {
                
    $returned_results[] = array(
                    
    'name' => $results_row['name'],
                    
    'description' => $results_row['description'],
                    
    'url' => $results_row['url'],
                    
    'keywords' => $results_row['keywords'
                );
            }
            
            return 
    $returned_results;
            
        }
    }

    function 
    search() {     
    if (isset(
    $_POST['keywords'])) {
        
    $suffix '';
        
    $keywords mysql_real_escape_string(htmlentities(trim($_POST['keywords'])));
        
        
    $errors = array();
        
        if (empty(
    $keywords)) {
            
    $errors[] = 'Please enter a search term.';
        } 
        
        if (
    strlen($keywords) < 3) {
            
    $errors[] = 'Your search term must be 3 characters or more.';
        }
        
        if (
    search_results($keywords) === false) {
            
    $errors[] = 'Your search for <span style="color: green;">('.$keywords.')</span> returned no results.';
        }
        
        if (empty(
    $errors)) {
            
            
    $results search_results($keywords);
            
    $results_num count($results);
            
            
    $suffix = ($results_num != 1) ? 's' '';
            
            echo 
    '<div style="float: left; text-align: left; width: 100%; height: auto;"><span class="searchfont">Your search for <strong><span style="color: green;">'$keywords'</span></strong> returned <strong><span style="color: green;">'$results_num'</span></strong> result'$suffix'</span></div><br /><br />';
            
            foreach (
    $results as $result) {
                if (
    strlen($result['description']) > 200) {
                    
    $dots '...';
                } else {
                    
    $dots '';
                }
                
                echo 
    '
                <div style="float: left; text-align: left; width: 100%; height: auto; margin-bottom: 20px;">
                    <strong><a href="'
    ,$result['url'],'" target="_self" class="searchfont"><u>'$result['name'], '</u></a></strong><br />
                    <a href="'
    $result['url'], '" target="_self" class="searchfont3" style="color: green;">'$result['url'], '</a><br />
                    <span class="searchfont2">'
    $result['description'],$dots'</span><br />
                    <span class="font15" style="color: blue;">'
    ,$result['keywords'],'</span>
                </div>'
    ;
            }
            
        } else {
            foreach (
    $errors as $error) {
                echo 
    '<div style="float: left; text-align: left; width: 100%; height: auto;"><span class="searchfont">',$error'</span></div><br />';
            }
        }
    }
    }

    ?>
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    101
    Rep Power
    27
    Don't you have to apply the WHERE clause to each SELECT in your union?

    SELECT ... from foo WHERE keywords like whatever
    UNION ALL
    SELECT ... from baz WHERE keywords like whatever
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    120
    Rep Power
    2
    Ya I have that in there, WHERE $where and $where looks like this,

    $where .= "`keywords` LIKE '%$keyword%'";
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    101
    Rep Power
    27
    You only have "WHERE $where" attached to the fourth part of the union. I'm saying, I think you have to have that clause on EVERY part of the union.
    PHP Code:
    $results "
        SELECT `name`, LEFT(`description`, 300) as `description`, `keywords`, `url` FROM `trailers` 
        WHERE 
    $where
        UNION
        SELECT `name`, LEFT(`description`, 300) as `description`, `keywords`, `url` FROM `parts`
        WHERE 
    $where
        UNION
        SELECT `name`, LEFT(`description`, 300) as `description`, `keywords`, `url` FROM `events`
        WHERE 
    $where
        UNION
        SELECT `name`, LEFT(`description`, 300) as `description`, `keywords`, `url` FROM `jobs`   
        WHERE 
    $where
        "

    Comments on this post

    • dmittner agrees : Yep. WHERE clause is required per UNION component
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    120
    Rep Power
    2
    Is it not on every line I put arrows to each one on each select statement to show its there,

    PHP Code:
     $results 
        SELECT `name`, LEFT(`description`, 300) as `description`, `keywords`, `url` FROM `trailers`  
        ---> WHERE 
    $where 
        UNION 
        SELECT `name`, LEFT(`description`, 300) as `description`, `keywords`, `url` FROM `parts` 
        ---> WHERE 
    $where 
        UNION 
        SELECT `name`, LEFT(`description`, 300) as `description`, `keywords`, `url` FROM `events` 
        ---> WHERE 
    $where 
        UNION 
        SELECT `name`, LEFT(`description`, 300) as `description`, `keywords`, `url` FROM `jobs`    
        --->WHERE 
    $where 
        "

  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    101
    Rep Power
    27
    OK... your original post doesn't reflect that.

    So you have added the WHERE clauses to every part of your union query. Print the final query string before you execute the query - what does it look like? If the result set still isn't what you expect, take the query over to your favorite database utility and play with it until it does.
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    120
    Rep Power
    2
    Oh yeah lol sorry about that. ok ill try that out and post back here
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    120
    Rep Power
    2
    It only searches the keywords columns, i'm going to try something and then post back here to see if that works.
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    120
    Rep Power
    2
    OK I tried putting the 4 column names in the $where variable like this,

    $where .= "`name`, `description`, `keywords`, `url` LIKE '%$keyword%'";

    and it didn't work
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    101
    Rep Power
    27
    Well yeah, your WHERE clause only has one test in it, "`keywords` LIKE whatever". If you want to search by keywords OR other fields, you have to include other tests in the WHERE clause.

    So if I searched for "andy bobandy" and I wanted to find matches on both they keywords or description fields:

    WHERE (`keywords` like '%andy%' AND `keywords` like '%bobandy%') OR (`description` like '%andy%' AND `description` like '%bobandy%')
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    120
    Rep Power
    2
    Ok figured it out,

    $where .= "`name` LIKE '%$keyword%' OR `description` LIKE '%$keyword%' OR `keywords` LIKE '%$keyword%' OR `url` LIKE '%$keyword%'";

    Thanks for the help again!

IMN logo majestic logo threadwatch logo seochat tools logo