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

mssql post state to email

P: 14
Hey Everyone hope all ok. I am needing your expertise.

I have an html form and inside of it i have a drop down option to choose a state

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>
Any-time the customer selects a a state and submits the form it goes to my mssql database and pulls an ip address releavant to the staten they choose in the html form.

Expand|Select|Wrap|Line Numbers
  1. +-----------+-------+---------------+
  2. | stateip_id| state |   user_ip     |
  3. +-----------+-------+---------------+
  4. |      1    | AL    | 67.100.244.74 |
  5. |      2    | AK    | 68.20.131.135 |
  6. |      3    | AZ    | 64.134.225.33 |
  7. +-----------+-------+---------------+
So for example, lets say they choose Alabama (AL), when they submit the form i want the code to connect to the php file and then show the ip address releavant to the state, in this case (AL). I have some php code for this thanks to this forum and another


Expand|Select|Wrap|Line Numbers
  1. <?php
  2. // visit http://php.net/pdo for more details
  3. // start error handling
  4.  
  5. try 
  6. {
  7. $Server = "00.00.000.000,0000";
  8. $User = "username";
  9. $Pass = "password";
  10. $DB = "dbname";
  11.  
  12. //connection to the database
  13. $dbhandle = mssql_connect($Server, $User, $Pass)
  14.   or die("Couldn't connect to SQL Server on $Server"); 
  15.  
  16. //select a database to work with
  17. $selected = mssql_select_db($DB, $dbhandle)
  18.   or die("Couldn't open database $DB"); 
  19.  
  20. $state = $_POST['State'];
  21.  
  22. $query = "SELECT TOP 1 user_ip
  23.               FROM state_ip
  24.               WHERE state='$state'
  25.               ORDER BY newid()";
  26.  
  27. //execute the SQL query and return records
  28. $result = mssql_query($query);
  29.  
  30. $numRows = mssql_num_rows($result); 
  31. echo "<h1>" . $numRows . " Row" . ($numRows == 1 ? "" : "s") . " Returned </h1>"; 
  32.  
  33. //display the results 
  34. while($row = mssql_fetch_array($result));
  35.  
  36. }
  37. catch (Exception $e)
  38. {
  39.   echo "sorry, there was an error.";
  40.   mail("email@gmail.com", "database error", $e->getMessage(), "From: email@gmail.com");
  41. }
  42.  
  43. if(isset($_POST['email'])) {
  44.  
  45.     // EDIT THE 2 LINES BELOW AS REQUIRED
  46.     $email_to = "email@gmail.com";
  47.     $email_subject = "This is a test";
  48.  
  49.  
  50.     function died($error) {
  51.         // your error code can go here
  52.         echo "We are very sorry, but there were error(s) found with the form you submitted. ";
  53.         echo "These errors appear below.<br /><br />";
  54.         echo $error."<br /><br />";
  55.         echo "Please go back and fix these errors.<br /><br />";
  56.         die();
  57.     }
  58.  
  59.     // validation expected data exists
  60.     if(!isset($_POST['first_name']) ||
  61.         !isset($_POST['last_name']) ||
  62.         !isset($_POST['email']) ||
  63.         !isset($_POST['what']) ||
  64.         !isset($_POST['State']) ||
  65.         !isset($_POST['comments'])) {
  66.         died('We are sorry, but there appears to be a problem with the form you submitted.');       
  67.     }
  68.  
  69.     $what = $_POST['what']; // required
  70.     $first_name = $_POST['first_name']; // required
  71.     $last_name = $_POST['last_name']; // required
  72.     $email_from = $_POST['email']; // required
  73.     $state = $_POST['State']; // not required
  74.     $comments = $_POST['comments']; // required
  75.  
  76.     $error_message = "";
  77.     $email_exp = '/^[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}$/';
  78.   if(!preg_match($email_exp,$email_from)) {
  79.     $error_message .= 'The Email Address you entered does not appear to be valid.<br />';
  80.   }
  81.     $string_exp = "/^[A-Za-z .'-]+$/";
  82.   if(!preg_match($string_exp,$first_name)) {
  83.     $error_message .= 'The First Name you entered does not appear to be valid.<br />';
  84.   }
  85.   if(!preg_match($string_exp,$last_name)) {
  86.     $error_message .= 'The Last Name you entered does not appear to be valid.<br />';
  87.   }
  88.   if(strlen($comments) < 2) {
  89.     $error_message .= 'The Comments you entered do not appear to be valid.<br />';
  90.   }
  91.   if(strlen($error_message) > 0) {
  92.     died($error_message);
  93.   }
  94.     $email_message = "Form details below.\n\n";
  95.  
  96.     function clean_string($string) {
  97.       $bad = array("content-type","bcc:","to:","cc:","href");
  98.       return str_replace($bad,"",$string);
  99.     }
  100.  
  101.     $email_message .= "First Name: ".clean_string($first_name)."\n";
  102.     $email_message .= "Last Name: ".clean_string($last_name)."\n";
  103.     $email_message .= "What: ".clean_string($what)."\n";
  104.     $email_message .= "Email: ".clean_string($email_from)."\n";
  105.     $email_message .= "State: ".clean_string($state)."\n";
  106.     $email_message .= "Comments: ".clean_string($comments)."\n";
  107. // create email headers
  108. $headers = 'From: '.$email_from."\r\n".
  109. 'Reply-To: '.$email_from."\r\n" .
  110. 'X-Mailer: PHP/' . phpversion();
  111. if (!mail($email_to, $email_subject, $email_message, $headers))
  112. {
  113.     echo "failed to send message";
  114. }  
  115.  
  116. ?>
  117.  
  118.  
  119. <!-- include your own success html here -->
  120.  
  121. Thank you for contacting us. We will be in touch with you very soon.
  122.  
  123. <?php
  124. }
  125. ?>
This above code works great, i collects the state randomly and sends it to me in an email with the rest of the form info. The problem i have is that in the email it is sending me the letters, for example it is sending me "AL or AZ or CA etc.." it is not sending me the ip address. I think it has something to do with this line of code


Expand|Select|Wrap|Line Numbers
  1. $email_message .= "State: ".clean_string($state)."\n";
mainly the $state part. I want it to select the user_ip part but can not find out how to do it. i have tryed this but it does not work

Expand|Select|Wrap|Line Numbers
  1. $email_message .= "State: ".clean_string('user_ip')."\n";
This is how it displays in an email

Expand|Select|Wrap|Line Numbers
  1. First Name: afdf
  2. Last Name: sfgsdf
  3. What: hellow
  4. Email: sd@fd.com
  5. State: AL
  6. Comments: ali alabama2
i want it to be this

Expand|Select|Wrap|Line Numbers
  1. First Name: afdf
  2. Last Name: sfgsdf
  3. What: hellow
  4. Email: sd@fd.com
  5. State: 123.456.1.21
  6. Comments: ali alabama2
Any help would be great thanks every one
Dec 1 '11 #1
Share this Question
Share on Google+
1 Reply


omerbutt
100+
P: 638
try adding echo before the $query
Expand|Select|Wrap|Line Numbers
  1. echo $query = "SELECT TOP 1 user_ip...........
  2.  
and from the browser copy the query and run it in the phpmyadmin to see if the selected ip is present there or not against that state or not
regards,
Omer Aslam
Dec 1 '11 #2

Post your reply

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