I have the following query, where records are currently printed by a specific category or topic, for example:
$section_category = 'news'; //noticias
Currently I have more than two topics or categories news, sports, technology
the same one that is in the column main_cover
of the same tablenews_sport
This is my query:
$ACTIVE = 1;
$LANGUAGE = 'es';
$limit_data = 10;
$stmt = $con->prepare("SELECT n.id_news_sport as id,
n.url as url,
n.cover_page as cover_page,
n.mini_title as mini_title,
n.mini_description as mini_description,
n.date_post as date_post,
n.main_cover as main_cover,
u.trade_brand as trade_brand
FROM news_sport n
LEFT JOIN users u ON n.id_author=u.id_user
WHERE n.language=? AND n.main_cover=? AND n.active=? ORDER BY n.id_news_sport DESC LIMIT ?");
$stmt->bind_param("ssii",$LANGUAGE,$section_category,$ACTIVE,$limit_data);
$stmt->execute();
$stmt->store_result();
$json = array();
$stmt->bind_result(
$json['id'],
$json['url'],
$json['cover_page'],
$json['mini_title'],
$json['mini_description'],
$json['date_post'],
$json['main_cover'],
$json['trade_brand']
);
while ($stmt->fetch()) {
echo json_encode($json, JSON_UNESCAPED_SLASHES | JSON_PRETTY_PRINT | JSON_FORCE_OBJECT);
}
I have tried to obtain 30 records from the same query, that is, 10 records of news
10 of sports
and 10 records of technology
add a GROUP BY n.main_cover
from what I have consulted but I do not get what I expect.
At the moment I am having these results at JSON
https://jsfiddle.net/k8xv6o9j/
And, what I want to achieve is the following, if you look at the main_cover
results , what I want is to print those results but 10 records of each one, for example:JSON
news, sports, technology
"articles":[
{
"id": 1009,
"url": "es/deportes/futbol/ecuador/ligapro/serie-a/1009/final-de-ida-de-ligapro-entre-independiente-del-valle-y-emelec-se-posterga-por-fuerte-lluvia",
"cover_page": "https://i.imgur.com/7jCfeo3.jpg",
"mini_title": "Final de ida de LigaPro entre Independiente del Valle y Emelec se posterga por fuerte lluvia",
"mini_description": "El partido fue reprogramado para las 20:00 de este domingo.",
"date_post": "2021-12-06 00:15:07",
"main_cover": "news",
"trade_brand": null
},
"id": 1009,
"url": "es/deportes/futbol/ecuador/ligapro/serie-a/1009/final-de-ida-de-ligapro-entre-independiente-del-valle-y-emelec-se-posterga-por-fuerte-lluvia",
"cover_page": "https://i.imgur.com/7jCfeo3.jpg",
"mini_title": "Final de ida de LigaPro entre Independiente del Valle y Emelec se posterga por fuerte lluvia",
"mini_description": "El partido fue reprogramado para las 20:00 de este domingo.",
"date_post": "2021-12-06 00:15:07",
"main_cover": "news",
"trade_brand": null
},
"id": 1009,
"url": "es/deportes/futbol/ecuador/ligapro/serie-a/1009/final-de-ida-de-ligapro-entre-independiente-del-valle-y-emelec-se-posterga-por-fuerte-lluvia",
"cover_page": "https://i.imgur.com/7jCfeo3.jpg",
"mini_title": "Final de ida de LigaPro entre Independiente del Valle y Emelec se posterga por fuerte lluvia",
"mini_description": "El partido fue reprogramado para las 20:00 de este domingo.",
"date_post": "2021-12-06 00:15:07",
"main_cover": "news",
"trade_brand": null
},
"id": 1009,
"url": "es/deportes/futbol/ecuador/ligapro/serie-a/1009/final-de-ida-de-ligapro-entre-independiente-del-valle-y-emelec-se-posterga-por-fuerte-lluvia",
"cover_page": "https://i.imgur.com/7jCfeo3.jpg",
"mini_title": "Final de ida de LigaPro entre Independiente del Valle y Emelec se posterga por fuerte lluvia",
"mini_description": "El partido fue reprogramado para las 20:00 de este domingo.",
"date_post": "2021-12-06 00:15:07",
"main_cover": "news",
"trade_brand": null
},
"id": 1009,
"url": "es/deportes/futbol/ecuador/ligapro/serie-a/1009/final-de-ida-de-ligapro-entre-independiente-del-valle-y-emelec-se-posterga-por-fuerte-lluvia",
"cover_page": "https://i.imgur.com/7jCfeo3.jpg",
"mini_title": "Final de ida de LigaPro entre Independiente del Valle y Emelec se posterga por fuerte lluvia",
"mini_description": "El partido fue reprogramado para las 20:00 de este domingo.",
"date_post": "2021-12-06 00:15:07",
"main_cover": "news",
"trade_brand": null
},
{
"id": 1008,
"url": "es/deportes/futbol/ecuador/ligapro/serie-a/1008/manta-se-aferra-a-la-serie-a-tras-golear-3-tantos-a-0-al-olmedo-de-riobamba-ya-descendido-a-la-serie-b-2022",
"cover_page": "https://i.imgur.com/mUj5k5w.jpg",
"mini_title": "Manta se aferra a la Serie A tras golear 3 tantos a 0 al Olmedo de Riobamba, ya descendido a la Serie B 2022.",
"mini_description": "El equipo manabita fue muy fuerte de local y sac\u00f3 3 puntos claves para escapar del descenso y se prende la pelea por la permanencia.",
"date_post": "2021-11-22 02:21:36",
"main_cover": "sports",
"trade_brand": null
},
{
"id": 1008,
"url": "es/deportes/futbol/ecuador/ligapro/serie-a/1008/manta-se-aferra-a-la-serie-a-tras-golear-3-tantos-a-0-al-olmedo-de-riobamba-ya-descendido-a-la-serie-b-2022",
"cover_page": "https://i.imgur.com/mUj5k5w.jpg",
"mini_title": "Manta se aferra a la Serie A tras golear 3 tantos a 0 al Olmedo de Riobamba, ya descendido a la Serie B 2022.",
"mini_description": "El equipo manabita fue muy fuerte de local y sac\u00f3 3 puntos claves para escapar del descenso y se prende la pelea por la permanencia.",
"date_post": "2021-11-22 02:21:36",
"main_cover": "sports",
"trade_brand": null
},
{
"id": 1008,
"url": "es/deportes/futbol/ecuador/ligapro/serie-a/1008/manta-se-aferra-a-la-serie-a-tras-golear-3-tantos-a-0-al-olmedo-de-riobamba-ya-descendido-a-la-serie-b-2022",
"cover_page": "https://i.imgur.com/mUj5k5w.jpg",
"mini_title": "Manta se aferra a la Serie A tras golear 3 tantos a 0 al Olmedo de Riobamba, ya descendido a la Serie B 2022.",
"mini_description": "El equipo manabita fue muy fuerte de local y sac\u00f3 3 puntos claves para escapar del descenso y se prende la pelea por la permanencia.",
"date_post": "2021-11-22 02:21:36",
"main_cover": "sports",
"trade_brand": null
},
{
"id": 1008,
"url": "es/deportes/futbol/ecuador/ligapro/serie-a/1008/manta-se-aferra-a-la-serie-a-tras-golear-3-tantos-a-0-al-olmedo-de-riobamba-ya-descendido-a-la-serie-b-2022",
"cover_page": "https://i.imgur.com/mUj5k5w.jpg",
"mini_title": "Manta se aferra a la Serie A tras golear 3 tantos a 0 al Olmedo de Riobamba, ya descendido a la Serie B 2022.",
"mini_description": "El equipo manabita fue muy fuerte de local y sac\u00f3 3 puntos claves para escapar del descenso y se prende la pelea por la permanencia.",
"date_post": "2021-11-22 02:21:36",
"main_cover": "sports",
"trade_brand": null
},
{
"id": 1008,
"url": "es/deportes/futbol/ecuador/ligapro/serie-a/1008/manta-se-aferra-a-la-serie-a-tras-golear-3-tantos-a-0-al-olmedo-de-riobamba-ya-descendido-a-la-serie-b-2022",
"cover_page": "https://i.imgur.com/mUj5k5w.jpg",
"mini_title": "Manta se aferra a la Serie A tras golear 3 tantos a 0 al Olmedo de Riobamba, ya descendido a la Serie B 2022.",
"mini_description": "El equipo manabita fue muy fuerte de local y sac\u00f3 3 puntos claves para escapar del descenso y se prende la pelea por la permanencia.",
"date_post": "2021-11-22 02:21:36",
"main_cover": "sports",
"trade_brand": null
},
{
"id": 1007,
"url": "es/deportes/futbol/ecuador/ligapro/serie-a/1007/ldu-de-quito-empato-con-orense-y-no-ira-a-la-libertadores-2022",
"cover_page": "https://i.imgur.com/I2geFn5.jpg",
"mini_title": "LDU de Quito empat\u00f3 con Orense y no ir\u00e1 a la Libertadores 2022",
"mini_description": "El mal rendimiento del equipo albo lo deja fuera de Libertadores tendr\u00e1n que conformarse con disputar la Copa Sudamericana 2022.",
"date_post": "2021-11-22 01:17:35",
"main_cover": "technology",
"trade_brand": null
},
{
"id": 1007,
"url": "es/deportes/futbol/ecuador/ligapro/serie-a/1007/ldu-de-quito-empato-con-orense-y-no-ira-a-la-libertadores-2022",
"cover_page": "https://i.imgur.com/I2geFn5.jpg",
"mini_title": "LDU de Quito empat\u00f3 con Orense y no ir\u00e1 a la Libertadores 2022",
"mini_description": "El mal rendimiento del equipo albo lo deja fuera de Libertadores tendr\u00e1n que conformarse con disputar la Copa Sudamericana 2022.",
"date_post": "2021-11-22 01:17:35",
"main_cover": "technology",
"trade_brand": null
},
{
"id": 1007,
"url": "es/deportes/futbol/ecuador/ligapro/serie-a/1007/ldu-de-quito-empato-con-orense-y-no-ira-a-la-libertadores-2022",
"cover_page": "https://i.imgur.com/I2geFn5.jpg",
"mini_title": "LDU de Quito empat\u00f3 con Orense y no ir\u00e1 a la Libertadores 2022",
"mini_description": "El mal rendimiento del equipo albo lo deja fuera de Libertadores tendr\u00e1n que conformarse con disputar la Copa Sudamericana 2022.",
"date_post": "2021-11-22 01:17:35",
"main_cover": "technology",
"trade_brand": null
},
{
"id": 1007,
"url": "es/deportes/futbol/ecuador/ligapro/serie-a/1007/ldu-de-quito-empato-con-orense-y-no-ira-a-la-libertadores-2022",
"cover_page": "https://i.imgur.com/I2geFn5.jpg",
"mini_title": "LDU de Quito empat\u00f3 con Orense y no ir\u00e1 a la Libertadores 2022",
"mini_description": "El mal rendimiento del equipo albo lo deja fuera de Libertadores tendr\u00e1n que conformarse con disputar la Copa Sudamericana 2022.",
"date_post": "2021-11-22 01:17:35",
"main_cover": "technology",
"trade_brand": null
},
{
"id": 1007,
"url": "es/deportes/futbol/ecuador/ligapro/serie-a/1007/ldu-de-quito-empato-con-orense-y-no-ira-a-la-libertadores-2022",
"cover_page": "https://i.imgur.com/I2geFn5.jpg",
"mini_title": "LDU de Quito empat\u00f3 con Orense y no ir\u00e1 a la Libertadores 2022",
"mini_description": "El mal rendimiento del equipo albo lo deja fuera de Libertadores tendr\u00e1n que conformarse con disputar la Copa Sudamericana 2022.",
"date_post": "2021-11-22 01:17:35",
"main_cover": "technology",
"trade_brand": null
},
]
Note: I have repeated the 5 records
JSON
of each one, so as not to make it extensive and so that you can see the results that I want to be able to print, it does not matter if they are not in order as long as there are 10 of each theme or category of:news, spors, technology
How can I get the desired results?
According to some examples that have been exposed to me, I have practiced the following:
SELECT n.id_news_sport as id,
n.url as url,
n.cover_page as cover_page,
n.mini_title as mini_title,
n.mini_description as mini_description,
n.date_post as date_post,
n.main_cover as main_cover,
u.trade_brand as trade_brand
FROM news_sport AS n
LEFT JOIN users u ON n.id_author=u.id_user
CROSS JOIN (
SELECT n1.main_cover
FROM news_sport n1
LIMIT 10
) AS ou
WHERE n.active = '1' GROUP BY n.main_cover DESC LIMIT 10
I still don't know how it works but I still don't get the desired results, just this:
There are other examples, but it is not compatible with version 5 ofMYSQL
La idea general es:
Assuming you want to display the 10 most recent by category:
a) With the option equivalent to
row_num()
:@ultimoGrupo
The grouping is done by first ordering by category and controlling the change of category with the user variable . The ordering is done at the sub-query level. The limit is set in the main query.b) With
find_in_set()
:Grouping is done with
GROUP BY
and sorting is done in thegroup_concat()
. The limit is set in the conditions of the JOIN.c) Call the statement prepared for each category:
Both the grouping and the ordering and the limitation is for each
execute()
of the prepared statement.You could do it with a UNION of two SELECT , one for each main_cover , but that would leave you without it being dynamic.
The other could do a subquery where you generate a counter field depending on its main_cover, you can do it with ROW_NUMBER() and with that select those whose column number is less than 10, more or less like this:
and it would be a matter of you completing with the selected fields and the WHERE within the subquery, I think.
I see that you put some data to be able to test, I have read the query and except for the fact that I do not have the relationship with users to do the ELFT join, it brings me this result, taking into account only 3 for the number of records that you give
Searching for MySQL versions prior to 8 I found this tutorial to emulate the ROW_NUMBER() : https://www.mysqltutorial.org/mysql-row_number/ Following it I defined the QUERY as follows:
Putting that only bring only as proof. and it gives me the following result:
Hope this works for you.
What you mention can be done with Cross Apply.
If I have not misunderstood, in the news_sport table you have all the news and you want to show 10 records of each category crossing against the same table and main_cover column.
The first thing I would recommend is that you separate the categories into an individual table, but using your current model, I can get the list of unique categories by group by of the news_sport table by the main_cover column and then the cross against the news of each of those categories through a cross apply:
If the previous query works, for each category it will show up to a maximum of 10 notifications. I don't have a mysql installed and I haven't been able to test the query, but at least in SQL Server I'm pretty sure it works.
Un saludo
Dado que tienes una versión antigua de MySQL, te puede servir esta consulta.
La idea es agrupar las noticias por autores en una sub-consulta, y luego usar
FIND_IN_SET()
para el límite entreN
yM
(fuente).Aquí te dejo un Fiddle (por cierto se agradece si nos ahorras el trabajo inicial, creando un fiddle con un Ejemplo Mínimo y Verificable parecido a este, sólo con las tablas columnas esenciales):
Schema (MySQL v5.5)
Query #1
View on DB Fiddle