从同一个表中选择不同的结果

Question

I've the following structure in the post table (example):

id | id_author | content | date | ft
1  | 1         | hi!     | 2016 | 2
2  | 1         | hello!  | 2016 | 3
3  | 1         | welcome | 2016 | 1
4  | 1         | test!   | 2016 | 2

and I've the query:

SELECT id, id_author, content, date, ft FROM post where id_author = '$author' ORDER BY id DESC LIMIT 7

But, I need too, to select the posts with your respective ft with LIMIT 4. Like this:

SELECT id, id_author, content, date, ft FROM post where id_author = '$author' and ft = 1 ORDER BY id DESC LIMIT 4

SELECT id, id_author, content, date, ft FROM post where id_author = '$author' and ft = 2 ORDER BY id DESC LIMIT 4

SELECT id, id_author, content, date, ft FROM post where id_author = '$author' and ft = 3 ORDER BY id DESC LIMIT 4

I could to do the "filter" of ft with a foreach, for example:

foreach($query as $ex) {
    switch($ex["ft"]) {
        ...
    }
}

But, my first query need to have LIMIT 7 and the querys realtives to ft need to select the last 4 results from all posts.

How to do this without having to do multiples querys?

EDIT:

I need to show the last 7 posts (general) in one div, the last 4 posts with images (ft = 1) in another div, the last 4 posts with mentions (ft = 2) in another div and the last 4 posts with hashtags (ft = 3) in another div.

Answer 1

You could do this with the UNION operator.

SELECT
    'General' AS post_type,
    id,
    id_author,
    content,
    date,
    ft
FROM
    Post
WHERE
    id_author = '$author'
ORDER BY
    id DESC
LIMIT 7
UNION ALL
SELECT
    'Image' AS post_type,
    id,
    id_author,
    content,
    date,
    ft
FROM
    Post
WHERE
    id_author = '$author' AND
    ft = 1
ORDER BY
    id DESC
LIMIT 4
UNION ALL
SELECT
    'Mentions' AS post_type,
    id,
    id_author,
    content,
    date,
    ft
FROM
    Post
WHERE
    id_author = '$author' AND
    ft = 2
ORDER BY
    id DESC
LIMIT 4
UNION ALL
SELECT
    'Hashtags' AS post_type,
    id,
    id_author,
    content,
    date,
    ft
FROM
    Post
WHERE
    id_author = '$author' AND
    ft = 3
ORDER BY
    id DESC
LIMIT 4