Page 1 of 1

ORDER BY RAND not working

Posted: Thu Nov 10, 2011 5:23 am
by alibaba
Hey Everyone, hope all good. i have a problem with some php that is not randomly selecting an ip. I will try to explain my problem below. I have a state form

Code: Select 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>
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

Code: Select all

    +-------+---------------+
    | state |      ip       |
    +-------+---------------+
    | AL    | 67.100.244.74 |
    | AK    | 68.20.131.135 |
    | AZ    | 64.134.225.33 |
    +-------+---------------+
Thanks to people on the forums 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

Code: Select all

<?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@gmail.com", "database error", $e->getMessage(), "From: email@gmail.com");
    }
    ?><?php
    
    if(isset($_POST['email'])) {
         
        // EDIT THE 2 LINES BELOW AS REQUIRED
        $email_to = "1stoptutorials@gmail.com";
        $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. 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

Code: Select all

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

Re: ORDER BY RAND not working

Posted: Thu Nov 10, 2011 5:41 am
by mikeashfield
There may be an easier way that i don't know of, but this should work:

Code: Select all

// QUERY THE DATABASE AND GET THE COUNT
$result = mysql_query("SELECT ip FROM vincer WHERE state = $REQUEST['state']");
// NUMBER OF RESULTS RETURNED
$num_rows = mysql_num_rows($result);

// GENERATE A RANDOM NUMBER
$random_number = rand(1, $num_rows);
// SELECT THE IP ADDRESS FROM THE ROW NUMBER GENERATED ABOVE
$result=mysql_query("SELECT ip FROM vincer LIMIT $random_number, 1");
// ECHO THE IP ADDRESS
print_r(mysql_fetch_assoc($result));