problem in joining 3 tables using INNER JOIN statement

Question

I am trying to set a sql query that will show jobs from a selected type of trade and within certain distance. I have three tables clients, traders and jobs. What I need is like-

SELECT * from jobs WHERE the type of job required matches by the type of jobs this trader offers AND the DISTANCE between the client and trader is within a range specified

I have managed to get the first part done. it shows all jobs that match the types of job a trader offers, but struggling to get the second part. Here is my query

 $stmt=$pdo->prepare("SELECT jobs.*, clientPostcode, traderPostcode FROM jobs 
                        INNER JOIN traders ON FIND_IN_SET(jobs.tradeType,traders.tradeTypes ) WHERE traders.traderEmail=:traderEmail 
                        INNER JOIN clients ON jobs.clientEmail= clients.clientEmail" );
        $stmt->bindparam('traderEmail',$traderEmail);

    $stmt->execute();
            while($row=$stmt->fetch(PDO::FETCH_ASSOC)){
                //calculate distance between origin and destination 
               IF (condition is ok){
                ?>
                <div class="card col-lg-12 mt-5 text-center">
                    <div class="card-body">
                        <h6 class="card-title text-primary">Job Type: <?php echo $row['tradeType'] ?> (Job Title: <?php echo $row['jobTitle'] ?> ) </h6>
                        <p class="card-text"><?php echo $row['jobDescription'] ?></p>
                        <a class="btn btn-primary" href="">
                        <i class="fas fa-edit fa-xs"></i> Send Interest</a>
                        <a class="btn btn-success" href="" target="_blank">
                        <i class="fas fa-glasses fa-xs"></i> Shortlist</a>
                    </div>
                </div>
                <?php
}
                    }
                ?>

_"struggling to get the second part"_ is not an explanation of your issue. When posting a question, you need to give us a detailed explanation of what actually happens and what debugging you've done. To start with learning [how to debug your PDO queries/statements](https://stackoverflow.com/questions/32648371/my-pdo-statement-doesnt-work). — M. Eriksson, Oct 26 '21 at 22:16
See https://stackoverflow.com/questions/24370975/find-distance-between-two-points-using-latitude-and-longitude-in-mysql for finding the distance between coordinates. — Barmar, Oct 26 '21 at 22:28
@nbk thanks a lot. I changed my code as you suggested and it worked. — user13800891, Oct 26 '21 at 23:09

score 1 · Answer 1 · answered Oct 26 '21 at 23:15

$stmt=$pdo->prepare("SELECT jobs.*, clients.clientPostcode, traders.traderPostcode FROM jobs 
                    INNER JOIN traders ON FIND_IN_SET(jobs.tradeType,traders.tradeTypes )
                    INNER JOIN clients ON jobs.clientEmail= clients.clientEmail  WHERE traders.traderEmail=:traderEmail " );
$stmt->bindparam('traderEmail',$traderEmail);
    $stmt->execute();
        while($row=$stmt->fetch(PDO::FETCH_ASSOC)){
            
            $origin      = $row['traderPostcode'];
            $destination = $row['clientPostcode'];

then I used google distance matrix api (cURL)to calculate distance between the two postcodes. I appreciate everyone for your time. Will try to be more specific for future questions.

problem in joining 3 tables using INNER JOIN statement

1 Answers1