PHP MySQL query - Show row only once

Question

I have a table similar to this

id    message    state        timestamp
1     abcd       Heartbeat    1970-01-01 01:00
2     efgh       Start        1970-01-01 02:00
3     sdcvsd     Stop         1970-01-01 02:30
4     efgh       Start        1970-01-01 03:00
5     sdcvsd     Stop         1970-01-01 03:30
6     dsdfsd     Heartbeat    1970-01-01 04:10
7     sdcsc      Heartbeat    1970-01-01 04:20
8     sewwdf     Heartbeat    1970-01-01 04:30

What I would like to do is query this table in PHP and show the rows, but only show the latest row of Heartbeat, because it is very repetitive and I only need to know when the last one happened. All other states will echo even if they are repetitive. So the output would be (newest at the top):

8 sewwdf     Heartbeat    1970-01-01 04:30
5 sdcvsd     Stop         1970-01-01 03:30
4 efgh       Start        1970-01-01 03:00
3 sdcvsd     Stop         1970-01-01 02:30
2 efgh       Start        1970-01-01 02:00

I have only been able to leave it out with

mysqli_query($con, " SELECT * FROM msg WHERE state <> 'Heartbeat' ORDER BY id DESC LIMIT 16 " );

I don't think I can use GROUP BY or DISTINCT for more than one state.

How can I query only the latest Heartbeat state without effecting other states or remove the older ones in the PHP echo?

mysqli_query($con, " SELECT * FROM msg GROUP BY id ORDER BY id DESC LIMIT 16" ); try this — Death-is-the-real-truth
– Death-is-the-real-truth, Commented Aug 5, 2017 at 4:36
SELECT * FROM msg GROUP BY(state) HAVING timestamp = MAX(timestamp) ORDER BY id DESC LIMIT 16 — JYoThI
– JYoThI, Commented Aug 5, 2017 at 5:13

Лев Макаренко · Accepted Answer · 2017-08-05 05:36:02Z

2

This also works:

select * from
`msg`
where
`state` = 'Heartbeat'
and
`id` =  
(
select
`id`
from
`msg`
where
`state` = 'Heartbeat'
order by `timestamp` desc
limit 1
)

union

select * from
`msg`
where
`state` <> 'Heartbeat'

order by `timestamp` desc

edited Aug 5, 2017 at 5:36

answered Aug 5, 2017 at 5:18

Лев Макаренко

3342 silver badges5 bronze badges

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

2 Comments

Лев Макаренко Over a year ago

These id's are real, do u want to enumerate result rows ?

Fid Over a year ago

SELECT * FROM msg WHERE message = '$foo' AND state <> 'Heartbeat' UNION SELECT * FROM msg WHERE state = 'Heartbeat' AND id = (SELECT id FROM msg WHERE state = 'Heartbeat' ORDER BY id DESC LIMIT 1) ORDER BY id DESC LIMIT 16

Chandra Kumar · Accepted Answer · 2017-08-05 04:56:23Z

0

Try this:

mysqli_query($con, "SELECT * FROM msg GROUP BY(state) ORDER BY id DESC LIMIT 16" );

edited Aug 5, 2017 at 4:56

answered Aug 5, 2017 at 4:39

Chandra Kumar

4,2131 gold badge19 silver badges26 bronze badges

2 Comments

Fid Over a year ago

This shows one row of every state. I need all states ungrouped, except 'Heartbeat' state, which I need the latest to be shown.

Fid Over a year ago

No, this groups each state. I do not want that. Please read the question carefully and see example.

Suraj Rao · Accepted Answer · 2017-08-05 04:56:53Z

0

Your query is:

mysqli_query($con, " SELECT * FROM msg WHERE state <> 'Heartbeat' ORDER BY id DESC LIMIT 16 " );

I think It should be :

mysqli_query($con, " SELECT * FROM msg WHERE state="Heartbeat" ORDER BY id DESC LIMIT 16 " );

You can see my attached picture, What happen in their? Thank You.

edited Aug 5, 2017 at 4:56

Suraj Rao

29.7k11 gold badges96 silver badges104 bronze badges

answered Aug 5, 2017 at 4:52

user7824421

1 Comment

Fid Over a year ago

No, this only outputs the one state.

Arun pandian M · Accepted Answer · 2017-08-05 05:06:02Z

0

Try this Query :

SELECT * FROM `msg` GROUP BY `state` ORDER BY `id` DESC,`Timestamp` DESC LIMIT 16

it will work

answered Aug 5, 2017 at 5:06

Arun pandian M

89210 silver badges17 bronze badges

2 Comments

Fid Over a year ago

No, this groups each state. I do not want that.

Arun pandian M Over a year ago

so you just want state with heartbeat ?

Mahipal Patel · Accepted Answer · 2017-08-05 05:40:28Z

0

you can try this also

select * from `test2` where `state` != 'Heartbeat' OR `id` = (select `id` from `test2` where `state` = 'Heartbeat' order by `date` desc limit 1 )

if need order with date

select * from `test2` where `state` != 'Heartbeat' OR `id` = (select `id` from `test2` where `state` = 'Heartbeat' order by `date` desc limit 1 ) ORDER BY date DESC

answered Aug 5, 2017 at 5:40

Mahipal Patel

5433 silver badges15 bronze badges

Collectives™ on Stack Overflow

PHP MySQL query - Show row only once

5 Answers 5

2 Comments

2 Comments

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

2 Comments

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related