LIKE Query With Spaces

PHP
1

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

$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

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:


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:


WHERE linkname LIKE '%red%' AND linkname LIKE '%widget%'
3

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

WHERE linkname LIKE '%productsplit%' AND linkname LIKE '%productsplit%'
4

Here is some sample code for splitting the string into words and forming the WHERE clause:


$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

Hi,

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

$linkname = 'red-widget';

When you say not to use $word should I be using $linkname ?

6

From URL you will need this:

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


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.