Page 1 of 1

Help with pulling data from an array in MySQL database

Posted: Fri Apr 03, 2015 8:49 am
by fazzno1
Hello everyone,
I'm a teach myself novice dev and have been playing around with this problem for the past 6 hours. Any help is appreciated.

Business Objective -
My website sends automated emails based on a specific action or event.
When the event has occurred, the code needs to pull the user email address from MySQL DB and use as the address for the email.
In this particular code, I am sending 3 emails (1 to each user, and 1 to admin). 2 work, 1 doesn't. I'll show the code for the one that doesn't.

I've narrowed it down to the code not identifying the user_email.

I'll post the code at the bottom of this post but want to ensure context is clear.

In the Database there are 2 sections (offers, and users)
What it needs to do is use the variable 'Offer_id' to identify the 'agent_id', which then is the same as 'id' and allows me to reference the correct user_email.
A million thank yous in advance for saving my sleep :)

Code below is for the one that doesn't:

Code: Select all

$option = $_REQUEST["option"];
$action = $_REQUEST["action"];
$offer_id = $_REQUEST["offer_id"];

if ($action == 'A') {
	
  $sqlaction = "update offers set accept_ind = 'A' where offer_id = '$offer_id' ";
  $rowresult = mysql_query($sqlaction); 
  
  
           
  // Notify Agent Offer has been accepted
	// ************************************
$offer_id = $_REQUEST["offer_id"];
	
	$getAgentId =  mysql_query("SELECT agent_id FROM offers WHERE offer_id = '$offer_id' ");
	$resultAgentId =  mysql_fetch_array($getAgentId);
	$agentId = $resultAgentId['agent_id'];
	$getAgentEmail = mysql_query("SELECT user_email FROM users WHERE id = '$agentId' ");
	$resultAgentEmail =  mysql_fetch_array($getAgentEmail);
	$agentEmail = $resultAgentEmail['user_email'];
....code to send the email is below this and I know works.

Re: Help with pulling data from an array in MySQL database

Posted: Fri Apr 03, 2015 10:01 am
by Celauran
fazzno1 wrote:I've narrowed it down to the code not identifying the user_email.
Have you confirmed that $agentId is populated? Checked mysql_error()?

Re: Help with pulling data from an array in MySQL database

Posted: Fri Apr 03, 2015 4:17 pm
by fazzno1
Hi,
There is no error in code syntax but I'm not sure how to check if agentid is populated.
I used code for a different email to source an email address and the email was sent so I know the email code works.
It's just I can't get the code to populate with the correct email address.
The logic in the code is ok I.e. It makes sense in the way it is trying to identify which email address to populate, but for some reason, it is not doing it.

Thanks

Re: Help with pulling data from an array in MySQL database

Posted: Sat Apr 04, 2015 10:18 am
by Christopher
fazzno1 wrote:Hi,
There is no error in code syntax but I'm not sure how to check if agentid is populated.
First, don't use the mysql extension, it is no longer supported. Use the mysqli extension.

Second, add some minimal filtering/validation to your code.

Code: Select all

$offer_id = (int)$_REQUEST["offer_id"];
// or if alphanumeric
$offer_id = preg_replace('/[^A-Za-z0-9]/', '', $_REQUEST["offer_id"]);
// of filter functions
$offer_id = filter_var($_REQUEST["offer_id"] , FILTER_SANITIZE_STRING);
if ($offer_id) {

Second, add some error checking to your code

Code: Select all

        $errorMsg = '';
        $getAgentId =  mysql_query("SELECT agent_id FROM offers WHERE offer_id = '$offer_id' ");
        if ($getAgentId ) {
            $resultAgentId =  mysql_fetch_array($getAgentId);
            $agentId = $resultAgentId['agent_id'];
            $getAgentEmail = mysql_query("SELECT user_email FROM users WHERE id = '$agentId' ");
            if ($getAgentEmail) {
                $resultAgentEmail =  mysql_fetch_array($getAgentEmail);
                $agentEmail = $resultAgentEmail['user_email'];
            } else {
                $errorMsg = mysql_error();
            }
        } else {
           $errorMsg = mysql_error();
        }

Re: Help with pulling data from an array in MySQL database

Posted: Sat Apr 04, 2015 4:42 pm
by fazzno1
Thanks so much for taking the time.
Can I just replace MySQL with Mysqli?
Do the function changes just by adding the 'I' ?
Does all other syntax remain the same?

Re: Help with pulling data from an array in MySQL database

Posted: Sat Apr 04, 2015 4:57 pm
by Celauran
It's mostly the same. With MySQLi, you need to provide the resource as an argument if you're doing things procedurally. Manual here: http://php.net/manual/en/book.mysqli.php