ORDER BY RAND not working

Question

hope all is well. I have a problem with some php that does not randomly select 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> 

At any time, the client selects state a and submits the form, which it submits to my database, and pulls the release address of the ip address into 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 the people on this forum, I have a php code that collects the ip address when the form is submitted and sent to my email address. Excellent. 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(" [e-mail] ", "database error", $e->getMessage(), "From: [e-mail] "); } ?><?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($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 does not do is grab a random ip from state. For each state, i.e. AL, AK, AZ, etc., I have about 150 different IP addresses. So, let's say someone chooses the state of Alabama (AL), I want him to randomly select ip from the database, which is on the same line as AL. It picks up the ip address in order, but it always sends me the same ip for AL.

According to the code, he should do it because ORDER BY RAND is there

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

I asked, and some people suggested that I needed another column in my table called id, so this 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 an identifier, so I can randomly pull in ip. Does anyone know which PHP code I need to add this identifier for this to work. Any help would be greatly appreciated

Thank you all

Ali

Answer 1

RAND() will give different values ​​each time.
If you do not want this, you can specify a fixed initial value.
If you are paranoid, you can give a different initial value each time.

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

If there are very few different ip in the state, the probability of seeing the same ip increases, as the number of ip approaches one.
If there is only 1, then that will be returned, of course.

You can see the RAND() that was used when changing the request:

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