Continuing the discussion from Incorrect search query result :
Many thanks for the alternative solution:
I was hoping for a solution which used IN without having to duplicate the AND statements.
Test Results
// Mittineague TEST START =======================
SELECT * FROM cities WHERE name LIKE %XXX%
OR (`cities`.`name` LIKE :variant_name_0
OR `cities`.`state_name` LIKE :variant_state_name_0)
OR (`cities`.`name` LIKE :variant_name_1
OR `cities`.`state_name` LIKE :variant_state_name_1)
OR (`cities`.`name` LIKE :variant_name_2
OR `cities`.`state_name` LIKE :variant_state_name_2)
OR (`cities`.`name` LIKE :variant_name_3
OR `cities`.`state_name` LIKE :variant_state_name_3)
// Mittineague TEST END =======================
// John_Betong:
$sql: SELECT *
FROM cities2
WHERE concat(name, " ", state_name) LIKE ?
AND concat(name, " ", state_name) LIKE ?
AND concat(name, " ", state_name) LIKE ?
AND concat(name, " ", state_name) LIKE ?
Params: array( %a%", "%b%", "%c%", "%d% )
We’ve seen code like this that passes an array to a query
$array = array("this", "that", "another");
$in_list = implode(",", $array);
$query = "SELECT field FROM table WHERE field IN ($in_list);
the query being equivalent to (i.e. array changed to a list)
$query = "SELECT field FROM table WHERE field IN ("this", "that", "another");
And we’ve seen code that uses wildcards in a query
$query = "SELECT field FROM table WHERE field LIKE "%h%";
But short of building a query that uses ORs
OR field LIKE "%his";
OR field LIKE "tha%";
OR field LIKE, "%nothe%";
I know of no way to pass an array of wildcarded strings to a MySQL WHERE
Impossible?
Maybe it could be done using a database other than MySQL?
1 Like
r937
February 13, 2016, 9:36am
2
[quote=“Mittineague, post:1, topic:215294, full:true”]Impossible?
[/quote]
pretty much, yeah
2 Likes
molona
February 13, 2016, 2:57pm
3
But even if it was possible… would it be worth it? It looks like it could take ages to run a query like that
lol,
yes, it wouldn’t be wise to comb a large database for every field containing any of the letters “d”, “o”, “n”, “t”.
Actually my examples are short but not closely representative.
The germ for this pondering was that the member that posted the topic this was branched from has a field like
“first middle last”
Users might search for any combination of two of the three, and the result should be found. So what is needed is.
“first middle%”, “first%last” and “%middle last”
Not so bad with only the three variations, And putting together an “OR _ OR _ OR _” isn’t too difficult.
And in this case a single “%input%input%” would work.
More that it’s just looking for an easier better way. But sometimes work is work after all.
1 Like
Mittineague:
But short of building a query that uses ORs
OR field LIKE “%his”;
OR field LIKE “tha%”;
OR field LIKE, “%nothe%”;
I know of no way to pass an array of wildcarded strings to a MySQL WHERE
Impossible?
You can use REGEXP:
SELECT field FROM table WHERE field REGEXP '(his$|^tha|nothe)'
However, regular expressions in MySQL are quite primitive, for example they will not work well with utf-8 character sets.
Postgresql has very good regular expression support and will work for utf-8 characters as well, so it is better suited for languages other than English or Latin in this case (however, much more limited in the choice of collations).
1 Like
This query nearly works, it is case insensitive but must have:
FULLTEXT index
complete words only
SELECT *
FROM cities2
WHERE MATCH (name, state_name)
AGAINST ('+sasony -halle' IN BOOLEAN MODE);
// more parameters
stands for NOT
[no operator] implies OR
system
Closed
May 15, 2016, 9:30am
7
This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.