mercredi 12 mai 2021

IF condition in MSQL not returning data

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