Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 11 of 11
  1. #1
    Regular Coder
    Join Date
    Nov 2002
    Location
    Loughborough (UK)
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Syntax for LIKE ?Param

    Hi. I've recently moved all my inline SQL to be properly parameterised (rather than the rookie technique of "+ strParam +"), but as I am still a rookie, how do I use a parameter syntax in a LIKE statement? I.e. I was expecting it to look like this:

    SELECT * FROM catalogue_products WHERE (ProductTitle LIKE '%?ProductTitle%')

    ...but this yields no results. What am I doing wrong?

    Thanks,
    Chris

  • #2
    Regular Coder PremiumBlend's Avatar
    Join Date
    Apr 2006
    Location
    Marion, Iowa
    Posts
    201
    Thanks
    0
    Thanked 13 Times in 13 Posts
    SELECT * FROM catalogue_products WHERE (ProductTitle LIKE '%?ProductTitle%')
    This resolves to finding anything that matches ProductTitle to [anything before PLUS your parameter PLUS ProductTitle PLUS anything after].

    Have you tried removing the 'ProductTitle'?

    SELECT * FROM catalogue_products WHERE (ProductTitle LIKE '%?%')

    This might not be the logic you need at all, it just appears to be an oversight on your part possibly??
    My Website: DumpsterDoggy

  • #3
    Regular Coder
    Join Date
    Nov 2002
    Location
    Loughborough (UK)
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi. Thanks for your reply. Unless I am being really thick and missing something, the logic is correct for what I need. It's basically an online catalogue of books. If somebody was searching for books with the key word; "pig", I want it to pick up "Recent Advances in Pig Nutrition"... so I think I am right in saying I need to search %?%

    The trouble is that the syntax I have used doesn't return anything!

  • #4
    Regular Coder
    Join Date
    Jan 2007
    Posts
    213
    Thanks
    0
    Thanked 0 Times in 0 Posts
    ok here is a quick example

    PHP Code:
    <?php
        
    if (strlen($_POST['search']) > 0)$productsearch $_POST['search'];
        else 
    $productsearch $_GET['search'];
        
        
    $sql mysql_query("SELECT * FROM catalogue_products WHERE `ProductTitle` LIKE '%".$productsearch."%'";
        
    $numrows = @mysql_num_rows($sql);
        
        if (
    $numrows 0){
            echo 
    "Your search for ".$productsearch." returned ".$numrows." matches.<br />";
            while (
    $row mysql_fetch_array($sql)){
                
    ## display it all
            
    }
        } else {
            echo 
    "Your search for ".$productsearch." returned no results.";
        }
    ?>
    Of course the SQL syntax could also be written like this...
    PHP Code:
        $sql mysql_query("SELECT * FROM catalogue_products WHERE `ProductTitle` RLIKE '".$productsearch."'"
    hope that helps
    Matthew Bagley
    Paramiliar Design Studios
    Website Design | Website Development | Search Engine Optimisation (SEO)

  • #5
    Regular Coder
    Join Date
    Nov 2002
    Location
    Loughborough (UK)
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This works fine when I use inline SQL and just concat the variable, but I am trying to use proper MySQL Parameters. So the whole code looks like the following chunk. The problem seems to be where I am trying to search with a [LIKE %?Param%]... it just yields no results... doesn't even error!

    StringBuilder sb = new StringBuilder();

    sb.Append("SELECT DISTINCT");
    sb.Append(" ProductTitle,");
    sb.Append(" FROM catalogue_products");
    sb.Append(" WHERE (catalogue_products.Active=1)");

    if (strProductTitle != "")
    {
    sb.Append(" AND (catalogue_products.ProductTitle LIKE '%?ProductTitle%')");
    }



    MySqlCommand objCmd = new MySqlCommand();

    objCmd.CommandType = CommandType.Text;
    objCmd.CommandText = sb.ToString();



    // Add Parameters

    objCmd.Parameters.Add("ProductTitle", strProductTitle);
    objCmd.Parameters["ProductTitle"].Direction = ParameterDirection.Input;
    Last edited by christrinder; 02-08-2007 at 12:34 PM.

  • #6
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    The ? is incorrect, it is not used in LIKE.

    proper usage is:

    Code:
    select
    foo, 
    bar
    from yourtable
    where foo like '%searchterm%'
    the % can appear before or after your search term, or in both places.

    Note two things:

    1) words of less than 4 characters are discarded from search results

    2) if a word is common in the column you are searching, that is appears in 50% or more rows, it is ignored irrespective of the length of the word

    Finally, you should be testing your sql directly in the mysql client. once you know it is working correctly and returning the results you expect, then you tweak it for your application language; be it php, perl, coldfusion or other.

  • #7
    Regular Coder
    Join Date
    Nov 2002
    Location
    Loughborough (UK)
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for your reply. Using the search word inbetween '%%' is fine if you're using a simply string, but I'm trying to avoid SQL-Injection by using MySqlCommand.Parameters. These are attached to the MySqlCommand and referenced in the commandText using the prefix '?'

    Does anybody know how to use such a parameter in a LIKE statement, or even if it simply isn't possible?

    Thanks in advance for your help.

    Chris

  • #8
    Regular Coder
    Join Date
    Jan 2007
    Posts
    213
    Thanks
    0
    Thanked 0 Times in 0 Posts
    As i stated above you can use the RLIKE statement instead, that way you dont need to use the %%
    Matthew Bagley
    Paramiliar Design Studios
    Website Design | Website Development | Search Engine Optimisation (SEO)

  • #9
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Matthew, I don't think it was that clear. I myself skipped over it. I think a one sentence blurb on the use of RLIKE would have helped.

    Glad to know that can also be used.

  • #10
    Regular Coder
    Join Date
    Jan 2007
    Posts
    213
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by neomaximus2k View Post

    Of course the SQL syntax could also be written like this...
    PHP Code:
        $sql mysql_query("SELECT * FROM catalogue_products WHERE `ProductTitle` RLIKE '".$productsearch."'"
    hope that helps
    Yeah i didnt have much time on my hands that day to explain what RLIKE is and how it works also you could use REGEXP in MySQL but I myself have never used it so wouldn't know what to suggest

    RLIKE is a synonym for REGEXP
    REGEXP and RLIKE use the current character set when deciding the type of a character. The default is latin1 (cp1252 West European). Warning: These operators are not multi-byte safe.
    Matthew Bagley
    Paramiliar Design Studios
    Website Design | Website Development | Search Engine Optimisation (SEO)

  • #11
    Regular Coder
    Join Date
    Nov 2002
    Location
    Loughborough (UK)
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thumbs up

    Thank you all for your help!

    The RLIKE works a treat. I'm not yet sure how it works exactly, but it achieves the results, so I will investigate it a little further on the web.

    Thanks again!


  •  

    Posting Permissions

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