send data from sql to email

[alibaba1]

Hi everyone hope all well. I am needing your valuable expertise in a problem i am having. I will try to explain below.

I have a form and inside of that form i have a "select a state" option:

<select name="State">
<option value="0" selected="selected">Select a State</option>
<option value="AL">Alabama</option>
<option value="AK">Alaska</option>
<option value="AZ">Arizona</option>
<option value="AR">Arkansas</option>
   etc.....
</select>
 

When the customer selects a state and then clicks the forms submit button, i want them to get taken my mysql database and pull an ip address from the table relevant to what they choose.

I have created a table inside of phpmyadmin with two fields.
the first is: state,varchar,30,primary
the second is: ip,text

I then imported an csv file which populated it with the following format
state ip
AL 67.100.244.74
AK 68.20.131.135
AK 64.134.225.33
etc.......


So an example would be: customer fills out the form and selects Alabama as a state. They then submit the form and the form connects to the database where it sees the state Alabama (AL). It then collects the ip from the ip section releated to the ALbama section, and submits that to my email address along with the rest of the form information (name,email,etc...). It also needs to randomly choose an ip from Alabama because in the database i have Alabama (AL) multiple times, so it just chooses anyone of the Alabama ip's

I hope i am making sense

Thanks for all your help

Ali

[Dormilich]

i want them to get taken my mysql database and pull an ip address from the table

I don’t see a problem with that. just query your DB before sending the mail.

It also needs to randomly choose an ip from Alabama because in the database i have Alabama (AL) multiple times

this is usually done through ORDER BY RAND() LIMIT 1

[alibaba1]

Hey Dormilich thanks for your reply and sorry in the delay. I had a look into query DB and i found this that might help me

<select name="State">
<option value="0" selected="selected">Select a State</option>
<option value="AL">Alabama</option>
<option value="AK">Alaska</option>
<option value="AZ">Arizona</option>
<option value="AR">Arkansas</option>
   etc.....
</select>
 
<?php
$state = $_POST['State']; // User selected Alaska (AK) so $_POST['State'] = 'AK'
$query = "SELECT state, ip FROM ipaddresses WHERE state='" . mysql_real_escape_string($state) . "'";
$result = mysql_query($query, $link_resource);
$row = mysql_fetch_assoc($result);
 
// ip-address belonging to Alaska (AK) turns out to be 68.20.131.135 as it's stored in the same row as AK
?>
 

The problem i have with this is it keeps giving errors. I mst be doing something wrong. This is the error i get is this

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) in /home/content/b/o/l/boldbaba406/html/1stop/1vince/test1.php on line 12
 
Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: A link to the server could not be established in /home/content/b/o/l/boldbaba406/html/1stop/1vince/test1.php on line 12
 
Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in /home/content/b/o/l/boldbaba406/html/1stop/1vince/test1.php on line 13
 
Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/content/b/o/l/boldbaba406/html/1stop/1vince/test1.php on line 14

I also had a look at your recommended ORDER BY RAND() LIMIT 1 and came up with this code

// Get the state that the user selected
$selectedState = mysql_real_escape_string($_REQUEST['State']);
 
// Build a query
$query = "SELECT ip
          FROM table_name
          WHERE state = '$selectedState'
          ORDER BY rand()
          LIMIT 1";
 
// Execute it
$result = mysql_query($query);
 
// Fetch the result as an array
$row = mysql_fetch_assoc($result);
 
// The IP address you want is now stored in $row['ip']
 

I just can not get it to work. Can you show me how i am supposed to put all of this together as it is like a jigsaw at the moment. thanks you for all your help

Ali

[Dormilich]

you need to properly open the DB connection before you can use any of the DB functions.

there’s also a safer (and more modern) way to talk to the DB:

<?php
// visit http://php.net/pdo for more details
 
// start error handling
try 
{
  // connect
  $pdo = new PDO("mysql:host=localhost;dbname=your_db_table", $login, $password);
  // enable error handling through exceptions
  $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  // create safe query
  $query = $pdo->prepare("SELECT ip FROM table_name WHERE state = ? ORDER BY rand() LIMIT 1");
  // pass data & execute query (since the data are of string type
  // and therefore can be passed in this lazy way)
  $query->execute(array($_POST['State']));
  // get value
  $ip = $query->fetchColumn();
  // print out the IP address using $ip
}
catch (Exception $e)
{
  echo "sorry, there was an error.";
  mail("[e-mail]", "database error", $e->getMessage(), "From: [e-mail]");
}

this might look like a lot more to write, but it’s
- safer (no SQL injection can penetrate this)
- more customizable (there are more than a dozen fetch modes)
- if one component of the DB code fails, it doesn’t break the whole script

[alibaba1]

Thanks for that, you have been a great help. Do i not need to add username and password anywhere? Thanks for everything

Ali

[Dormilich]

$pdo = new PDO("mysql:host=localhost;dbname=your_db_table", $login, $password);

like that?

[alibaba1]

Thanks for that.Do i need to add some php to the html state part at all

<select name="State">
<option value="0" selected="selected">Select a State</option>
<option value="AL">Alabama</option>
<option value="AK">Alaska</option>
<option value="AZ">Arizona</option>
<option value="AR">Arkansas</option>
   etc.....
</select>
 

Thanks for everything

Ali

[Dormilich]

for what purpose?

[alibaba1]

Sorry Dormilich, i am probably being a muppit here but this is what i am try to do. I took your code and added it to a php file. Then i change the info inside to direct to my databse, i also changed this bit of code as well:

$query = $pdo->prepare("SELECT ip FROM vincer WHERE state = ? ORDER BY rand() LIMIT 1");

I added my table called vincer.

I then created a basic html form with my state dropdown menu in it. I then added the name of my php file into the action like the code below

<form name="contactform" method="post" action="form.php">

When i go to test the form live and submit it i get this in an email

SQLSTATE[42000] [1044] Access denied for user 'vincetest1'@'%' to database 'vincer'

Im not sure what i am missing that is why i thought maybe i might need some php in the form but obviously not. Any help would be great. thanks

ALi

[Dormilich]

there’s a problem with the connection string (the so-called DSN) (cf. post #6). for some reason the host is wrong, there should be "localhost" where the "%" is.

[alibaba1]

i am not sure if this could be the issue but for the ip table in my phpmyadmin i have the type to be decimal(20,0. I was not sure what it should be set to either decimal,varchar, text etc.. could this be the issue and also is this right

Thanks

ALi

[Dormilich]

type of what?

the problem above is that you have the wrong host, i.e. you don’t even connect. could you post the DSN you use?

[alibaba1]

Hi Dormilich. I have been testing this on two different hosting accounts.
Hostgator and godaddy.

On godaddy when i had hostname set to localhost it brought up this error in my email

SQLSTATE[HY000] [2002] Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

So i changed localhost to godaddy given hostname. When i test the form again no errors are given and everything submits, but i do not receive any email. So i thought i would test it on hostgator. Hostgator allow localhost as the host name so i left it as that. I uploaded and and tested it and i get exactly the same as the second godaddy attempt, the form submits but receive no email.

I'm sure it is something small. Any help would be great

Thanks

Ali

[Dormilich]

did you test whether mail() returned true? did you include the From: header? did you check the spam folder? (and of course error mails only occur on errors)

[alibaba1]

did you test whether mail() returned true?
No i did not, not sure exactly what this

did you include the From: header?
Is that this bit of code

 mail("[e-mail]", "database error", $e->getMessage(), "From: [e-mail]");
 

did you check the spam folder?
Yes it is not in there

Thanks

Ali

[Dormilich]

er, if there are no errors, you don’t receive an error mail …

[alibaba1]

yeah there is no errors at all. I just do not receive the email. Could i mix the php code you sent me with something like this

<?php
if(isset($_POST['email'])) {
 
    // EDIT THE 2 LINES BELOW AS REQUIRED
    $email_to = "[e-mail]";
    $email_subject = "This is a test";
 
 
    function died($error) {
        // your error code can go here
        echo "We are very sorry, but there were error(s) found with the form you submitted. ";
        echo "These errors appear below.<br /><br />";
        echo $error."<br /><br />";
        echo "Please go back and fix these errors.<br /><br />";
        die();
    }
 
    // validation expected data exists
    if(!isset($_POST['first_name']) ||
        !isset($_POST['last_name']) ||
        !isset($_POST['email']) ||
        !isset($_POST['State']) ||
        !isset($_POST['comments'])) {
        died('We are sorry, but there appears to be a problem with the form you submitted.');       
    }
 
    $first_name = $_POST['first_name']; // required
    $last_name = $_POST['last_name']; // required
    $email_from = $_POST['email']; // required
    $state = $_POST['State']; // not required
    $comments = $_POST['comments']; // required
 
    $error_message = "";
    $email_exp = '/^[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}$/';
  if(!preg_match($email_exp,$email_from)) {
    $error_message .= 'The Email Address you entered does not appear to be valid.<br />';
  }
    $string_exp = "/^[A-Za-z .'-]+$/";
  if(!preg_match($string_exp,$first_name)) {
    $error_message .= 'The First Name you entered does not appear to be valid.<br />';
  }
  if(!preg_match($string_exp,$last_name)) {
    $error_message .= 'The Last Name you entered does not appear to be valid.<br />';
  }
  if(strlen($comments) < 2) {
    $error_message .= 'The Comments you entered do not appear to be valid.<br />';
  }
  if(strlen($error_message) > 0) {
    died($error_message);
  }
    $email_message = "Form details below.\n\n";
 
    function clean_string($string) {
      $bad = array("content-type","bcc:","to:","cc:","href");
      return str_replace($bad,"",$string);
    }
 
    $email_message .= "First Name: ".clean_string($first_name)."\n";
    $email_message .= "Last Name: ".clean_string($last_name)."\n";
    $email_message .= "Email: ".clean_string($email_from)."\n";
    $email_message .= "State: ".clean_string($state)."\n";
    $email_message .= "Comments: ".clean_string($comments)."\n";
 
 
// create email headers
$headers = 'From: '.$email_from."\r\n".
'Reply-To: '.$email_from."\r\n" .
'X-Mailer: PHP/' . phpversion();
@mail($email_to, $email_subject, $email_message, $headers);  
?>
 
<!-- include your own success html here -->
 
Thank you for contacting us. We will be in touch with you very soon.
 
<?php
}
?>
 

I receive the email when i use the above script. It also sends me the state AL,AK,AR etc.. instead of the actual ip

Thanks

ALi

[Dormilich]

you could somewhat manage that, though you have 2 different error handling methods (die() & exceptions).

the main problem is line #69. you have absolutely no verification, whether the mail was sent or not. try

if (!mail(/* message etc. */))
{
    echo "failed to send message";
}

[alibaba1]

This is how my php code looks

 
<?php
// visit http://php.net/pdo for more details
// start error handling
 
try 
{
  // connect
  $pdo = new PDO('mysql:host=localhost;dbname=name', 'user', 'pass');
  // enable error handling through exceptions
  $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  // create safe query
  $query = $pdo->prepare("SELECT ip FROM vincer WHERE state = ? ORDER BY rand() LIMIT 1");
  // pass data & execute query (since the data are of string type
  // and therefore can be passed in this lazy way)
  $query->execute(array($_POST['State']));
  // get value
  $ip = $query->fetchColumn();
  // print out the IP address using $ip
}
catch (Exception $e)
{
  echo "sorry, there was an error.";
  mail("[e-mail]", "database error", $e->getMessage(), "From: [e-mail]");
}
 
if(isset($_POST['email'])) {
 
    // EDIT THE 2 LINES BELOW AS REQUIRED
    $email_to = "[e-mail]";
    $email_subject = "This is a test";
 
 
    function died($error) {
        // your error code can go here
        echo "We are very sorry, but there were error(s) found with the form you submitted. ";
        echo "These errors appear below.<br /><br />";
        echo $error."<br /><br />";
        echo "Please go back and fix these errors.<br /><br />";
        die();
    }
 
    // validation expected data exists
    if(!isset($_POST['first_name']) ||
        !isset($_POST['last_name']) ||
        !isset($_POST['email']) ||
        !isset($_POST['State']) ||
        !isset($_POST['comments'])) {
        died('We are sorry, but there appears to be a problem with the form you submitted.');       
    }
 
    $first_name = $_POST['first_name']; // required
    $last_name = $_POST['last_name']; // required
    $email_from = $_POST['email']; // required
    $state = $_POST['State']; // not required
    $comments = $_POST['comments']; // required
 
    $error_message = "";
    $email_exp = '/^[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}$/';
  if(!preg_match($email_exp,$email_from)) {
    $error_message .= 'The Email Address you entered does not appear to be valid.<br />';
  }
    $string_exp = "/^[A-Za-z .'-]+$/";
  if(!preg_match($string_exp,$first_name)) {
    $error_message .= 'The First Name you entered does not appear to be valid.<br />';
  }
  if(!preg_match($string_exp,$last_name)) {
    $error_message .= 'The Last Name you entered does not appear to be valid.<br />';
  }
  if(strlen($comments) < 2) {
    $error_message .= 'The Comments you entered do not appear to be valid.<br />';
  }
  if(strlen($error_message) > 0) {
    died($error_message);
  }
    $email_message = "Form details below.\n\n";
 
    function clean_string($string) {
      $bad = array("content-type","bcc:","to:","cc:","href");
      return str_replace($bad,"",$string);
    }
 
    $email_message .= "First Name: ".clean_string($first_name)."\n";
    $email_message .= "Last Name: ".clean_string($last_name)."\n";
    $email_message .= "Email: ".clean_string($email_from)."\n";
    $email_message .= "State: ".clean_string($state)."\n";
    $email_message .= "Comments: ".clean_string($comments)."\n";
 
 
// create email headers
$headers = 'From: '.$email_from."\r\n".
'Reply-To: '.$email_from."\r\n" .
'X-Mailer: PHP/' . phpversion();
if (!mail($email_to, $email_subject, $email_message, $headers))
{
    echo "failed to send message";
}  
 
?>
 
 
<!-- include your own success html here -->
 
Thank you for contacting us. We will be in touch with you very soon.
 
<?php
}
?>
 

When i use this i receive an email and this is how it comes out

First Name: afdf
Last Name: sfgsdf
Email: [e-mail]
State: AZ
Comments: dsfsda
 

as you can see all i get is the two letters of the state instead of the ip.

You must be getting board of this, sorry for all the questions

Ali

[Dormilich]

well, if you use $state instead of $ip …

tip:
isset($_POST['first_name'], $_POST['last_name']) only returns true if both indices exist

$email = filter_input(INPUT_POST, "email", FILTER_VALIDATE_EMAIL);

[alibaba1]

Hey Dormilich Thanks for all your help you have been fantastic. Happy to say finally got the ip address coming in the email. This was the final code

 
<?php
// visit http://php.net/pdo for more details
// start error handling
 
try 
{
  // connect
  $pdo = new PDO('mysql:host=localhost;dbname=name', 'user', 'pass');
  // enable error handling through exceptions
  $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  // create safe query
  $query = $pdo->prepare("SELECT ip FROM vincer WHERE state = ? ORDER BY rand() LIMIT 1");
  // pass data & execute query (since the data are of string type
  // and therefore can be passed in this lazy way)
  $query->execute(array($_POST['State']));
  // get value
  $ip = $query->fetchColumn();
  // print out the IP address using $ip
}
catch (Exception $e)
{
  echo "sorry, there was an error.";
  mail("[e-mail]", "database error", $e->getMessage(), "From: [e-mail]");
}
 
if(isset($_POST['email'])) {
 
    // EDIT THE 2 LINES BELOW AS REQUIRED
    $email_to = "[e-mail]";
    $email_subject = "This is a test";
 
 
    function died($error) {
        // your error code can go here
        echo "We are very sorry, but there were error(s) found with the form you submitted. ";
        echo "These errors appear below.<br /><br />";
        echo $error."<br /><br />";
        echo "Please go back and fix these errors.<br /><br />";
        die();
    }
 
    // validation expected data exists
    if(!isset($_POST['first_name']) ||
        !isset($_POST['last_name']) ||
        !isset($_POST['email']) ||
        !isset($_POST['State']) ||
        !isset($_POST['comments'])) {
        died('We are sorry, but there appears to be a problem with the form you submitted.');       
    }
 
    $first_name = $_POST['first_name']; // required
    $last_name = $_POST['last_name']; // required
    $email_from = $_POST['email']; // required
    $state = $_POST['State']; // not required
    $comments = $_POST['comments']; // required
 
    $error_message = "";
    $email_exp = '/^[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}$/';
  if(!preg_match($email_exp,$email_from)) {
    $error_message .= 'The Email Address you entered does not appear to be valid.<br />';
  }
    $string_exp = "/^[A-Za-z .'-]+$/";
  if(!preg_match($string_exp,$first_name)) {
    $error_message .= 'The First Name you entered does not appear to be valid.<br />';
  }
  if(!preg_match($string_exp,$last_name)) {
    $error_message .= 'The Last Name you entered does not appear to be valid.<br />';
  }
  if(strlen($comments) < 2) {
    $error_message .= 'The Comments you entered do not appear to be valid.<br />';
  }
  if(strlen($error_message) > 0) {
    died($error_message);
  }
    $email_message = "Form details below.\n\n";
 
    function clean_string($string) {
      $bad = array("content-type","bcc:","to:","cc:","href");
      return str_replace($bad,"",$string);
    }
 
    $email_message .= "First Name: ".clean_string($first_name)."\n";
    $email_message .= "Last Name: ".clean_string($last_name)."\n";
    $email_message .= "Email: ".clean_string($email_from)."\n";
    $email_message .= "State: ".clean_string($ip)."\n";
    $email_message .= "Comments: ".clean_string($comments)."\n";
 
 
// create email headers
$headers = 'From: '.$email_from."\r\n".
'Reply-To: '.$email_from."\r\n" .
'X-Mailer: PHP/' . phpversion();
if (!mail($email_to, $email_subject, $email_message, $headers))
{
    echo "failed to send message";
}  
 
?>
 

The bit that made it all come together was this bit

$email_message .= "State: ".clean_string($ip)."\n";

The only thing that is not working is the random feature, im not sure why because like you suggested this bit has been added

$query = $pdo->prepare("SELECT ip FROM vincer WHERE state = ? ORDER BY rand() LIMIT 1");

Once again for all your help i could not have done it without you

Ali

[Dormilich]

not sure if case matters … check also the MySQL Manual entry for RAND():