I'm building a job search site and I have 3 tables.

1: jobs_table: id, user_id, job_title, location, job_description, currency, salary, salary_type, employment_type, post_time, visiblity

2: applications_table: id, creator_id, applicant_id, job_id, status

3: user_table: id, profile_picture, first_name, last_name, phone_number, email_address, password, data, verification_key, modify_date

Currently, I'm selecting from the jobs_table based on user input (PHP code below), however, I'm trying to also display to the user which jobs they have already applied for and to do this I need to select from the Jobs_table (get the jobs data as I'm already doing), but also select from the applications_table with the current users ID to check if there is a row with the applicant_id and job_id if this row exists then the user has already applied for that position.

Any help is much appreciated.

PHP

$conditions = [];

// Start by processing the user input into a data structure that can be used to construct the query

if (!empty($t)) {
    $conditions[] = [
        ['job_title', 'LIKE', '%' . $t . '%'],

    ];

}

if (!empty($l)) {
    $conditions[] = [
        ['location', '=', $l],
    ];
}

if (!empty($s)) {
    $conditions[] = [
        ['salary', '>=', $s],
    ];
}

// Loop the conditions and process them into valid SQL strings

$bindValues = [];
$whereClauseParts = [];

foreach ($conditions as $conditionSet) {
    $set = [];

    foreach ($conditionSet as $condition) {
        list($fieldName, $operator, $value) = $condition;

        $set[] = "`{$fieldName}` {$operator} :{$fieldName}";
        $bindValues[$fieldName] = $value;
    }

    $whereClauseParts[] = implode(' OR ', $set);
}

$statement = "SELECT * FROM 001_jobs_table_as  WHERE visiblity = 2";

if (!empty($whereClauseParts)) {
    $statement .= " AND (" . implode(') AND (', $whereClauseParts) . ")";
}


    /* Pagination Code starts */
    $per_page_html = '';
    $page = 1;
    $start=0;
    if(!empty($_GET["page"])) {
        $page = $_GET["page"];
        $start=($page-1) * ROW_PER_PAGE;
    }


    $limit=" limit " . $start . "," . ROW_PER_PAGE;
    $pagination_statement = $dbh->prepare($statement);
    $pagination_statement->execute($bindValues);


$row_count = $pagination_statement->rowCount();
if(!empty($row_count)){
    $per_page_html .= "<div class='page_row_selector'>";
    $page_count=ceil($row_count/ROW_PER_PAGE);
    if($page_count>1) {
        for($i=1;$i<=$page_count;$i++){
            if($i==$page){
                $per_page_html .= '<input type="submit" name="page" value="' . $i . '" class="btn-page active_page" />';
            } else {
                $per_page_html .= '<input type="submit" name="page" value="' . $i . '" class="btn-page" />';
            }
        }
    }
    $per_page_html .= "</div>";
}

    $query = $statement.$limit;
    $pdo_statement = $dbh->prepare($query);
    $pdo_statement->execute($bindValues);
    $result = $pdo_statement->fetchAll();


if(empty($result)) {  ?>

<div class="job_card">

<h1 class="display-5 text-center no_result_message"> No match found. </h1>

</div>


<?php }else{ 


 foreach($result as $row) { 

 $user_id = $row['user_id'];
 $job_key = $row['id'];
 $job_title = $row['job_title'];
 $location = $row['location'];
 $job_description = $row['job_description'];
 $employment_type = $row['employment_type'];
 $salary = $row['salary'];
 $salary_type = $row['salary_type'];
 $currency = $row['currency'];
 $post_time = $row['post_time'];
 $user_id = $row['user_id'];

$to_time = time();
$from_time = strtotime($post_time);
$time_elapsed = $to_time - $from_time; 

$seconds = round(abs($time_elapsed));   
$minutes = round(abs($time_elapsed) / 60);    
$hours = round(abs($time_elapsed) / 3600);    
$days = round(abs($time_elapsed) / 86400);    
$weeks = round(abs($time_elapsed) / 604800);


// display job information in here. 


} ?>

UPDATE:

I have now revised my SELECT query to the following:

$statement = "SELECT * FROM 001_jobs_table_as jt";

$statement .= " LEFT JOIN 001_application_table_as at ON at.job_id = jt.jt_id";

$statement .= " RIGHT JOIN 001_user_table_as ut ON ut.id = at.applicant_id";

$statement .= " WHERE jt.visiblity = 2";

However, I'm getting duplicates in the results, every user that applies for a job duplicates that job in the results.

What about using LEFT JOIN?

The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2).

SELECT *, id AS jt_id FROM jobs_table jt
LEFT JOIN applications_table at ON jt.jt_id = at.job_id AND jt.user_id = at.applicant_id 
WHERE jt.visibility = 2 AND (jt.job_title LIKE :job_title) AND (jt.location = :location) AND (jt.salary >= :salary);

This should return all rows from jobs_table which match searched criteria and some of those rows can have extra data from applications_table if user already applied to that specific job (row) from jobs_table.

Something like:

jt_id  user_id  job_title  location  ... id  applicant_id  job_id
=================================================================
1      15       php dev    london
2      23       java dev   liverpool
3      44       haskell    manchester
4      52       front end  bristol       7   52            4
5      66       golang     leeds

Row with jt_id = 4 has some extra values meaning user already applied to that job. This should give you some directions but unfortunatelly, i didn't have a time to test this query.

EDIT

I've made a mistake. LEFT JOIN should go before WHERE clause...silly me. Check the query once again, it has been updated. Or try it online