MySQL widget search

red, blue, green, OR purple ...

[DrDoc]

I have four variables, containing "random" values... Now I want to search for matches in the database.

For example, the column "foo" in the table "bar" should match either "baz" or "widget". However, "baz3" or "widget39" would also be a match...

Is there a way I can search for all four variables at the same time? I tried using a regexp, but it didn't work...

This is what I tried:

SELECT foo FROM bar WHERE foo REGEXP "^($a¦$b¦$c¦$d)[0-9]*$"

What am I doing wrong?

[DrDoc]

Well, the query works now... It works as long as I'm using variables like in the example above, but not if I'm using something like $foo[0]... Anyone knows why that is? Shouldn't the variable be replaced before the query is submitted? So, it shouldn't matter then if I use $foo or $foo[4]... But, it does.

[jatar_k]

I have had similar problems to that, no idea idea why though, so I usually cat them together when making the string as opposed to putting them inside the double quotes.

[bcc1234]

...where colname like 'prefixstring%'

[DrDoc]

Well, I can't use LIKE, or cat them together, since the string must match one of the variables with no other characters other than tracing numbers...

[andreasfriedrich]

Try any of the following methods where the last one seems to be the nicest when you want to use all elements of $foo in the REGEXP [mysql.com].

mysql_query [php.net]( 
"SELECT [mysql.com] field §FROM§ table WHERE [mysql.com] field 
REGEXP [mysql.com] \"^({$foo[0]}¦{$foo[1]}¦{$foo[2]}¦{$foo[3]})[0-9]*$\""); 
# 
mysql_query [php.net](sprintf [php.net]( 
'SELECT [mysql.com] %s §FROM§ %s WHERE [mysql.com] %s 
REGEXP [mysql.com] "^(%s¦%s¦%s¦%s)[0-9]*$"', 
$field, $table, $field, $foo[0], $foo[1], $foo[2])); 
# 
mysql_query [php.net](sprintf [php.net]( 
'SELECT [mysql.com] %s §FROM§ %s WHERE [mysql.com] %s 
REGEXP [mysql.com] "^(%s)[0-9]*$"', 
$field, $table, $field, implode [php.net]('¦', $foo))); 

Andreas

[jatar_k]

sorry DrDoc, not very concise on my part. I meant that I have to cat the query string when building it before passing it to mysql_query.

$query = "SELECT foo FROM bar WHERE foo REGEXP \"^(" . $foo[0] . "¦" . $foo[1] . "¦" . $foo[2] . "¦" . $foo[3] . ")[0-9]*$\"";

something like that

[DrDoc]

That's the first thing I tried, but it didn't work either :(

Anyway, I just used separate variables ($fooa¦$foob¦$fooc¦$food) and that works just fine...

[andreasfriedrich]

Did you check why it did not work? What kind of SQL string did it produce?

$foo = array('a1','a2','a3','a4'); 
echo "<br>"; 
echo "SELECT foo FROM bar WHERE foo 
REGEXP \"^(" . $foo[0] . "¦" . $foo[1] . "¦" . $foo[2] . "¦" 
. $foo[3] . ")[0-9]*$\"";  
echo "<br>"; 
echo "SELECT foo FROM bar WHERE foo 
REGEXP \"^({$foo[0]}¦{$foo[1]}¦{$foo[2]}¦{$foo[3]})[0-9]*$\"";  
echo "<br>"; 
echo sprintf('SELECT %s FROM %s WHERE %s 
REGEXP "^(%s¦%s¦%s¦%s)[0-9]*$"', 'foo', 'bar', 'foo', $foo[0], 
$foo[1], $foo[2], $foo[3]);  
echo "<br>"; 
echo sprintf( 'SELECT %s FROM %s WHERE %s 
REGEXP "^(%s)[0-9]*$"', 'foo', 'bar', 'foo', implode('¦', $foo));  
echo "<br>"; 

all produce the same string:

SELECT foo FROM bar WHERE foo REGEXP "^(a1¦a2¦a3¦a4)[0-9]*$" 
SELECT foo FROM bar WHERE foo REGEXP "^(a1¦a2¦a3¦a4)[0-9]*$" 
SELECT foo FROM bar WHERE foo REGEXP "^(a1¦a2¦a3¦a4)[0-9]*$" 
SELECT foo FROM bar WHERE foo REGEXP "^(a1¦a2¦a3¦a4)[0-9]*$" 

It´d be interesting to know what caused these problems for you.

Andreas

[DrDoc]

It just didn't return any matches... But when I changed it to separate variables (and that's the only thing I changed) then I got the results I wanted.

I even tried to put the whole query in a string and echo'd it to make sure it looked like it should...