I used to have this code to filter a big DB based on many conditions:
$con = "select personas.id as id,personas.firstname, personas.lastname, personas.address, personas.neighborhood, personas.email, personas.address_location_text, relaciones.relative,secciones.name,(select email from sophia_people where document = '".$_REQUEST['dni']."' LIMIT 1) as emailt1, personas.picture_hash, personas.birthday as fecha from
sophia_course_section_enrollments matriculas,
sophia_course_sections secciones,
sophia_people personas,
sophia_people_relations relaciones
where
matriculas.person=personas.id and
matriculas.section=secciones.id and
matriculas.status=2 and
relaciones.person = personas.id and
secciones.year=13 and personas.firstname LIKE _utf8 '%".$_REQUEST['nombre']."%' COLLATE utf8_general_ci and
personas.lastname LIKE _utf8 '%".$_REQUEST['apellido1']." ".$_REQUEST['apellido2']."%' COLLATE utf8_general_ci and
personas.address_location_city = '016' AND
relaciones.relative IN (SELECT id FROM sophia_people where document LIKE '%".$_REQUEST['dni']."%')";
The problem comes in the last 2 lines. I use personas.address_location_city = 016 to check if the student lives in a certain town. Now, the variable changed and towns are stored from now on in address_location_iso_city.
A big amount of students have their town stored in address_location_city and some of them have it stored in address_location_iso_city. In order to solve this issue, I need the mysql query to check for boths, so I need to add this IF Statement:
if address_location_iso_city is null then address_location_city else address_location_iso_city end if
But if I add it to the query doesnt work:
$con = "select personas.id as id,personas.firstname, personas.lastname,
personas.address, personas.neighborhood, personas.email,
personas.address_location_text,
relaciones.relative,secciones.name,
(select email
from sophia_people
where document = '".$_REQUEST['dni']."' LIMIT 1) as emailt1,
personas.picture_hash, personas.birthday as fecha
from sophia_course_section_enrollments matriculas,
sophia_course_sections secciones,
sophia_people personas,
sophia_people_relations relaciones
where matriculas.person=personas.id
and matriculas.section=secciones.id
and matriculas.status=2
and relaciones.person = personas.id
and secciones.year=13
and personas.firstname LIKE _utf8 '%".$_REQUEST['nombre']."%' COLLATE utf8_general_ci
and personas.lastname LIKE _utf8 '%".$_REQUEST['apellido1']." ".$_REQUEST['apellido2']."%' COLLATE utf8_general_ci
and ( if personas.address_location_iso_city is null
then personas.address_location_city = '016'
else personas.address_location_iso_city = '016'
end if)
AND relaciones.relative IN (
SELECT id
FROM sophia_people
where document LIKE '%".$_REQUEST['dni']."%')";
any idea of what I am doing wrong? is there an easier way of doing this?
Aucun commentaire:
Enregistrer un commentaire