CODEWITHSUNDEEP
phpmysqljoin
WAUS
WAUS

Reputation: 63

Use result of query in a second query

I'm trying to use the result of one query as part of a WHERE in a second query. I'm not sure how to best approach this, any assistance is greatly appreciated:

First Query:

$result = mysql_query("SELECT `keyword` FROM `history` ORDER BY `last_update` ASC LIMIT 1 ");
while($row = mysql_fetch_array($result))
$keyword = $row['keyword'];

Second Query

$result = mysql_query("SELECT `id` FROM `data_store` WHERE `keyword`= [result from my first query] ORDER BY `id` DESC LIMIT 1");
while($row = mysql_fetch_array($result))
$id = $row['id'];

For clarification, keyword.data_store relates to keyword.history

Upvotes: 1

Views: 269

Answers (3)

Gaurav
Gaurav

Reputation: 28755

$result = mysql_query("SELECT `id` 
                       FROM `data_store` 
                       WHERE `keyword` = (
                                 SELECT `keyword` 
                                 FROM `history` 
                                 ORDER BY `last_update` ASC 
                                 LIMIT 1 )
                       ORDER BY `id` DESC LIMIT 1");

Upvotes: 0

denispyr
denispyr

Reputation: 1443

(Stripped out PHP aspects because it is irrelevant)

SELECT `id` FROM `data_store` WHERE 
  `keyword` IN (
    SELECT `keyword` FROM `history` ORDER BY `last_update` ASC LIMIT 1
  ) 
ORDER BY `id` DESC LIMIT 1

Upvotes: 0

Martin.
Martin.

Reputation: 10529

Use subquery

$result = mysql_query("
SELECT `id` 
FROM   `data_store` 
WHERE  `keyword` = (SELECT `keyword` 
                    FROM   `history` 
                    ORDER  BY `last_update` ASC 
                    LIMIT  1) 
ORDER  BY `id` DESC");
while($row = mysql_fetch_array($result))
$id = $row['id'];

Upvotes: 1

Related Questions