PHP, word

PHP
1

Say I have


$str = "foo, bar";

I want to make any words in $str, come out as:

‘foo’, ‘bar’

With the ’ at the beginning and end.

I’m stuck on this part as I can’t run my query without these.

2

Seems overkill, but this works.


printf(
    "'%s'",
    implode(
        "', '",
        array_map(
            'trim',
            explode(
                ',',
                'foo, bar'
            )
        )
    )
); # 'foo', 'bar'
3

Thank you, this seems to work - but now I’m having problems with the query I was using so I’m switching over to LIKE.

Basically I want to create a query, like this:


SELECT * FROM `table` WHERE `name` LIKE `%foo%` OR LIKE `%bar%`

It needs to work too if there are multiple words, for example:

foo, bar, foobar, barfoo

At the moment I’m doing this:


<?php
function searchReplace($str){
	$replace = str_replace(" ", ", ", $str);
	$replace = str_replace("the, ", "", $replace);
	$replace = str_replace("movie, ", "", $replace);
	//$replace = mysql_real_escape_string($replace);
	return $replace;
}

$str = searchReplace($_GET['q']);

$arr =  array($str);
$val = implode(',',$arr);

$query = implode("', '", array_map('trim', explode(',',$val)));
$query = "'" . $query . "'";
echo $query;
?>

Basically I want to split the words up that a user enters and then search for the words that have been split in the database with a LIKE query.

I’m using the function to replace common words like “the” or “movie” to avoid searching for results containing those words.

Thanks again

4

Can you provide some sample data?

You start with…then end up with…

2 or 3 examples should suffice, hopefully covering an edge case or two.

5

Basically, say a user searches for “apples and pears”

There are no results called “apples and pears”… but the search page splits up the words to apples, and, pears as above.

The query will then search for

LIKE ‘%apples%’, OR LIKE ‘%and%’, OR LIKE ‘%pears%’

and return:

Pears and Bananas
Apples and Tomatos
Fruit and Vegetables

6 

$query = 'apples and pears';

preg_match_all(
    '~(\\w+)~',
    $query,
    $words
);

$sql = 'SELECT * FROM table WHERE ';

foreach(array_shift($words) as $word){
    $sql .= sprintf(
        " field LIKE '%%%s%%' OR",
        $word #this needs escaping
    );
}

echo rtrim($sql, ' OR');
# SELECT * FROM table WHERE field LIKE '%apples%' OR field LIKE '%and%' OR field LIKE '%pears%'
7

Ya, that will be SLOW…

But if you really have to do it, your select should be something like:
SELECT x FROM y WHERE z REGEXP ‘regex’;

To generate the sql regex from the user input, you can just use a simple replace.
Replace that , with |: /\s*,\s*/|/g.

Read: http://dev.mysql.com/doc/refman/5.1/en/regexp.html

8

You’re a legend, thank you very much Anthony.

9

sprintf is commonly used for replacable variables.

For example:


$sql = sprintf(
    'SELECT * FROM `table` WHERE `name` LIKE "%s" OR LIKE "%s"',
    mysql_real_escape_string($foo),
    mysql_real_escape_string($bar)
);

I’ve just taken a look at the documentation for mysql_real_escape_string and they use the very same sprintf technique.

10

Did you investigate mysql’s FULLTEXT indexing and search capabilities?

A search for OR LIKE “%and%” is probably not what you want.

11

Is this reply directed to me?

In the case of my example, the sprint function will replace %s with a text string.

For example:


$foo = 'foo" OR "x"="x'; // SQL injection attempt
$sql = sprintf(
    'SELECT * FROM `table` WHERE `name` LIKE "%s"',
    mysql_real_escape_string($foo) // injection denied
);
// $sql is now SELECT * FROM `table` WHERE `name` LIKE "foo\\" OR \\"x\\"=\\"x"
12

No it wasn’t, it was directed at the OP, sorry for the Confucius.