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,3 0,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("admin@example.org", "database error", $e->getMessage(), "From: noreply@example.org");
}

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", [B]$login[/B], [B]$password[/B]);

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("email@gmail.com", "database error", $e->getMessage(), "From: email@gmail.com");

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

Thanks

Ali