SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Hybrid View

  1. #1
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    730
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    LIKE Query With Spaces

    Hi,

    I have a query which works fine for 2 keywords next to each other. For example "red-widget" will display all "red widgets" but it will not display "red-large-widgets"

    Can anyone advise how I can query the keywords "red widget" whilst still display both "red widgets" and "red large widgets".


    Code:
    $query = "SELECT name, linkname, product_id, price, discount, image_link
    									FROM furniture_groups a
    									INNER JOIN productdbase b
    									ON a.id = b.product_id
    									WHERE linkname LIKE '%$linkname%'";

  2. #2
    SitePoint Guru bronze trophy
    Join Date
    Dec 2003
    Location
    Poland
    Posts
    925
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    You'll have to search for each word separately then. First, split your search string into words (for example using preg_split() ) and then use php to create the sql WHERE part so that the db will search for all the words seaprately.

    So the WHERE part would have format like this:
    Code:
    WHERE linkname LIKE '%$word1%' AND linkname LIKE '%$word2%' AND linkname LIKE '%$word3%' AND ...
    In your case of searching for "red widget" it would be like this:
    Code:
    WHERE linkname LIKE '%red%' AND linkname LIKE '%widget%'

  3. #3
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    730
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Lemon Juice View Post
    You'll have to search for each word separately then. First, split your search string into words (for example using preg_split() ) and then use php to create the sql WHERE part so that the db will search for all the words seaprately.
    I am using a query from a link for example: product.php?linkname=red-widget or product.php?linkname=green-widget

    I have looked up pregsplit but I dont fully understand it. Does it still refer product as product

    Code:
    WHERE linkname LIKE '%productsplit%' AND linkname LIKE '%productsplit%'

  4. #4
    SitePoint Guru bronze trophy
    Join Date
    Dec 2003
    Location
    Poland
    Posts
    925
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    Here is some sample code for splitting the string into words and forming the WHERE clause:

    PHP Code:
    $linkname 'red-widget';

    // word delimiter can be a whitespace character (space, tab, newline), comma, period, plus or minus
    $words preg_split('/[\s,.+-]+/'$linkname);

    $whereParts = array();

    foreach (
    $words as $word) {
        if (
    $word != '') {
            
    $whereParts[] = "linkname LIKE '%$word%'";
        }
    }

    $where implode(" AND "$whereParts);
    echo 
    $where
    Just rember not to put $word into the string like I did in the simplified example above. For security reasons you will need to use mysqli_escape_string() or PDO::quote().

  5. #5
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    730
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    With this part do I not need this as I be taking the query from the URL.

    Code:
    $linkname = 'red-widget';
    When you say not to use $word should I be using $linkname ?

  6. #6
    SitePoint Guru bronze trophy
    Join Date
    Dec 2003
    Location
    Poland
    Posts
    925
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    From URL you will need this:
    PHP Code:
    $linkname $_GET['linkname']; 
    I said don't use $word without escaping because your site will be vulnerable to SQL injection. So use something like this:
    PHP Code:
    foreach ($words as $word) {
        if (
    $word != '') {
            
    $word mysqli_escape_string($word);
            
    $whereParts[] = "linkname LIKE '%$word%'";
        }

    You may also need to change mysqli_escape_string to a different function depending on the mysql extension you are using and whether you are connecting using procedural or object-oriented style. The example above will work for mysqli procedural style.


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
  •