Hi, I'm back ^_^

Ok, I almost have this cute thing working. I know I am close but I have no idea how to get this working the right way. I am Left Joining a few tables and I want to filter the results after multiple values.

......

Ex:
Options to Order By:
ID/NAME and ASC/DESC

Options to Selecte WHERE Language:
Language 1 and Language 2 and Language 3 (from lb.limba)

Options to Selecte Having Department Type:
Department type solo or combined results (it can be vas or uscat or uscat_vas)

......

The problem is that when I try to select the options from the LIMBA column, I can only use OR but not AND as a connect operator in my query. Thus if I search for the rows where I have all 3 languages (out of 10) I will get all rows that contain any of the 3 languages specified individually. If I try to use AND then I get zero results. So I am a bit in the dark on how to aproach the solution for this situation. Thanks for your time :)

Here is my code:

    $p1 = 'id';

    $p2 = 'DESC';

    $val1 = 'Engleza';
    $val2 = 'Italiana';
    $val3 = 'Rusa';

    $hv1 = 'vas';
    $hv2 = 'uscat';
    $hv3 = 'uscat_vas';

    $where = "

    WHERE (lb.limba LIKE '%".$val1."%'
        OR lb.limba LIKE '%".$val2."%'
        OR lb.limba LIKE '%".$val3."%')

    ";

    $having = "HAVING tip_job = '".$hv1."'";

        $query = "

        SELECT can.id,
            can.nume,
            can.prenume,
            can.telefon,
            can.sex,

            group_concat(distinct(lb.limba) ORDER BY 1 separator ', ') AS limbi_straine, 
            group_concat(distinct(dp.departament) ORDER BY 1 separator ', ') AS denumiri_job,
            group_concat(distinct(dpt.tip) ORDER BY 1 separator '_') AS tip_job,

            can.cv,
            can.observatii,
            can.interview,
            can.acceptat,
            can.plecat,
            can.ignorat,
            can.stare,
            can.data

        FROM candidati AS can 
            LEFT JOIN limbi_candidati           AS lbc ON (can.id              = lbc.candidat_id)
            LEFT JOIN limbi                     AS lb ON (lb.limba_id          = lbc.limba_id)
            LEFT JOIN departamente_candidati    AS dpc ON (can.id              = dpc.candidat_id)
            LEFT JOIN departamente              AS dp ON (dp.departamanet_id   = dpc.departament_id)
            LEFT JOIN departamente_tip          AS dpt ON (dp.tip_id           = dpt.tip_id)

        ".$where."

        GROUP BY can.id

        ".$having."

        ORDER BY ".$p1." ".$p2

        ;

Recommended Answers

All 2 Replies

I think I made it work. I ditched WHERE in favor of HAVING like this:

$having = "HAVING (limbi_straine LIKE '%".$val1."%' AND limbi_straine LIKE '%".$val2."%' AND limbi_straine LIKE '%".$val3."%') AND tip_job = '".$hv3."'";

I believe you found the right solution!!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.