please help with bolded query. Results not right.

[jmcc]

I am trying to send an email to everyone in the table that meet a certain requirement.

 

Everything works fine until I use "AND" or just look for province requirement.

 

 

Code below:

 

////////////////////////Recordset for property being distributed/////////////

 

mysql_select_db($database_prop, $prop);

$query_dist_prop = sprintf("SELECT * FROM seller WHERE id = %s", GetSQLValueString($colname_dist_prop, "int"));

$dist_prop = mysql_query($query_dist_prop, $prop) or die(mysql_error());

$row_dist_prop = mysql_fetch_assoc($dist_prop);

$totalRows_dist_prop = mysql_num_rows($dist_prop);

 

$typeDis = $row_dist_prop['type'];

$provDis = $row_dist_prop['province'];

$cityDis = $row_dist_prop['city'];

 

 

/////////////////////////SEND EMAILS TO PRIVATE BUYERS //////////////////////

 

elseif ( $to == "Buyers") {

$query_dist = "SELECT DISTINCT email FROM private_buyer WHERE notify = '1' AND province = '$provDis' AND city = '$cityDis'";

 

 

$dist = mysql_query($query_dist, $prop) or die(mysql_error());

$row_dist = mysql_fetch_assoc($dist);

$totalRows_dist = mysql_num_rows($dist);

 

 

// ---------------- SEND MAIL FORM ----------------

 

$proptype = $row_dist_prop['type'];

$propprov = $row_dist_prop['province'];

$propcit = $row_dist_prop['city'];

$propsub = $row_dist_prop['suburb'];

$propprice = $row_dist_prop['price'];

$propid = $row_dist_prop['id'];

 

if($totalRows_dist > 0)

{

$count = 0;

while ($row_dist = mysql_fetch_assoc($dist))

{

echo $row_dist['email']; // Just a test to see who will receive email

 

// send e-mail to ...

$to2 = $row_dist['email'] . ', ';

 

// Your subject

$subject="Property Distribution";

 

// From

$header="from: Property Networking Solutions";

 

// Your message

$messages= "Match alert notification\t\n\t\nThis property was sent to you by ..............\t\n\t\nProperty Description:\t\nType: $proptype\t\nProvince: $propprov\t\nCity: $propcit\t\nSuburb: $propsub\t\nPrice: R $propprice\t\n\t\nHere is a link to the property:link.php?id=$propid\t\n\t\n\t\n" ;

 

// send email

$sentmail = mail($to2,$subject,$messages,$header);

$count++;

}

 

}

 

}

[Bricktop]

Hi jmcc,

 

What error are you receiving when using the AND operator?

[jmcc]

There is no error the problem is that when I use the AND or try to refine results results $row_dist['email'] is empty.

 

Example:

This works:

$query_dist = "SELECT DISTINCT email FROM private_buyer WHERE notify = '1' ";

 

This doesnt:

$query_dist = "SELECT DISTINCT email FROM private_buyer WHERE  province = '$provDis' ";

 

Neither does this:

$query_dist = "SELECT DISTINCT email FROM private_buyer WHERE notify = '1' AND province = '$provDis' AND city = '$cityDis'"; 

 

I have done this countless time, just cant figure out why it doesnt work this time.

[Bricktop]

Hi jmcc,

 

If you echo out $provDis and/or $cityDis do you get any data returned?

 

Obviously, WHERE notify = '1' is not relying on the previous database query to return a result, the other examples are so I think that may be where the problem is.

[jmcc]

I have no idea what I just did but it works.

 

Thank you for you input it helped.

 

[jmcc]

no wait i know whats wrong

 

the problem is with my while.

 

Don't know what wrong thought.

 

When I use the while no emails are sent, when I include the while, one email is sent.

 

Code:

////////////////////////////////////// SEND EMAILS TO PRIVATE BUYERS //////////////////////////////

 

else if ( $to == "Buyers") {

 

$query_dist = "SELECT DISTINCT email FROM private_buyer WHERE notify = '1' AND province = '$provDis' AND city = '$cityDis'";

 

$dist = mysql_query($query_dist, $prop) or die(mysql_error());

$row_dist = mysql_fetch_assoc($dist);

$totalRows_dist = mysql_num_rows($dist);

 

 

// ---------------- SEND MAIL FORM ----------------

 

 

if($totalRows_dist > 0)

{

$count = 0;

 

while ($row_dist = mysql_fetch_assoc($dist))

{

 

// send e-mail to ...

$to2 = $row_dist['email'] . ', ';

 

// Your subject

$subject="Property Distribution";

 

// From

$header="from: Property Networking Solutions";

 

// Your message

$messages= "Match alert notification\t\n\t\nThis property was sent to you by Property Networking Solutions.\t\n\t\nProperty Description:\t\nType: $proptype\t\nProvince: $propprov\t\nCity: $propcit\t\nSuburb: $propsub\t\nPrice: R $propprice\t\n\t\nHere is a link to the property: http://www.propertyforsalesa.co.za/buyer_detail_view.php?id=$propid\t\n\t\n\t\n" ;

 

// send email

$sentmail = mail($to2,$subject,$messages,$header);

$count++;

}

 

}

 

}