...

View Full Version : Syntax for LIKE ?Param



christrinder
02-07-2007, 10:29 PM
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

PremiumBlend
02-07-2007, 11:34 PM
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??:confused:

christrinder
02-08-2007, 10:21 AM
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! :mad:

neomaximus2k
02-08-2007, 01:11 PM
ok here is a quick example


<?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...

$sql = mysql_query("SELECT * FROM catalogue_products WHERE `ProductTitle` RLIKE '".$productsearch."'";

hope that helps

christrinder
02-08-2007, 01:27 PM
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;

guelphdad
02-08-2007, 03:07 PM
The ? is incorrect, it is not used in LIKE.

proper usage is:


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.

christrinder
02-13-2007, 10:18 PM
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

neomaximus2k
02-13-2007, 10:33 PM
As i stated above you can use the RLIKE statement instead, that way you dont need to use the %%

guelphdad
02-13-2007, 10:42 PM
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. :)

neomaximus2k
02-13-2007, 10:57 PM
Of course the SQL syntax could also be written like this...

$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.

christrinder
02-14-2007, 09:34 PM
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!



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum