CODEWITHSUNDEEP
phpmysqlconcatenation
Mac Taylor
Mac Taylor

Reputation: 5148

problem in showing correct mysql concatenation

i have two mysql tables which are linked together and i need to show my category list

table_stories

sid    ||   title   ||   time      ||  hometext  ||  associated
1      ||   test    ||  2010-07-19 ||  text----  ||  8-14-

table_topics

topicid     ||  topicname  ||  slug
1           ||  car        ||  car  
4           ||  wall       ||  wall  
8           ||  benz       ||  benz  
14          ||  ford       ||  ford

now if i use this query to fetch story list :

 LEFT JOIN table_topics AS nto ON 
 (CONCAT(' ',COALESCE(ns.associated,'-'),'-') LIKE CONCAT('%',nto.topicid,'-%'))

then output would show stories with topic id : 8 , 4 , 14

you see that it counts topic id 4 as similar as 14

please concider that i cant change mysql table structure and i should find a work around for this problem

Upvotes: 0

Views: 71

Answers (2)

OMG Ponies
OMG Ponies

Reputation: 332581

Use a combination of the FIND_IN_SET and REPLACE functions:

LEFT JOIN table_topics AS nto ON FIND_IN_SET(nto.topicid, REPLACE(ns.associated, '-', ','))

Upvotes: 2

Maxem
Maxem

Reputation: 2684

I'd do the following: Split associated on '-', check if the topic id is IN the resulting array.

Upvotes: 0

Related Questions