-3

I created a search bar using LIKE '%$keyword%'.

My DB:

title Name
IOT Sara,Husna
management Mia,Sara

When i searched 'Sara' the result i get is:

Sara,Husna

IOT

I need the result to be :

Sara

IOT,management

I have also tried to do explode but it appear both name while it just need to display the name that is input in the search bar. I am confused.

*Note that i have to use comma separated value in the DB.

gugupio
  • 11
  • 1
    if the string is `Sara, Husna, Sarah, Peter` , then what do u want to display when u search Sara ? – Ken Lee Nov 28 '21 at 12:07
  • 1
    "_Note that i have to use comma separated value in the DB_" Why would that be? Makes things more complicated. – brombeer Nov 28 '21 at 12:10
  • 1
    you should take a look into https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad – nbk Nov 28 '21 at 12:14
  • It may be simple if you do not show the result from the name field, just from the title field. You can shot the searched string instead of the name field. – Shadow Nov 28 '21 at 12:22
  • 1
    The root of your problem really is the mistake of storing comma separated values in a single field in your database. Learn about database normalisation. You say you "have" to use comma-separated values, but don't explain why. There's generally always an alternative – ADyson Nov 28 '21 at 12:26

1 Answers1

2

Your search would be like this and using GROUP_CONCAT, if you are searching for one prosin.

Like the comments already said it is preferred to used normalized tables without comma delimited data see Is storing a delimited list in a database column really that bad?

The Name for that you are search you don't need in the query, if you don't want to, because you send it in your form via GET or POST, but you can add it in your search query as constant

Last take also a look at How can I prevent SQL injection in PHP? you want to use prepared statements with parameters when you handle dta that are entered

CREATE TABLE searbar (
  `title` VARCHAR(10),
  `Name` VARCHAR(10)
);

INSERT INTO searbar
  (`title`, `Name`)
VALUES
  ('IOT', 'Sara,Husna'),
  ('management', 'Mia,Sara');

SELECT GROUP_CONCAT(title) FROM searbar WHERE `Name` LIKE '%Sara%'

| GROUP_CONCAT(title) |
| :------------------ |
| IOT,management      |

db<>fiddle here

nbk
  • 45,398
  • 8
  • 30
  • 47