By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,952 Members | 1,725 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,952 IT Pros & Developers. It's quick & easy.

send data from sql to email

P: 14
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:

Expand|Select|Wrap|Line Numbers
  1. <select name="State">
  2. <option value="0" selected="selected">Select a State</option>
  3. <option value="AL">Alabama</option>
  4. <option value="AK">Alaska</option>
  5. <option value="AZ">Arizona</option>
  6. <option value="AR">Arkansas</option>
  7.    etc.....
  8. </select>
  9.  
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
Oct 24 '11 #1
Share this Question
Share on Google+
21 Replies


Dormilich
Expert Mod 5K+
P: 8,639
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
Oct 24 '11 #2

P: 14
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

Expand|Select|Wrap|Line Numbers
  1. <select name="State">
  2. <option value="0" selected="selected">Select a State</option>
  3. <option value="AL">Alabama</option>
  4. <option value="AK">Alaska</option>
  5. <option value="AZ">Arizona</option>
  6. <option value="AR">Arkansas</option>
  7.    etc.....
  8. </select>
  9.  
  10. <?php
  11. $state = $_POST['State']; // User selected Alaska (AK) so $_POST['State'] = 'AK'
  12. $query = "SELECT state, ip FROM ipaddresses WHERE state='" . mysql_real_escape_string($state) . "'";
  13. $result = mysql_query($query, $link_resource);
  14. $row = mysql_fetch_assoc($result);
  15.  
  16. // ip-address belonging to Alaska (AK) turns out to be 68.20.131.135 as it's stored in the same row as AK
  17. ?>
  18.  
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

Expand|Select|Wrap|Line Numbers
  1. 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
  2.  
  3. 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
  4.  
  5. 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
  6.  
  7. 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

Expand|Select|Wrap|Line Numbers
  1. // Get the state that the user selected
  2. $selectedState = mysql_real_escape_string($_REQUEST['State']);
  3.  
  4. // Build a query
  5. $query = "SELECT ip
  6.           FROM table_name
  7.           WHERE state = '$selectedState'
  8.           ORDER BY rand()
  9.           LIMIT 1";
  10.  
  11. // Execute it
  12. $result = mysql_query($query);
  13.  
  14. // Fetch the result as an array
  15. $row = mysql_fetch_assoc($result);
  16.  
  17. // The IP address you want is now stored in $row['ip']
  18.  
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
Nov 3 '11 #3

Dormilich
Expert Mod 5K+
P: 8,639
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:
Expand|Select|Wrap|Line Numbers
  1. <?php
  2. // visit http://php.net/pdo for more details
  3.  
  4. // start error handling
  5. try 
  6. {
  7.   // connect
  8.   $pdo = new PDO("mysql:host=localhost;dbname=your_db_table", $login, $password);
  9.   // enable error handling through exceptions
  10.   $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  11.   // create safe query
  12.   $query = $pdo->prepare("SELECT ip FROM table_name WHERE state = ? ORDER BY rand() LIMIT 1");
  13.   // pass data & execute query (since the data are of string type
  14.   // and therefore can be passed in this lazy way)
  15.   $query->execute(array($_POST['State']));
  16.   // get value
  17.   $ip = $query->fetchColumn();
  18.   // print out the IP address using $ip
  19. }
  20. catch (Exception $e)
  21. {
  22.   echo "sorry, there was an error.";
  23.   mail("admin@example.org", "database error", $e->getMessage(), "From: noreply@example.org");
  24. }
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
Nov 3 '11 #4

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

Ali
Nov 3 '11 #5

Dormilich
Expert Mod 5K+
P: 8,639
Expand|Select|Wrap|Line Numbers
  1. $pdo = new PDO("mysql:host=localhost;dbname=your_db_table", $login$password);
like that?
Nov 3 '11 #6

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

Expand|Select|Wrap|Line Numbers
  1. <select name="State">
  2. <option value="0" selected="selected">Select a State</option>
  3. <option value="AL">Alabama</option>
  4. <option value="AK">Alaska</option>
  5. <option value="AZ">Arizona</option>
  6. <option value="AR">Arkansas</option>
  7.    etc.....
  8. </select>
  9.  
Thanks for everything

Ali
Nov 3 '11 #7

Dormilich
Expert Mod 5K+
P: 8,639
for what purpose?
Nov 3 '11 #8

P: 14
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:

Expand|Select|Wrap|Line Numbers
  1. $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

Expand|Select|Wrap|Line Numbers
  1. <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

Expand|Select|Wrap|Line Numbers
  1. 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
Nov 3 '11 #9

Dormilich
Expert Mod 5K+
P: 8,639
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.
Nov 3 '11 #10

P: 14
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
Nov 3 '11 #11

Dormilich
Expert Mod 5K+
P: 8,639
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?
Nov 3 '11 #12

P: 14
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

Expand|Select|Wrap|Line Numbers
  1. 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
Nov 4 '11 #13

Dormilich
Expert Mod 5K+
P: 8,639
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)
Nov 4 '11 #14

P: 14
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
Expand|Select|Wrap|Line Numbers
  1.  mail("email@gmail.com", "database error", $e->getMessage(), "From: email@gmail.com");
  2.  
did you check the spam folder?
Yes it is not in there

Thanks

Ali
Nov 4 '11 #15

Dormilich
Expert Mod 5K+
P: 8,639
er, if there are no errors, you don’t receive an error mail …
Nov 4 '11 #16

P: 14
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

Expand|Select|Wrap|Line Numbers
  1. <?php
  2. if(isset($_POST['email'])) {
  3.  
  4.     // EDIT THE 2 LINES BELOW AS REQUIRED
  5.     $email_to = "1stoptutorials@gmail.com";
  6.     $email_subject = "This is a test";
  7.  
  8.  
  9.     function died($error) {
  10.         // your error code can go here
  11.         echo "We are very sorry, but there were error(s) found with the form you submitted. ";
  12.         echo "These errors appear below.<br /><br />";
  13.         echo $error."<br /><br />";
  14.         echo "Please go back and fix these errors.<br /><br />";
  15.         die();
  16.     }
  17.  
  18.     // validation expected data exists
  19.     if(!isset($_POST['first_name']) ||
  20.         !isset($_POST['last_name']) ||
  21.         !isset($_POST['email']) ||
  22.         !isset($_POST['State']) ||
  23.         !isset($_POST['comments'])) {
  24.         died('We are sorry, but there appears to be a problem with the form you submitted.');       
  25.     }
  26.  
  27.     $first_name = $_POST['first_name']; // required
  28.     $last_name = $_POST['last_name']; // required
  29.     $email_from = $_POST['email']; // required
  30.     $state = $_POST['State']; // not required
  31.     $comments = $_POST['comments']; // required
  32.  
  33.     $error_message = "";
  34.     $email_exp = '/^[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}$/';
  35.   if(!preg_match($email_exp,$email_from)) {
  36.     $error_message .= 'The Email Address you entered does not appear to be valid.<br />';
  37.   }
  38.     $string_exp = "/^[A-Za-z .'-]+$/";
  39.   if(!preg_match($string_exp,$first_name)) {
  40.     $error_message .= 'The First Name you entered does not appear to be valid.<br />';
  41.   }
  42.   if(!preg_match($string_exp,$last_name)) {
  43.     $error_message .= 'The Last Name you entered does not appear to be valid.<br />';
  44.   }
  45.   if(strlen($comments) < 2) {
  46.     $error_message .= 'The Comments you entered do not appear to be valid.<br />';
  47.   }
  48.   if(strlen($error_message) > 0) {
  49.     died($error_message);
  50.   }
  51.     $email_message = "Form details below.\n\n";
  52.  
  53.     function clean_string($string) {
  54.       $bad = array("content-type","bcc:","to:","cc:","href");
  55.       return str_replace($bad,"",$string);
  56.     }
  57.  
  58.     $email_message .= "First Name: ".clean_string($first_name)."\n";
  59.     $email_message .= "Last Name: ".clean_string($last_name)."\n";
  60.     $email_message .= "Email: ".clean_string($email_from)."\n";
  61.     $email_message .= "State: ".clean_string($state)."\n";
  62.     $email_message .= "Comments: ".clean_string($comments)."\n";
  63.  
  64.  
  65. // create email headers
  66. $headers = 'From: '.$email_from."\r\n".
  67. 'Reply-To: '.$email_from."\r\n" .
  68. 'X-Mailer: PHP/' . phpversion();
  69. @mail($email_to, $email_subject, $email_message, $headers);  
  70. ?>
  71.  
  72. <!-- include your own success html here -->
  73.  
  74. Thank you for contacting us. We will be in touch with you very soon.
  75.  
  76. <?php
  77. }
  78. ?>
  79.  
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
Nov 4 '11 #17

Dormilich
Expert Mod 5K+
P: 8,639
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
Expand|Select|Wrap|Line Numbers
  1. if (!mail(/* message etc. */))
  2. {
  3.     echo "failed to send message";
  4. }
Nov 4 '11 #18

P: 14
This is how my php code looks

Expand|Select|Wrap|Line Numbers
  1.  
  2. <?php
  3. // visit http://php.net/pdo for more details
  4. // start error handling
  5.  
  6. try 
  7. {
  8.   // connect
  9.   $pdo = new PDO('mysql:host=localhost;dbname=name', 'user', 'pass');
  10.   // enable error handling through exceptions
  11.   $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  12.   // create safe query
  13.   $query = $pdo->prepare("SELECT ip FROM vincer WHERE state = ? ORDER BY rand() LIMIT 1");
  14.   // pass data & execute query (since the data are of string type
  15.   // and therefore can be passed in this lazy way)
  16.   $query->execute(array($_POST['State']));
  17.   // get value
  18.   $ip = $query->fetchColumn();
  19.   // print out the IP address using $ip
  20. }
  21. catch (Exception $e)
  22. {
  23.   echo "sorry, there was an error.";
  24.   mail("1stoptutorials@gmail.com", "database error", $e->getMessage(), "From: 1stoptutorials@gmail.com");
  25. }
  26.  
  27. if(isset($_POST['email'])) {
  28.  
  29.     // EDIT THE 2 LINES BELOW AS REQUIRED
  30.     $email_to = "1stoptutorials@gmail.com";
  31.     $email_subject = "This is a test";
  32.  
  33.  
  34.     function died($error) {
  35.         // your error code can go here
  36.         echo "We are very sorry, but there were error(s) found with the form you submitted. ";
  37.         echo "These errors appear below.<br /><br />";
  38.         echo $error."<br /><br />";
  39.         echo "Please go back and fix these errors.<br /><br />";
  40.         die();
  41.     }
  42.  
  43.     // validation expected data exists
  44.     if(!isset($_POST['first_name']) ||
  45.         !isset($_POST['last_name']) ||
  46.         !isset($_POST['email']) ||
  47.         !isset($_POST['State']) ||
  48.         !isset($_POST['comments'])) {
  49.         died('We are sorry, but there appears to be a problem with the form you submitted.');       
  50.     }
  51.  
  52.     $first_name = $_POST['first_name']; // required
  53.     $last_name = $_POST['last_name']; // required
  54.     $email_from = $_POST['email']; // required
  55.     $state = $_POST['State']; // not required
  56.     $comments = $_POST['comments']; // required
  57.  
  58.     $error_message = "";
  59.     $email_exp = '/^[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}$/';
  60.   if(!preg_match($email_exp,$email_from)) {
  61.     $error_message .= 'The Email Address you entered does not appear to be valid.<br />';
  62.   }
  63.     $string_exp = "/^[A-Za-z .'-]+$/";
  64.   if(!preg_match($string_exp,$first_name)) {
  65.     $error_message .= 'The First Name you entered does not appear to be valid.<br />';
  66.   }
  67.   if(!preg_match($string_exp,$last_name)) {
  68.     $error_message .= 'The Last Name you entered does not appear to be valid.<br />';
  69.   }
  70.   if(strlen($comments) < 2) {
  71.     $error_message .= 'The Comments you entered do not appear to be valid.<br />';
  72.   }
  73.   if(strlen($error_message) > 0) {
  74.     died($error_message);
  75.   }
  76.     $email_message = "Form details below.\n\n";
  77.  
  78.     function clean_string($string) {
  79.       $bad = array("content-type","bcc:","to:","cc:","href");
  80.       return str_replace($bad,"",$string);
  81.     }
  82.  
  83.     $email_message .= "First Name: ".clean_string($first_name)."\n";
  84.     $email_message .= "Last Name: ".clean_string($last_name)."\n";
  85.     $email_message .= "Email: ".clean_string($email_from)."\n";
  86.     $email_message .= "State: ".clean_string($state)."\n";
  87.     $email_message .= "Comments: ".clean_string($comments)."\n";
  88.  
  89.  
  90. // create email headers
  91. $headers = 'From: '.$email_from."\r\n".
  92. 'Reply-To: '.$email_from."\r\n" .
  93. 'X-Mailer: PHP/' . phpversion();
  94. if (!mail($email_to, $email_subject, $email_message, $headers))
  95. {
  96.     echo "failed to send message";
  97. }  
  98.  
  99. ?>
  100.  
  101.  
  102. <!-- include your own success html here -->
  103.  
  104. Thank you for contacting us. We will be in touch with you very soon.
  105.  
  106. <?php
  107. }
  108. ?>
  109.  
When i use this i receive an email and this is how it comes out

Expand|Select|Wrap|Line Numbers
  1. First Name: afdf
  2. Last Name: sfgsdf
  3. Email: sd@fd.com
  4. State: AZ
  5. Comments: dsfsda
  6.  
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
Nov 4 '11 #19

Dormilich
Expert Mod 5K+
P: 8,639
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);
Nov 4 '11 #20

P: 14
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

Expand|Select|Wrap|Line Numbers
  1.  
  2. <?php
  3. // visit http://php.net/pdo for more details
  4. // start error handling
  5.  
  6. try 
  7. {
  8.   // connect
  9.   $pdo = new PDO('mysql:host=localhost;dbname=name', 'user', 'pass');
  10.   // enable error handling through exceptions
  11.   $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  12.   // create safe query
  13.   $query = $pdo->prepare("SELECT ip FROM vincer WHERE state = ? ORDER BY rand() LIMIT 1");
  14.   // pass data & execute query (since the data are of string type
  15.   // and therefore can be passed in this lazy way)
  16.   $query->execute(array($_POST['State']));
  17.   // get value
  18.   $ip = $query->fetchColumn();
  19.   // print out the IP address using $ip
  20. }
  21. catch (Exception $e)
  22. {
  23.   echo "sorry, there was an error.";
  24.   mail("email@gmail.com", "database error", $e->getMessage(), "From: email@gmail.com");
  25. }
  26.  
  27. if(isset($_POST['email'])) {
  28.  
  29.     // EDIT THE 2 LINES BELOW AS REQUIRED
  30.     $email_to = "1stoptutorials@gmail.com";
  31.     $email_subject = "This is a test";
  32.  
  33.  
  34.     function died($error) {
  35.         // your error code can go here
  36.         echo "We are very sorry, but there were error(s) found with the form you submitted. ";
  37.         echo "These errors appear below.<br /><br />";
  38.         echo $error."<br /><br />";
  39.         echo "Please go back and fix these errors.<br /><br />";
  40.         die();
  41.     }
  42.  
  43.     // validation expected data exists
  44.     if(!isset($_POST['first_name']) ||
  45.         !isset($_POST['last_name']) ||
  46.         !isset($_POST['email']) ||
  47.         !isset($_POST['State']) ||
  48.         !isset($_POST['comments'])) {
  49.         died('We are sorry, but there appears to be a problem with the form you submitted.');       
  50.     }
  51.  
  52.     $first_name = $_POST['first_name']; // required
  53.     $last_name = $_POST['last_name']; // required
  54.     $email_from = $_POST['email']; // required
  55.     $state = $_POST['State']; // not required
  56.     $comments = $_POST['comments']; // required
  57.  
  58.     $error_message = "";
  59.     $email_exp = '/^[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}$/';
  60.   if(!preg_match($email_exp,$email_from)) {
  61.     $error_message .= 'The Email Address you entered does not appear to be valid.<br />';
  62.   }
  63.     $string_exp = "/^[A-Za-z .'-]+$/";
  64.   if(!preg_match($string_exp,$first_name)) {
  65.     $error_message .= 'The First Name you entered does not appear to be valid.<br />';
  66.   }
  67.   if(!preg_match($string_exp,$last_name)) {
  68.     $error_message .= 'The Last Name you entered does not appear to be valid.<br />';
  69.   }
  70.   if(strlen($comments) < 2) {
  71.     $error_message .= 'The Comments you entered do not appear to be valid.<br />';
  72.   }
  73.   if(strlen($error_message) > 0) {
  74.     died($error_message);
  75.   }
  76.     $email_message = "Form details below.\n\n";
  77.  
  78.     function clean_string($string) {
  79.       $bad = array("content-type","bcc:","to:","cc:","href");
  80.       return str_replace($bad,"",$string);
  81.     }
  82.  
  83.     $email_message .= "First Name: ".clean_string($first_name)."\n";
  84.     $email_message .= "Last Name: ".clean_string($last_name)."\n";
  85.     $email_message .= "Email: ".clean_string($email_from)."\n";
  86.     $email_message .= "State: ".clean_string($ip)."\n";
  87.     $email_message .= "Comments: ".clean_string($comments)."\n";
  88.  
  89.  
  90. // create email headers
  91. $headers = 'From: '.$email_from."\r\n".
  92. 'Reply-To: '.$email_from."\r\n" .
  93. 'X-Mailer: PHP/' . phpversion();
  94. if (!mail($email_to, $email_subject, $email_message, $headers))
  95. {
  96.     echo "failed to send message";
  97. }  
  98.  
  99. ?>
  100.  
The bit that made it all come together was this bit

Expand|Select|Wrap|Line Numbers
  1. $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

Expand|Select|Wrap|Line Numbers
  1. $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
Nov 4 '11 #21

Dormilich
Expert Mod 5K+
P: 8,639
not sure if case matters … check also the MySQL Manual entry for RAND():
Nov 4 '11 #22

Post your reply

Sign in to post your reply or Sign up for a free account.