ORDER BY RAND not working

Question

hope all good. i have a problem with some php that is not randomly selecting an ip. I will try to explain.

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

Any-time the customer selects a a state and submits the form it goes to my database and pulls an ip address releavant to the state. This is what my database looks like

+-------+---------------+
| state |      ip       |
+-------+---------------+
| AL    | 67.100.244.74 |
| AK    | 68.20.131.135 |
| AZ    | 64.134.225.33 |
+-------+---------------+

Thanks to people on this forum i have some php code that is collecting the ip address when the form is submitted and it gets sent to my email. Perfect. here is the code

<?php
// visit http://php.net/pdo for more details
// start error handling

try 
{
  // connect
  $pdo = new PDO('mysql:host=localhost;dbname=name', 'dbuser', '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("[email protected]", "database error", $e->getMessage(), "From: [email protected]");
}
?><?php

if(isset($_POST['email'])) {

    // EDIT THE 2 LINES BELOW AS REQUIRED
    $email_to = "[email protected]";
    $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 only thing it is not doing is grabbing a random ip from the state. For each state ie AL,AK,AZ etc.. i have about 150 different ip addresses. So lets say someone selects the state of Alabama (AL), i want it to randomly choose and ip from the database that is in the same row as AL. It is picking up the ip address ok, but it is always sending me the same ip for AL.

According to the code it should be doing this because the ORDER BY RAND is in there

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

I asked around and some people suggested i need another column in my table with a name of id, so this is what it should look like

-------+-------+---------------+
| id   | state |      ip       |
+------+-------+---------------+
|  1   | AL    | 67.100.244.74 |
|  2   | AK    | 68.20.131.135 |
|  3   | AZ    | 64.134.225.33 |
+------+-------+---------------+

They say i need the id so i can randomly pull in the ip. Does anyone know what php code i need to add this id to make this all work. Any help would be much appreciated

Thanks Everyone

Ali

Answer 1

RAND() will give different values every time.
If you don't want this you can give a fixed seed value.
If you're paranoid, you can supply a different seed value every time.

SELECT ip FROM vincer WHERE state = ? ORDER BY RAND(UNIX_TIMESTAMP(NOW())) LIMIT 1  

If there's are very few different ip's in a state the chances of seeing the same ip go up as number of ip's approaches one.
If there's only 1 then that will be returned of course.

You can see the RAND() that's used by changing the query to:

SELECT @rand:= RAND() as rand, ip 
FROM vincer 
WHERE state = ? 
ORDER BY rand 
LIMIT 1