I have the HIGHLIGHT field (yes) (no) I would like to display the results by removing the first 4 results HIGHLIGHT (yes) and display the rest of the records both the (yes) from the 4th and the not
I tried to use LIMIT OFFSET more did not work
SELECT * FROM noticias WHERE status='Publicado' ORDER BY idnoticia DESC LIMIT 6,4
Any alternative?
Use the following subconsulta :
SELECT *
FROM noticias n
WHERE id NOT IN (
SELECT * FROM (
SELECT id
FROM noticias
WHERE destacar = 'Sim'
ORDER BY id LIMIT 4
) p);
Example working on SQL Fiddle .
Use the UNION operation to join the result of two different queries into one.
The query before UNION would only have the results highlighted, while the query after would be complemented with the results not highlighted.
SELECT *
FROM noticias
WHERE status='Publicado'
AND destacar='sim'
ORDER BY idnoticia DESC LIMIT 4
UNION
SELECT *
FROM noticias
WHERE status='Publicado'
AND destacar='nao'
ORDER BY idnoticia DESC
From here on you can simply implement pagination in your own way, using LIMIT and OFFSET .
To do this is easier by programming. Make a regular select with the items you want and then make sure the field is yes and remove
resultados = query(Select....
Contador = 0
Para Cada resultado Em resultados
{
Se resultado.destaque == sim E Contador < 4 {
remova resultado de resultados;
Contador++
}
}