-2

I have the following table in my database.

CREATE TABLE `market_messages` (
  `pm_id` int(11) NOT NULL,
  `pm_item_index` int(11) DEFAULT NULL,
  `pm_item_id` varchar(11) COLLATE utf8_polish_ci NOT NULL,
  `pm_item_sender` varchar(30) COLLATE utf8_polish_ci DEFAULT NULL,
  `pm_item_client` varchar(30) COLLATE utf8_polish_ci NOT NULL,
  `pm_item_sender_id` varchar(30) COLLATE utf8_polish_ci DEFAULT NULL,
  `pm_item_client_id` varchar(30) COLLATE utf8_polish_ci DEFAULT NULL,
  `pm_item_name` varchar(80) COLLATE utf8_polish_ci DEFAULT NULL,
  `pm_sender` varchar(30) COLLATE utf8_polish_ci DEFAULT NULL,
  `pm_receiver` varchar(30) COLLATE utf8_polish_ci DEFAULT NULL,
  `pm_entry_start` timestamp NULL DEFAULT NULL,
  `pm_message` text COLLATE utf8_polish_ci DEFAULT NULL,
  `pm_index` int(11) DEFAULT NULL,
  `pm_type` varchar(10) COLLATE utf8_polish_ci DEFAULT NULL,
  `pm_user_offer` varchar(50) COLLATE utf8_polish_ci DEFAULT NULL,
  `pm_status` varchar(30) COLLATE utf8_polish_ci DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;

Now I'm trying to group messages and order in specific sequence on the list. I need to list comments based on last entry into m_entry_start column which type is timestamp.

Here is my query, but result is not good:

$sql = "SELECT * 
        FROM market_messages 
        WHERE (pm_item_client = '$user_name' 
                AND pm_item_client_id = '$user_id') 
        OR      (pm_item_sender = '$user_name' 
                AND pm_item_sender_id = '$user_id') 
        GROUP BY pm_item_id, pm_item_client_id, pm_item_sender_id 
        ORDER BY MAX(pm_entry_start) DESC;";

Result is not this what I have expected. Echo $fetch['m_entry_start']; doesn't give me right result.

I'm expecting max value of pm_entry_start timestamp from each group, but in results is taken probably first value. Additionally `pm_entry_start' is echoed with ago() function to show elapsed time. Ago function is converting timestamp in format 2021-10-01 10:00:19 to something like 3 minutes ago, or 1 day ago etc. This is example result:

$user_name ago(timestamp)
Adam 1 month ago
Eve 23 days ago
Terese 2 months ago
Mark 5 days ago
Monica 3 min ago

I expecting results in order based on timestamp in descending order so from the last entry to the first.

$user_name ago(timestamp)
Monica 3 min ago
Mark 5 days ago
Eve 23 days ago
Adam 1 month ago
Terese 2 months ago

How to write the good query? I have started programming in PHP last year. This project is my first with use of MySQL database. Any help appreciated.

Sylwester Bogusiak
  • 1
  • 2
  • Did you try using `MAX()` – RiggsFolly Nov 01 '21 at 16:47
  • 1
    if you have started you should look at [how to prevent sql injection in php](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) – nbk Nov 01 '21 at 16:50
  • Yes, I have tried to use SELECT MAX(pm_entry_start), ... FROM ... but this query don't give any result to display. I have read somewhere on Stack pages that MAX() is working only with INT type of data. Still don't know how to fetch max value of timestamp. – Sylwester Bogusiak Nov 01 '21 at 18:01
  • @SylwesterBogusiak please edit your question and add some data examples and your expected result – Ergest Basha Nov 01 '21 at 20:12
  • https://dev.mysql.com/doc/refman/8.0/en/example-maximum-column-group-row.html has some hints on how to do stuff like this. – CBroe Nov 02 '21 at 07:49
  • @ErgestBasha I have edited my question. Hopefully this help to solve my problem. – Sylwester Bogusiak Nov 02 '21 at 10:48
  • @CBroe OK. I will try to use this examples. Thanks. – Sylwester Bogusiak Nov 02 '21 at 10:54
  • @ErgestBasha ago(timestamp) function just print result from MySQL query and has no impact to sort result. When I use echo $fetch['m_entry_start'] instead of echo ago($fetch['m_entry_start']) this give the same order in result just time is printed in other form. I need to change MySQL query, and I hope this is possible to select MAX(timestamp) from group of messages. – Sylwester Bogusiak Nov 02 '21 at 14:10
  • @ErgestBasha You don't understand. Time in MySQL query is fetched in TIMESTAMP format not VARCHAR from table market_messages. Ago function has no impact to sort results, only can display Time it in other format. Forget about ago function and just in simplicity once echo $fetch['m_entry_start'] for each group. – Sylwester Bogusiak Nov 02 '21 at 14:39

1 Answers1

0

Finally I have found the solution: SELECT ..., MAX(pm_entry_start) AS pm_entry_start FROM ... was missing part of MySQL query. Before I have tried only: SELECT ..., MAX(pm_entry_start) FROM ...

Sylwester Bogusiak
  • 1
  • 2