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!