CODEWITHSUNDEEP
mysqlsqldatabase
John Smith
John Smith

Reputation: 478

Using a value from result to avoid double MySQL query

I need to get the "title" field if the "content" field contains a certain phrase. With the title I can do the actual query to get the posts.

-- psuedo code --

$query1 ='SELECT `title` FROM `posts` WHERE `content` CONTAINS 'matchtest' 
ORDER BY `id` ASC, LIMIT 1';
$titlefromquery1;

$query2 = 'SELECT * FROM `posts` WHERE `title` = 'Reply to: ' . $titlefromquery1;
$repliesfromquery2;

The replies don't have the phrase to match fortunately the "titles" will always be "Reply to: $title" which is why both queries are needed it seems

Is there any way conditionally or otherwise to avoid a double query here?

Upvotes: 1

Views: 75

Answers (2)

seahawk
seahawk

Reputation: 1912

SELECT * FROM posts 
WHERE title= 
concat_ws('Reply to: ',
(SELECT title FROM posts WHERE <put_your_condition> ORDER BY id ASC LIMIT 1))

Upvotes: 1

vhu
vhu

Reputation: 12818

You can use temporary variables to achieve this:

SELECT @title:=`title` FROM `posts` WHERE `content` CONTAINS 'matchtest' ORDER BY `id` ASC, LIMIT 1;

SELECT * FROM `posts` WHERE `title` = CONCAT('Reply to: ' , @title);

Upvotes: 1

Related Questions