Using a value from result to avoid double MySQL query

Question

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?

I think, there is no other way. But you can change your system to INT. So that: You have posts so you can group replies and mains by parent_id parameter. with that you can group parent_id's and fetch with one query — Muhammet Arslan
– Muhammet Arslan, Commented Aug 26, 2015 at 4:55

seahawk · Accepted Answer · 2015-08-26 05:25:40Z

1

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

answered Aug 26, 2015 at 5:25

seahawk

1,92213 silver badges18 bronze badges

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

Comments

vhu · Accepted Answer · 2015-08-26 05:17:23Z

1

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);

answered Aug 26, 2015 at 5:17

vhu

12.9k11 gold badges42 silver badges50 bronze badges

Collectives™ on Stack Overflow

Using a value from result to avoid double MySQL query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related