Help with pulling data from an array in MySQL database

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
fazzno1
Forum Newbie
Posts: 3
Joined: Fri Apr 03, 2015 8:38 am

Help with pulling data from an array in MySQL database

Post 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.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

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

Post 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()?
fazzno1
Forum Newbie
Posts: 3
Joined: Fri Apr 03, 2015 8:38 am

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

Post 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
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

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

Post 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();
        }
(#10850)
fazzno1
Forum Newbie
Posts: 3
Joined: Fri Apr 03, 2015 8:38 am

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

Post 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?
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

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

Post 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
Post Reply