help debugging query

[adammc]

Hi Guys,

I was wondering if anyone can help me here?
The following code sends out an email alert to members of my site when a matching result is found.

[php]
<?

// Code for auto email for job alerts BEGINS

// Perform the Email Alerts Query
$MasterSQL="SELECT email,vehicle_make,vehicle_model FROM email_alerts WHERE (categories LIKE '%".$category."%' OR categories='any') AND (condition LIKE '%".$condition."%' OR condition='any') AND (state LIKE '%".$buyers_state."%' OR state='any') AND (vehicle_make LIKE '%".$vehicle_make."%' OR vehicle_make='any')";
$MasterSQL_Result=mysql_query($MasterSQL);
// Loop through the result set
for ($a=0; $a < mysql_num_rows($MasterSQL_Result); $a++)
{
// Assign result to an array
$EArray=mysql_fetch_array($MasterSQL_Result);
// Perform vehicle_model validation
If (trim($EArray["vehicle_make"])!="any")
{
$SendMail=0;
// Vehicle Model must also match
If (stristr($EArray["vehicle_model"],$vehicle_model))
{
$SendMail=1;
}
}Else
{
$SendMail=1;
}
// Check if we are ok to send email
If ($SendMail==1)
{
/* subject */
$subject = "Email Alert from Parts Web";
[/php]

It is not working correctly though :(

It is sending out an email when $condition is not equal to what is held in the email alert table?

Example condition options:
New, Secondhand, Reconditioned, Any

condition LIKE '%".$condition."%' OR condition='any'

[mcibor]

The code looks correct, it's not clear, but correct.

Why don't you just get mail alerts you want?

As I understand: category must be stated or any, the same with condition, only when vehicle make is stated, then the model has to be as well:

$MasterSQL="SELECT email, vehicle_make, vehicle_model FROM email_alerts WHERE (categories='$category' OR categories='any') AND (condition='$condition' OR condition='any') AND (state LIKE '$buyers_state' OR state='any') AND ((vehicle_make ='$vehicle_make' AND vehicle_model='$vehicle_model') OR vehicle_make='any')";

Hope this helps
Michal

[adammc]

Hi mcibor,
Thanks for the reply :)

Do i then need "// Perform vehicle_model validation"

Or do I just replace the query in my code with yours?

[mcibor]

My code should work, but it won't solve your first problem with condition.

How are you obtaing the variables: $condition, $category, etc.?

Michal

[adammc]

They are being 'posted' from a form.

[mcibor]

Then you have to use:

$condition = mysql_real_escape_string [php.net]($_POST['condition']);

and do that for each variable.
If you have register globals on in your php.ini, better to turn it off.
Also always validate your external data.

Hope it will work now and sorry for late answer.

Regards
Michal