Regular Expression mysql with AND operator

Question

I had create a search field and if i search "Spinach, Watermelon", it will explode the input by ",", and run MySQL search database. That means the SQL will run as

SELECT * FROM table WHERE 
(vegetable LIKE '%Spinach%' OR fruits LIKE '%Spinach%') AND (vegetable LIKE '%Watermelon%' OR fruits LIKE '%Watermelon%')

My database table data looks something like this :

id  Vegetable  fruits
----------------------
1   Spinach    Apple
2   Cucumber   Orange
3   Spinach    Watermelon

The result of each id only can come out once.

<php>
$keywords = trim($_REQUEST['keyword']);
$keywords = preg_replace("/,\s*/", "|", $keyword);
$where = "[[:<:]](" . $keywords . ")[[:>:]]";

$sql = "SELECT * FROM table ";
$sql .= "WHERE vegetable REGEXP '" . $where . "' OR fruits REGEXP '" . $where . "'";
$result = mysqli_query($conn, $sql);
while ($rs = mysqli_fetch_array($result)) 
{
    $vege = $rs["vegetable"];
    $fruits = $rs["fruits"];
}

</php>


<html>
<form method=post>
    <input type="text" class="form-control" placeholder="SEARCH..." value="<?=$keywords?>">
</form>
</html>

And what is your specific question you want to know about? As it stands, this an explanation of what you were doing and/or intended it to do, but there is no clear problem statement. — Tobias F.
– Tobias F., Commented Feb 19, 2019 at 9:21
I want to generate a mysql query like below with regular expression, SELECT * FROM table WHERE (vegetable LIKE '%Spinach%' OR fruits LIKE '%Spinach%') AND (vegetable LIKE '%Watermelon%' OR fruits LIKE '%Watermelon%') — Ichida
– Ichida, Commented Feb 19, 2019 at 9:28
Are you assuming the Asker does not specify whether 'Spinach' is a Fruit versus a Vegetable? And your table can only have exactly one fruit paired with exactly one vegetable? Both requirements seem strange. — Rick James
– Rick James, Commented Feb 19, 2019 at 17:12

Tim Biegeleisen · Accepted Answer · 2019-02-19 09:35:14Z

1

Here is the literal MySQL query using REGEXP:

SELECT *
FROM table
WHERE
    (vegetable REGEXP '[[:<:]]Spinach[[:>:]]' OR fruits REGEXP '[[:<:]]Spinach[[:>:]]') AND
    (vegetable REGEXP '[[:<:]]Watermelon[[:>:]]' OR fruits REGEXP '[[:<:]]Watermelon[[:>:]]');

answered Feb 19, 2019 at 9:35

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

3 Comments

Ichida Over a year ago

Should i still use the preg_replace? $keywords = preg_replace("/,\s*/", "|", $keyword);

Tim Biegeleisen Over a year ago

I don't know because this question does not manifest a need for that. Comparing one column against many OR possibilities would need my previous answer.

Ichida Over a year ago

Cause i want make the condition is based on what user input, for example if user input Spinach,Watermelon,Rice, then behind the WHERE mysql will add one more condition ---> AND ((vegetable REGEXP '[[:<:]]Rice[[:>:]]' OR fruits REGEXP '[[:<:]]Rice[[:>:]]')

Collectives™ on Stack Overflow

Regular Expression mysql with AND operator

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related