SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Hybrid View

  1. #1
    SitePoint Member
    Join Date
    Oct 2011
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    database split string search problem

    Hi there,

    Having a lot of trouble with this and PHP isn't my strongest area!

    When I search my database it only returns specific sentences like "problems with my internet"

    When I search something like "internet problems" I'd like for it to show the same result as above but right now it returns nothing.

    How would I go about putting a split string in there?

    so I can have my keywords like "internet, problems, with, my," etc.

    PHP Code:
        public function getAll ($keywords NULL)
        {
            if (
    $neskeywords == NULL) {
                
    $sql 'SELECT * FROM nes';
            } else {
                
    $sql 'SELECT * FROM nes WHERE keywords LIKE \'%' mysql_escape_string($keywords) . '%\'';
            }
            
    $stmt $this->_pdo->query($sql);
            
    $obj $stmt->fetchALL(PDO::FETCH_CLASS'SearchClass');
            foreach (
    $obj as $item) {
                
    $item->id = (int) $item->id;
                
    $ret[] = $item;
            }
            return 
    $ret;
        } 
    Thanks for your help

  2. #2
    SitePoint Addict sdleihssirhc's Avatar
    Join Date
    Feb 2009
    Posts
    387
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    You have to put every keyword in its own "like" statement:

    PHP Code:
    public function getAll($keywords NULL) {
        
    $sql 'SELECT * FROM nes';
        if (
    $keywords) {
            
    $sql .= 'WHERE keywords LIKE "%'.
                
    implode('%" AND keywords LIKE "%'$keywords).'%"';
        }
        
    /* process the SQL query here */

    Note that the keywords aren't being sanitized in the above code. However you decide to do it, the principle stays the same: put each keyword in its own "like" section.
    I'm the web overlord for Graphic Business Systems

  3. #3
    SitePoint Member
    Join Date
    Oct 2011
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks, I'm still struggling with it, I just can't seem to figure out what to change here to make it work:

    $stmt = $this->_pdo->query($sql);
    $obj = $stmt->fetchALL(PDO::FETCH_CLASS, 'SearchClass');
    foreach ($obj as $item) {
    $item->id = (int) $item->id;
    $ret[] = $item;
    }
    return $ret;

    Do you have any ideas?

  4. #4
    SitePoint Addict sdleihssirhc's Avatar
    Join Date
    Feb 2009
    Posts
    387
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    It looks like you have your own class that you're using, so it's hard to say for certain, but once you have the SQL string built (and remember, my version doesn't sanitize input), then you should just be able to leave everything else the same. Execute the query, get the results, etc.
    I'm the web overlord for Graphic Business Systems

  5. #5
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    As long as your mysql table is not Innodb you can create a FULLTEXT search : mysql search fulltext.

    This can be very useful if you want to rank results by the proximity of the words "internet" and "problem".

    There are a few gotchas:

    Make sure you test with words longer than 3 chars (unless you change a setting).

    Make sure you have plenty of test data in your table. Any resultset returning more than 50% of the total rows in your table is deemed to be a failed search...

    I had some success by switching between keyword searches (using LIKE) and FULLTEXT searches, depending on the size of the words in a phrase.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •