CakePHP problem with MySQL query

Question

I have two tables:

Clients: id, name Texts: id, text

Field text in Texts table can contain names of clients. I would like to find for each text, clients whose names are contained in text field. For example:

CLIENTS
id | name
1  | John Smith
2  | Mark Jackson
3  | Ann Boo

TEXTS
id | text
1  | John Smith and Ann Boo are my best friends.
2  | I really don't like Mark Jackson and John Smith.

The result should be:

RESULT
Text.id | Client.id | Client.name
1       | 1         | John Smith
1       | 3         | Ann Boo
2       | 1         | John Smith
2       | 2         | Mark Jackson

I hope you will help me :) I want to know what parameters should find() function have or just mysql query

If going this route you'll end up having to search for each single word in the text field. It'd probably make more sense to redesign how you store the relations to clients from the texts table. Think of how you can tag users in a facebook status, if you've used that. The user has to specify that it's a link to another user. — Stefan H Singer
– Stefan H Singer, Commented Jun 29, 2011 at 10:21
I've tried to do this in loop. Foreach text SELECT * FROM clients WHERE %name% LIKE $text['text'] but can't do this with cakephp. :/ — latata
– latata, Commented Jun 29, 2011 at 10:29

t-clausen.dk · Accepted Answer · 2011-06-29 10:32:22Z

1

DECLARE @clients TABLE (ID int IDENTITY(1,1), name VARCHAR(20))
INSERT @clients (name)
SELECT 'John Smith' UNION ALL SELECT 'Mark Jackson' UNION ALL SELECT 'Ann Boo'

DECLARE @texts TABLE (ID int IDENTITY(1,1), [text] VARCHAR(200))

INSERT @texts ([text]) VALUES ('John Smith and Ann Boo are my best friends.')
INSERT @texts ([text]) VALUES ('I really don''t like Mark Jackson and John Smith.')

SELECT T.id  [Text.id], C.id [Client.id], C.name [Client.name] 
FROM @CLIENTS C 
JOIN @TEXTS T 
ON T.[text] LIKE '%' + C.NAME + '%'

RESULT

Text.id     Client.id   Client.name
----------- ----------- --------------------
1           1           John Smith
1           3           Ann Boo
2           1           John Smith
2           2           Mark Jackson

answered Jun 29, 2011 at 10:32

t-clausen.dk

44.5k12 gold badges61 silver badges92 bronze badges

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

2 Comments

latata Over a year ago

'%' + C.NAME + '%' is it correct in MySQL cause it doesn't work for me. :(

latata Over a year ago

solution is: CONCAT('%', C.NAME, '%')

Collectives™ on Stack Overflow

CakePHP problem with MySQL query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related