Problem fetching the most recent result of a group in a query in two tables with PHP and MySQLi

2

I am studying mysqli and PHP to migrate a personal blog, from PHP 5.6 to 7 (mysql to mysqli), the blog has a table with names and another with items related to names, dates and times, write a similar example.

Before, I made a query in table 1 and then, when I viewed the results (within the while), I put the codes for a second query, now in table 2, to show the most recent item of records related to each name. .. I was thinking of simplifying the codes with a single query in both tables, but I do not know if it will be possible ... because I always encounter problems by not showing the most recent item.

Table format examples (I simplified the tables for the studies):

tabela 1 (elementos)
id,ini,nome,obs
conteudo da tabela:
1 | 200 | Elemento 1 | Livre
2 | 220 | Elemento 2 | Ocupado
tabela 2 (registros)
idtp,initp,fretp,poltp,data,hora
conteudo da tabela:
01 | 200 | 12 | 2018-06-12 | 12:11:01
02 | 220 | 10 | 2018-06-11 | 14:12:42
03 | 200 | 17 | 2018-06-01 | 10:12:31
04 | 220 | 15 | 2018-06-11 | 14:12:42
05 | 200 | 11 | 2018-06-11 | 16:15:44
06 | 300 | 16 | 2018-05-11 | 17:11:11
07 | 220 | 19 | 2018-06-11 | 14:12:42
08 | 100 | 10 | 2018-05-01 | 11:12:11
09 | 220 | 17 | 2018-06-11 | 14:12:42
10 | 300 | 11 | 2018-06-10 | 16:05:55

The query should take the ini from table 1 and compare with the initp from table 2 and return an initp result from each group with the most recent date.

I tried this query:

"SELECT tabela1.*,tabela2.*
FROM elementos AS tabela1
INNER JOIN registros AS tabela2 ON tabela1.ini=tabela2.initp
GROUP BY tabela2.initp
ORDER BY tabela1.ini DESC, tabela2.data DESC, tabela2.hora DESC"

But in the result, the most recent item in the second table does not appear, I believe the first one in the group appears.

I tried using MAX in the query, but then the most recent date appears, however the other result data does not appear correctly (they are from another date).

"SELECT tabela1.*,tabela2.*
FROM elementos AS tabela1
INNER JOIN (SELECT initp,MAX(data) As data,hora FROM registros GROUP BY initp) tabela2 ON tabela1.ini=tabela2.initp
ORDER BY tabela1.ini DESC, tabela2.data DESC, tabela2.hora DESC"

Is it possible to do this or should I continue with two queries to the database? Thank you in advance!

    
asked by anonymous 15.09.2018 / 22:26

0 answers