-2

I have a home search table with have a city column which can have multiple cities like "Avignon, France|Nantes, France|". Addresses are "|" separated. There's a other Ads table where home ads are stored and there's also a city column which can have only one city like "Avignon, France".

Now I have to search matching home searches which matched with ads city .

How I can query home searches ???

Currently using home.city LIKE "%' . $ad->city. '%". Which not return the 100 % correct results . I need 100% correct results .

Problem is when we match home searches for "Arbonne, France"

It display ads for "Narbonne, France" which is another city

Shadow
  • 32,277
  • 10
  • 49
  • 61
Mohammad Siddiqie
  • 1
  • 4
  • When you need an exact match, why do you use the % sign within your like? Why do you use like at all instead of just use the search home.city = 'something'? – Jonas Metzler May 06 '22 at 11:25
  • home.city are the string of multiple addresses and ad.city is a single address . home.city can be like 'Narbonne, France|Nice, France' and ad,city will be like "Nice, France". Means If I user the "=" operator that will be 'Narbonne, France|Nice, France'= "Nice,France" which is not correct – Mohammad Siddiqie May 06 '22 at 11:29
  • What happens if you skip the leading `%`? – jarlh May 06 '22 at 11:36
  • I would suggest as first step making a view from the table that has the | delimited column and make a row for each entry. if you google comma separated to rows there are several techniques to do this. then you can do an exact match on your view. never store a list as a string in a table. it causes all kinds of problems as you are discovering – Bryan Dellinger May 06 '22 at 11:39
  • @BryanDellinger Can you suggest me a example ? – Mohammad Siddiqie May 06 '22 at 11:43
  • https://forums.mysql.com/read.php?10,635524,635529 – Bryan Dellinger May 06 '22 at 11:44
  • So, your problem comes from breaking the normalisation rules when you designed the database. The best solution would be to normalise your data structure and do not let multiple cities stored in home.city (to be exact, city and state should be split into two fields). You could use exact matching which in turn could utilise indexes for faster lookup. See answers to https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad SO question why what you are doing is really bad! – Shadow May 06 '22 at 12:20

0 Answers0