two queries into one

[drbeamer]

noob question: I have following two queries I'd like to combined into one - how is this done?

 

$temp = @mysql_query("SELECT * FROM purchased_leads WHERE leadID = '{$_REQUEST[leadid]}'");

 

"SELECT refundNotes FROM leads WHERE leadID = '{$_REQUEST[leadid]}'"

[robert_gsfame]

use JOIN

 

so u can use this

 

 

$temp= mysql_query("SELECT purchased_leads.* FROM purchased_leads p JOIN refundNotes r ON p.leadID=r.leadID WHERE p.leadID='{$_REQUEST[leadid]}'");

 

hope that helps

[drbeamer]

use JOIN

 

so u can use this

 

 

$temp= mysql_query("SELECT purchased_leads.* FROM purchased_leads p JOIN refundNotes r ON p.leadID=r.leadID WHERE p.leadID='{$_REQUEST[leadid]}'");

 

hope that helps

 

 

Thanks for the fast reply, Robert, but I can't get it to work. Here is the complete code before and after the edit. Before teh edit, the code sends an email, but after editing there is no email sent anylonger.

 

ORIGINAL:

if(strtolower($prev_status) == "refund requested" && strtolower($status) == "refund denied"){

//refund denied send denied email

//grab customer data

$temp = @mysql_query("SELECT * FROM purchased_leads WHERE leadID = '{$_REQUEST[leadid]}'");

$cusid = mysql_result($temp, 0, 'recID');

$temp = mysql_query("SELECT email, firstName, lastName FROM user WHERE recid = ({$cusid})");

if(mysql_num_rows($temp)>0){

$customer_email = @mysql_result($temp, 0, 'email');

$customer_first_name = @mysql_result($temp, 0, 'firstName');

$customer_last_name = @mysql_result($temp, 0, 'lastName');

 

$templatename = "reviewer_deny_refund";

$strings = Array (

'%FIRST_NAME%' => $customer_first_name,

'%LAST_NAME%' => $customer_last_name,

'%LEADID%' => $_REQUEST[leadid],

'%EMAIL_ADDRESS%' => $customer_email,

'%SITE_NAME%' => $label['site_name'],

'%REQUEST_DATETIME%' => date("Y-m-d H:i:s")

);

 

// Format the e-mail

$email_data = format_email($templatename, $strings);

// Strings to replace in the subject line

$strings = Array (

'%SITE_NAME%' => $label['site_name'],

'%LEADID%' => $_REQUEST[leadid]);

 

$email_data[subject] = format_subject($email_data[subject], $strings);

//echo "DEBUG: line 63<br>";

 

// Send the e-mail

if($customer_email !=""){

mail($customer_email, $email_data[subject], $email_data[message], "From: {$email_address[admin]}\r\n");

}

}

 

}

 

 

 

 

EDITED:

if(strtolower($prev_status) == "refund requested" && strtolower($status) == "refund denied"){

//refund denied send denied email

//grab customer data

$temp = mysql_query("SELECT purchased_leads.* FROM purchased_leads p JOIN refundNotes r ON p.leadID=r.leadID WHERE p.leadID='{$_REQUEST[leadid]}'");

$cusid = mysql_result($temp, 0, 'recID');

$temp = mysql_query("SELECT email, firstName, lastName FROM user WHERE recid = ({$cusid})");

if(mysql_num_rows($temp)>0){

$customer_email = @mysql_result($temp, 0, 'email');

$customer_first_name = @mysql_result($temp, 0, 'firstName');

$customer_last_name = @mysql_result($temp, 0, 'lastName');

$refund_notes = @mysql_result($temp, 0, 'refundNotes');

 

$templatename = "reviewer_deny_refund";

$strings = Array (

'%FIRST_NAME%' => $customer_first_name,

'%LAST_NAME%' => $customer_last_name,

'%LEADID%' => $_REQUEST[leadid],

'%EMAIL_ADDRESS%' => $customer_email,

'%REFUND_NOTES%' => $refund_notes,

'%SITE_NAME%' => $label['site_name'],

'%REQUEST_DATETIME%' => date("Y-m-d H:i:s")

);

 

// Format the e-mail

$email_data = format_email($templatename, $strings);

// Strings to replace in the subject line

$strings = Array (

'%SITE_NAME%' => $label['site_name'],

'%LEADID%' => $_REQUEST[leadid]);

 

$email_data[subject] = format_subject($email_data[subject], $strings);

 

// Send the e-mail

if($customer_email !=""){

mail($customer_email, $email_data[subject], $email_data[message], "From: {$email_address[admin]}\r\n");

}

}

 

}

 

[jcbones]

Of course it will not work like that.

 

You are redefining the variable $temp, and if you are going to use alias' in your query, then you need to define the columns by them as well.

 

  if(strtolower($prev_status) == "refund requested" && strtolower($status) == "refund denied"){      //refund denied send denied email      //grab customer data      $temp1 = mysql_query("SELECT p.recID,r.refundNotes FROM purchased_leads p JOIN refundNotes r ON p.leadID=r.leadID WHERE p.leadID='{$_REQUEST[leadid]}'");      if(mysql_num_rows($temp1) > 0) {       $row = mysql_fetch_row($temp1);       $refund_notes = $row[1];       $cusid = $row[0];      }      $temp2 = mysql_query("SELECT email, firstName, lastName FROM user WHERE recid = ({$cusid})");      if(mysql_num_rows($temp2)>0){         $customer_email = @mysql_result($temp, 0, 'email');         $customer_first_name = @mysql_result($temp, 0, 'firstName');         $customer_last_name = @mysql_result($temp, 0, 'lastName');         $refund_notes = @mysql_result($temp, 0, 'refundNotes');                  $templatename = "reviewer_deny_refund";         $strings = Array (            '%FIRST_NAME%' => $customer_first_name,            '%LAST_NAME%' => $customer_last_name,            '%LEADID%' => $_REQUEST[leadid],            '%EMAIL_ADDRESS%' => $customer_email,            '%REFUND_NOTES%' => $refund_notes,            '%SITE_NAME%' => $label['site_name'],            '%REQUEST_DATETIME%' => date("Y-m-d H:i:s")         );                  // Format the e-mail         $email_data = format_email($templatename, $strings);         // Strings to replace in the subject line         $strings = Array (         '%SITE_NAME%' => $label['site_name'],         '%LEADID%' => $_REQUEST[leadid]);                  $email_data[subject] = format_subject($email_data[subject], $strings);                  // Send the e-mail         if($customer_email !=""){            mail($customer_email, $email_data[subject], $email_data[message], "From: {$email_address[admin]}\r\n");         }      }         }

 

[drbeamer]

Thanks jcbones, I tried your code, but it doesn't work either, meaning the email is not being sent.  Right now the only code that results in an email being sent is the original code above without the refundNotes values.

[jcbones]

And you have echo'd the sql queries so you can see how they are built.

 

And you have checked for any errors returned from the database.

 

And you have checked for any errors coming from the script.

[drbeamer]

And you have echo'd the sql queries so you can see how they are built.

 

And you have checked for any errors returned from the database.

 

And you have checked for any errors coming from the script.

 

Sorry; no. I am not a programmer; I don't know how to check this. All I know how to do is swap code and check the result.  I received no errors on screen, however.

[jcbones]

OK, replace:

$temp1 = mysql_query("SELECT p.recID,r.refundNotes FROM purchased_leads p JOIN refundNotes r ON p.leadID=r.leadID WHERE p.leadID='{$_REQUEST[leadid]}'");

 

With:

$temp1 = mysql_query("SELECT p.recID,r.refundNotes FROM purchased_leads p JOIN refundNotes r ON p.leadID=r.leadID WHERE p.leadID='{$_REQUEST[leadid]}'") or die('Error: ' . mysql_error());

 

This will tell you if the change to your database query is failing.

[drbeamer]

OK, replace:

 

$temp1 = mysql_query("SELECT p.recID,r.refundNotes FROM purchased_leads p JOIN refundNotes r ON p.leadID=r.leadID WHERE p.leadID='{$_REQUEST[leadid]}'");

 

 

With:

 

$temp1 = mysql_query("SELECT p.recID,r.refundNotes FROM purchased_leads p JOIN refundNotes r ON p.leadID=r.leadID WHERE p.leadID='{$_REQUEST[leadid]}'") or die('Error: ' . mysql_error());

 

 

This will tell you if the change to your database query is failing.

 

 

Thanks - this helped!  I found the error, fixed it, but now there is another one:

 

- The first error was that refundNotes is a column in "leads" and not "purchased_leads".  Accordingly, I modified the code as follows:

$temp1 = mysql_query("SELECT p.recID,r.refundNotes FROM leads p JOIN leads r ON p.leadID=r.leadID WHERE p.leadID='{$_REQUEST[leadid]}'") or die('Error: ' . mysql_error());

 

- The new error is this: Error: Unknown column 'p.recID' in 'field list' . 

 

I believe the reason for this error is that recID is a column in the purchased_leads table. So the correct code would pull recID from purchased_leads, refundNotes from leads, and combine them based upon the leadID variable found in both tables.

[jcbones]

I hope I understood that.

 

$temp1 = mysql_query("SELECT p.recID,r.refundNotes FROM purchased_leads p JOIN leads r ON p.leadID=r.leadID WHERE p.leadID='{$_REQUEST[leadid]}'") or die('Error: ' . mysql_error());

[drbeamer]

This cannot work:

 

$temp1 = mysql_query("SELECT p.recID,r.refundNotes FROM purchased_leads p JOIN leads r ON p.leadID=r.leadID WHERE p.leadID='{$_REQUEST[leadid]}'") or die('Error: ' . mysql_error());

 

The reason is that this query tries to select "refundNotes" from the table "purchased_leads".  However, "refundNotes" is a column in the table named "leads".

 

How do we reformat the query to select "refundNotes" from the table "leads", and select recID from table "purchased_leads" ?

[jcbones]

This cannot work:

 

$temp1 = mysql_query("SELECT p.recID,r.refundNotes FROM purchased_leads p JOIN leads r ON p.leadID=r.leadID WHERE p.leadID='{$_REQUEST[leadid]}'") or die('Error: ' . mysql_error());

 

The reason is that this query tries to select "refundNotes" from the table "purchased_leads".  However, "refundNotes" is a column in the table named "leads".

 

How do we reformat the query to select "refundNotes" from the table "leads", and select recID from table "purchased_leads" ?

 

 

You are mistaken.

 

In the query you are using an alias for "leads" called "r".  In the SELECT clause we are asking for "r.refundNotes" which is asking for the refundNotes from the table "leads".

[drbeamer]

Sorry if I mis-interpreted the code - but it's not working. There is no email being sent (the whole point of the code is to send an email containing refundNotes) , and I am not getting any errors on the screen either. 

[jcbones]

At the top of your script insert this line:

 

error_reporting(E_ALL);ini_set('display_errors', 1);

 

See if errors report to the screen.

[drbeamer]

Thanks - but there are no errors on the screen.  I didn't mean to take so much of your time when I initiated the question, but I just didn't know this was so complicated. Are you available to hire on an hourly basis? Would sending you the whole file make it easier to pinpoint the problem?

 

John

[jcbones]

Post the file with your email, and database connection details deleted.

 

Make sure you enclose the code inside of [ php ] tags [ /php ]. <-no spaces of course.

[drbeamer]

Post the file with your email, and database connection details deleted.

 

Make sure you enclose the code inside of [ php ] tags [ /php ]. <-no spaces of course.

 

 

Thanks again for all your help jcbones - problem is resolved.  But I have other little coding jobs in case you are available/interested.  Cheers.