I am creating a search engine, for this I already have the query working, at the end of the query sent the parameter "LIMIT", because with that query I return the results and build the paging, however I have a probelama (precisely with the page that I can not identify).
This is my original query that works without probelam:
select v.id as idvcante, v.ky, v.titulo,
municipios.nombre as municipio, estados.nombre as estado, e.id as idempresa,
e.empresa, e.img_perfil,
empresas_contratos_vacantes.tipo, empresas_rangosal_vacantes.rango,
v.fechapublicacion,
empresas_nivexp_vacantes.nivel as experiencia, vacantes_cat.categoria,
vacantes_subcat.subcategoria from vacantes as v
inner join municipios on municipios.id=v.id_municipio
inner join estados on estados.id=v.id_estado
inner join empresas as e on e.id=v.id_empresa
inner join empresas_contratos_vacantes on
empresas_contratos_vacantes.id=v.id_contrato
inner join empresas_rangosal_vacantes on
empresas_rangosal_vacantes.id=v.id_rangosalario
inner join empresas_nivexp_vacantes on
empresas_nivexp_vacantes.id=v.id_experiencia
inner join vacantes_cat on vacantes_cat.id=v.id_categoria
inner join vacantes_subcat on vacantes_subcat.id=v.id_subcat where
v.id_estado=30 and v.titulo like "%asesor de crédito%" or titulo like
'%asesor%' or titulo like '%crédito%' order by visitas DESC LIMIT 5,5
The problem is that, I need to make this same query, but without the limit ( LIMIT 5,5 ), to know the total number of records that search in specific is generating me. To do this, I thought of adding this line:
(select count(*) from vacantes where v.id_estado=30 and v.titulo like
"%asesor de crédito%" or titulo like '%asesor%' or titulo like '%crédito%' )
as total
The query is now as follows:
select (select count(*) from vacantes where v.id_estado=30 and v.titulo like
"%asesor de crédito%" or titulo like '%asesor%' or titulo like '%crédito%' )
as total, v.id as idvcante, v.ky, v.titulo, municipios.nombre as municipio,
estados.nombre as estado, e.id as idempresa, e.empresa, e.img_perfil,
empresas_contratos_vacantes.tipo, empresas_rangosal_vacantes.rango,
v.fechapublicacion, empresas_nivexp_vacantes.nivel as experiencia,
vacantes_cat.categoria, vacantes_subcat.subcategoria from vacantes as v
inner join municipios on municipios.id=v.id_municipio inner join estados on
estados.id=v.id_estado inner join empresas as e on e.id=v.id_empresa inner
join empresas_contratos_vacantes on
empresas_contratos_vacantes.id=v.id_contrato inner join
empresas_rangosal_vacantes on
empresas_rangosal_vacantes.id=v.id_rangosalario inner join
empresas_nivexp_vacantes on empresas_nivexp_vacantes.id=v.id_experiencia
inner join vacantes_cat on vacantes_cat.id=v.id_categoria inner join
vacantes_subcat on vacantes_subcat.id=v.id_subcat where v.id_estado=30 and
v.titulo like "%asesor de crédito%" or titulo like '%asesor%' or titulo like
'%crédito%' order by visitas DESC LIMIT 5,5,
Everything to have this column. The problem is that for some reason it does not generate the same number of results, so I use that value to do the paging, where it must show 17 results, in some cases it shows 48 :( (I refer already visually in the system that I am developing)
So, concretely: Can someone help me identify what I'm doing wrong or in what case I can do this better?
Thank you very much for your time but above all for the help.
PS: You have to take into account that I always need to know in each query the total results that the search generates (because I need it in the code to build the results and the paging), even if they are only being listed 5, 10 o X Number the results, that will depend on the LIMIT parameter that I send at the end.
