How to check if two columns match from joined tables?

[robatojazzo]

I'm trying to check two see if the customer is within range of the realtor and if so to email that realtor. So far I got to the point where I can calculate the distance in the query. So since I can match those that are in distance how to check if two columns match? I need to know if two columns match what is the best way for this to be done?

Here is my php code.

           $realtCust = $db->prepare("SELECT
                rid
              , rEmail
              ,id
              , eMail
            FROM realtors r
                LEFT JOIN
              customers c
                ON pow(clatitude-rlatitude, 2) + pow(clongitude-rlongitude, 2) < pow(100/110.25, 2)
            ORDER BY rid, id");
            
            $realtCust->execute();
            $realtCust->rowCount();
    $realtCust_row = $realtCust->fetch(PDO::FETCH_ASSOC);
    $rcArray = array(
         [$realtCust_row['rid'],$realtCust_row['rEmail']],
         [$realtCust_row['id'],$realtCust_row['eMail']],
    
    );
    foreach( $rcArray as mail($rcEmail,)) {
        //echo "$a\n";
      if($arraysAreEqual = ($rcEmail==$rcEmail))
    
        mail();
    
      }else{
        echo "don't work";
      }
    }

 

[robatojazzo]

I updated my code.

$realtCust = $db->prepare("SELECT
rid
, rEmail
,id
, eMail
FROM realtors r
LEFT JOIN
customers c
ON pow(clatitude-rlatitude, 2) + pow(clongitude-rlongitude, 2) < pow(100/110.25, 2)
ORDER BY rid, id");

$realtCust->execute();
$realtCust->rowCount();
$realtCust_row = $realtCust->fetch(PDO::FETCH_ASSOC);
$rcArray = array(
[$realtCust_row['rid'],$realtCust_row['rEmail']],
[$realtCust_row['id'],$realtCust_row['eMail']],

);
$rcArray = array(
[$realtCust_row['rid'],$realtCust_row['rEmail']],
[$realtCust_row['id'],$realtCust_row['eMail']],

);
foreach( $rcArray as list( ,$rcEmail)) {
//echo "$a\n";

echo $rcEmail;
}

[Barand]

That query gives results similar to this (as per my reply over at devshed)

+-----+--------------------+----------+-------------------+
| rid | rEmail             | fullname | email             |
+-----+--------------------+----------+-------------------+
|   1 | realtor1@gmail.com | Cust_1   | fownes@gmail.com  |
|   1 | realtor1@gmail.com | Cust_3   | upton@gmail.com   |
|   1 | realtor1@gmail.com | Cust_5   | severn@gmail.com  |
|   1 | realtor1@gmail.com | Cust_10  | puckrup@gmail.com |
|   2 | realtor2@xyz.com   | Cust_2   | falcon@gmail.com  |
|   2 | realtor2@xyz.com   | Cust_6   | swan@gmail.com    |
|   2 | realtor2@xyz.com   | Cust_8   | sequoia@gmail.com |
|   2 | realtor2@xyz.com   | Cust_9   | moat@gmail.com    |
|   3 | realtor3@gmail.com | Cust_4   | bibury@gmail.com  |
|   3 | realtor3@gmail.com | Cust_7   | crucis@gmail.com  |
+-----+--------------------+----------+-------------------+

This gives the realtors with customers that are within the prescribed distance (in this case 100Km).

 

So realtor 1 has customers 1, 3, 5, 10 within range, realtor 2 has 2, 6, 8, 9 and realtor 3 has 4 and 7 within range.

 

What you haven't told is us what you want to do with that data, other than a vague "I want to send emails"

[robatojazzo]

 I'm trying to get my code to email the associate realtor.  For example if realtor 1 has customers 1, 3, 5, 10 within range then I want to send 1 email notification per customer with their email information to realtor 1.  This is pretty complex code well to me but i been trying different ways to do this like using an associative array.  Sorry for being vague but I'm pressed for time on my final project. 

[Barand]

Then all you need to do is loop through the results of that query,

for each row 
    send an email to the realtor email containing the customer email.
end foreach

[robatojazzo]

Sorry I took to long to reply but Ok so I changed my code and emails are going to their respected recipients.  The only problem is that there is two registered customers that is within distance of realtor_2.  Only one of the email address show up.  I was thinking of maybe of making a new table for matches.  So I did a print_r to see what was going on and this is what I got.

Array
(
    [rid] => 2
    [0] => 2
    [rEmail] => realtor_2@gmail.com
    [1] => realtor_2@gmail.com
    [id] => 3
    [2] => 3
    [eMail] => cust_1@coolmail.com
    [3] => cust_1@coolmail.com
)
Array
(
    [rid] => 4
    [0] => 4
    [rEmail] => realtor_1@gmail.com
    [1] => realtor_1@gmail.com
    [id] => 2
    [2] => 2
    [eMail] => cust_2@google.com
    [3] => cust_2@google.com
)
Array
(
    [rid] => 5
    [0] => 5
    [rEmail] => realtor_3@gmail.com
    [1] => realtor_3@gmail.com
    [id] => 
    [2] => 
    [eMail] => 
    [3] => 
)

This is the foreach loop. 

 
$realtCust_row = $realtCust->fetch(PDO::FETCH_ASSOC);

foreach ($realtCust as $realtCust_row):

  //send an email to the realtor email containing the customer email.

 $msgBody = "This is a test";

 // use wordwrap() if lines are longer than 70 characters
 $msgBody = wordwrap($msgBody,70);

 $Mailto = $realtCust_row['rEmail'];

 $FromEmail = $realtCust_row['eMail'];

  //mail($Mailto, $FromEmail , $msgBody);


echo "<pre>";
    print_r($realtCust_row);
echo "</pre>";

endforeach;

Edited January 22, 2017 by robatojazzo

[Barand]

You can't put the customer email as the "From" in the email. The from address must be from the same domain as the server. Put the customer email in a "Reply-To" header.

This is all you need

<?php

$sql = "SELECT
          rEmail
        , eMail
        , c.fullname
        FROM realtors r
        LEFT JOIN
        customers c
          ON pow(clatitude-rlatitude, 2) + pow((clongitude-rlongitude)*cos(radians(rlatitude)), 2) < pow(100/110.25, 2)
        ORDER BY rid, id";
$realCust = $db->query($sql);

foreach ($realCust as $email) {
    $to = $email['rEmail'];
    $subject = "Test";
    $message = "Hello, I am {$email['fullname']}";
    $headers = "Reply-To: {$email['eMail']}\r\n";
    
    // mail($to, $subject, $message, $headers);
    
    echo "To : $to<br>$subject<br>$message<br>$headers<hr>";
}
?>

[robatojazzo]

Thanks ok I see.  What was I thinking before?  To be honest I used another forum besides this one and devshed for help and I'm still learning mySQL.  My problem is I took on a big project and jumped in without a life jacket.  I just needed to break this down so I can understand what I'm trying to do.  It was a choice between mySQL or Access.  I'm really migrating from a windows environment to linux.  I like linux just a super newbie thats it.

 

Well with the project the script is working but now its emailing everybody even if they don't have customers in their area.  I set up test emails for the realtors using gmail.  I was thinking maybe making a email list with a time variable.

Edited January 22, 2017 by robatojazzo

[Barand]

Change LEFT JOIN to INNER JOIN. That way only realtors with customers will be found.

[robatojazzo]

Ok so for distance I added something that may complicate my code.  The realtor is willing to travel a certain distance in miles.  I added a column in my MySQL called willToTravel.  The data in that column is in miles.

`willToTravel` int(11) NOT NULL,

Ok so based off that information I needed clients to that are in the distance range of willToTravel.  Do you think it will be a good idea to calculate using 

acos and cos? 

[Barand]

Change query to

SELECT
  rEmail
, eMail
, c.fullname
FROM realtors r
LEFT JOIN
customers c
  ON pow(clatitude-rlatitude, 2) + pow((clongitude-rlongitude)*cos(radians(rlatitude)), 2) < pow(willtotravel/69.13, 2)
ORDER BY rid, id

[robatojazzo]

wow! Ok I need to study up more on this.  Do you have a paypal?  I don't have much but I would love to buy you coffee or tea.  Do you teach?

[Barand]

There is a "Donate to me" link underneath my avatar on the left. Thanks.

[Barand]

Sorry, I forgot to change it to INNER JOIN in that last version of the query.