Add string before and after array values for php>mysql searching

Question

I have an html form with multiple checkboxes. I pass those to php...

The values will come in like "G,BW" for example ("BW,G" needs to match as well) in check.php, I need to take the values from $_GET and modify them for an sql query...

<?php
if(!empty($_GET['wireColor'])) {
    foreach($_GET['wireColor'] as $colors) {
    echo $colors; //make sure it's right, then comment out
    }
}

$colors = rtrim($colors, ','); //Get rid of trailing , - not working right

$wireSearch = '\'REGEXP \'.*(^|,).$wireColor.(,|$)\''; //Trying to add the sql bits to pass to the query.

Ideally to get this passed:

$colors_lookup_sql = "SELECT * FROM parts WHERE ( wire_colors REGEXP '.*(^|,)$wireSearch(,|$)' and wire_colors REGEXP '.*(^|,)$wireSearch(,|$)' );";

Here's how the query should look at the end:

SELECT * FROM parts WHERE ( wire_colors REGEXP '.*(^|,)G(,|$)' and wire_colors REGEXP '.*(^|,)BW(,|$)' );

I'm having a hard time getting the regex bits into the query.

UPDATE

Here's what I have now:

<?php
if(!empty($_GET['wireColor'])) {
    foreach($_GET['wireColor'] as $colors) {
        $wireSearch = ' REGEXP \'.*(^|,)' .$colors.'(,|$)\' AND ';
    }
}

$Search = rtrim($wireSearch, 'AND'); //Trying to trim the last "AND"
$colors_lookup_sql = "SELECT * FROM parts WHERE ( wire_colors $wireSearch% );";

Which gives me mostly what I need, but print/echo the results and I get:

$wireSearch ends up as: REGEXP '.*(^|,)G(,|$)' AND REGEXP '.*(^|,)BW(,|$)' AND Which is great - I just need to nuke the last "AND". The trim above replaces it with the second value instead though. Weird.

and $colors_lookup_sql ends up as: SELECT * FROM parts WHERE ( wire_colors REGEXP '.*(^|,)BW(,|$)' AND % );

BUt for some reason the first value in the array goes away, which I don't understand since it was present before the sql statement.

Naryl · Accepted Answer · 2013-07-03 06:16:46Z

1

I'm not sure about the REGEX inside the query since I haven't used it but:

$wireSearch = '\'REGEXP \'.*(^|,).$wireColor.(,|$)\'';

Here you have a problem, the variable $wireColor is inside a string, and you are using ' so anything inside is not read as a variable, it should be something like:

$wireSearch = '\'REGEXP \'.*(^|,)'.$wireColor.'(,|$)\'';

answered Jul 3, 2013 at 6:16

Naryl

1,8881 gold badge10 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

fyn Over a year ago

That's why I was escaping them - I'll give that a shot.

fyn Over a year ago

This worked well enough to get me a lot further.. See my updates.

fyn Over a year ago

I'm gonna call this one solved - even though it led me down a rabbit hole. Thanks!

Kirk Logan · Accepted Answer · 2013-07-03 07:16:24Z

0

I cant say I entirely understand how your data is being stored, and I havent worked with REGEX much myself, but perhaps something like this would be a bit easier to work with:

$wireSearch = explode(",", $_GET['wireColor']);
$query = "SELECT * FROM parts WHERE wire_colors LIKE '%$wireSearch[0]%' 
         AND wire_colors LIKE '%$wireSearch[1]%'";

Not sure if this helps but I thought id throw in the idea.

answered Jul 3, 2013 at 7:16

Kirk Logan

7832 gold badges9 silver badges24 bronze badges

Comments

Toto · Accepted Answer · 2013-07-03 09:40:10Z

0

I guess mysql regex supports word boundaries, how about:

wire_colors REGEX '\bBW\b' and wire_colors regex '\bW\b'

answered Jul 3, 2013 at 9:40

Toto

91.7k63 gold badges97 silver badges135 bronze badges

Collectives™ on Stack Overflow

Add string before and after array values for php>mysql searching

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related