Help with PHP MYSQL Select Statment

I can't seem to grasp how I can select records when the records of one user span multiple rows.

Here is the schema of the table.

user_id  key          value
------------------------------------------
1        text         this is sample text
1        text_status  0
2        text         this is sample text
2        text_status  1

from the above table/row you can see that each user has info that has multiple rows. So in this case how do I select say "All the IDs, t开发者_如何学Goext value where text_status is "1"?

And to complicate it 1 step further, I need the email address of these accounts which is on another table. How can I write 1 select statement to pull in the email address as well? I know there is a JOIN statement for this but it's a bit complicated for me especially I can't even figure out the first part.

Added Note I must state that this table schema is a Wordpress default table wp_usermeta..

    SELECT t1.*
      FROM tbl t2
INNER JOIN tbl t1 ON t1.user_id = t2.user_id
                 AND t1.key = 'text'
     WHERE t2.key = 'text_status'
       AND t2.value = '1'

I think you've set up your table incorrectly. Make text_status and value exist within the same row.

The way it is right now, you would have to conduct two queries to get to your end result. Where as, the correct way needs only one.

This arbitrary key:value list scheme is alluring because of its flexibility. But it complicates queries obviously. Depending on the structure of your second table you could get away with:

SELECT key, value FROM user_table WHERE user_id=123
UNION ALL
SELECT 'email' as key, email as value FROM email_table WHERE user_id=123

But that pretty much only returns a list still, not a set of fields.

key and value looks wrong. SQL already gives you "key" (in the column name) and multiple "values" (in the values given per column in each row).

You've designed your table in a way that contravenes the way Database Management Systems are designed to work, which is leading to your problem. Read about database normalization.

Ideally your table would look something like this:

user_id  text_status   text
------------------------------------------
1        0             this is sample text
2        1             this is sample text

Then your query looks like:

SELECT `user_id`, `text` FROM `table` WHERE `text_status` = '1';

As your table stands now, you'll need something like (untested):

SELECT `table`.*
  FROM `table` LEFT JOIN
       (SELECT `user_id`
          FROM `table`
         WHERE `key` = "text_status"
           AND `value` = "1"
       ) AS `_` USING(`user_id`)
 WHERE `table`.`key` = "text"