CODEWITHSUNDEEP
phpmysql
Mel
Mel

Reputation: 111

select where row contains string

I have a column named software_hardware from the table activities which could have multiple product inputs separated by a pipeline like INVENTORY| POS | or GPOS | ACCOUNTING |.

I have this query:

SELECT a.id, a.ticket_number, a.client, a.software_hardware, 
a.issues_concern, a.status, a.technical_programmer, a.date_added 
FROM activities a
WHERE a.client = '".$_POST['client']."' AND a.software_hardware LIKE '%".$arr[$i]."%'
ORDER BY date_added DESC

$arr = explode("|", $_POST['Soft_hard']);

When I select a client and a product, related info would be displayed on a div. Now my problem is, if I have POS and GPOS, I realized I couldn't use LIKE '%[input product name here]%'as this would return results that contains both POS and GPOS if I selected POS.

How can I fix this query to not display GPOS if I selected POS?

PS. I am aware of the issues with MySQL. It's what the company I'm in uses so I have no choice at the moment.

Upvotes: 2

Views: 56

Answers (2)

cdaiga
cdaiga

Reputation: 4939

Try this:

SELECT a.id, a.ticket_number, a.client, a.software_hardware, 
a.issues_concern, a.status, a.technical_programmer, a.date_added 
FROM activities a
WHERE a.client = '".$_POST['client']."' 
AND UPPER(a.software_hardware) LIKE '%".strtoupper($arr[$i])."%'
ORDER BY a.date_added DESC

Upvotes: 0

Nick
Nick

Reputation: 147166

You can use REGEXP:

a.software_hardware REGEXP '[[:<:]]" . $arr[$i] . "[[:>:]]'

the [[:<:]] and [[:>:]] markers stand for word boundaries. So POS cannot match GPOS as there would be no word boundary at the beginning.

Note you will need to trim($arr[$i]) if it has leading or trailing whitespace.

Upvotes: 3

Related Questions