MySQL get query from query
I have the following MySQL query that gets me the meta_key
equal to _cnote_module_number
and the post_id
equal to $mod_id
.
But now how do i add onto that query after i get the post_id = '$mod_id'
to look at the result and find the meta_value
(which is a number) and than to look back into meta_key
equal to _cnote_module_number
and get the meta_value
thats greater than the meta_value
开发者_JAVA百科 from the first result.
$query = ( "SELECT * FROM postmeta WHERE meta_key = '_cnote_module_number' AND post_id = '$mod_id'" );
EDIT
I have 2 tables the first is postmeta, with..
-------------------------------------
post_id | meta_key | meta_value
-------------------------------------
10 | jason | 1
49 | cnote | 3
56 | cnote | 2
97 | cnote | 7
The second is usermeta with..
-----------
mod_id
-----------
10
49
67
I want to make sure that postmeta.meta_key = 'cnote'
and usermeta.mod_id
is equal to postmeta.post_id
. Than whichever ID was equivalent i want to get the postmeta.meta_value
and than find the first number greater than that value.
In this example i would like the result to be the 4th row from the postmeta
You can JOIN
a table to itself by giving it two different names. For example, the following would return all meta_values where the B
table's meta_value was greater than the A
table's meta_value:
SELECT B.meta_value
FROM postmeta A
JOIN postmeta B
ON ( B.meta_value > A.meta_value
AND A.meta_key = B.meta_key)
WHERE A.meta_key = 'cnote'
AND A.post_id = '$mod_id'
But for your specific question, we want the next meta_value. That's even easier; since you are only wanting to return one row, you can take the MIN of the meta_values from above:
SELECT MIN(B.meta_value)
FROM postmeta A
JOIN postmeta B
ON ( B.meta_value > A.meta_value
AND A.meta_key = B.meta_key)
WHERE A.meta_key = 'cnote'
AND A.post_id = '$mod_id'
If you want to get a query that returns all pairs of meta_values where A.meta_value immediately precedes B.meta_value, we can do that too; let me know.
精彩评论