0

So I've been working on a page to locate shops based on zip code and mile distance. I used this tutorial as a walk-through http://htmlcampus.com/build-a-zip-code-store-locator-application-in-php/

Now I've changed a couple of things like the mySQL table is called locations instead of zip_codes. I've tested it and it connects to the database and all, but it just says that no results were found, even when I enter in an exact zip code of one of the shops (90278 or 92602). http://bit.ly/Me2d1M

Below is my code for the page (password left out). I hope someone could spot what might be wrong, something I missed.

<?php

// Create page variables
$r = NULL;
$z = NULL;
$shops = NULL;
$Errors = NULL;

// Establish DB connection
mysql_connect ('localhost', 'acq-admin', 'password') or die(mysql_error());
mysql_select_db ('autocareq') or die(mysql_error());

// Declare page functions
function Dist ($lat_A, $long_A, $lat_B, $long_B) {

  $distance = sin(deg2rad($lat_A))
      * sin(deg2rad($lat_B))
      + cos(deg2rad($lat_A))
      * cos(deg2rad($lat_B))
      * cos(deg2rad($long_A - $long_B));

  $distance = (rad2deg(acos($distance))) * 69.09;
  return $distance;

}

### Handle form if submitted
if (isset ($_POST['submitted'])) {

  // Validate Zip code field
  if (!empty ($_POST['zip']) && is_numeric ($_POST['zip'])) {

    $z = (int)$_POST['zip'];

    // Verify zip code exists
    $query = "SELECT lat, lon FROM locations WHERE zip = '$z'";
    $result = mysql_query ($query);

    if (mysql_num_rows ($result) == 1) {
      $zip = mysql_fetch_assoc ($result);
    } else {
      $Errors = '<p>The zip code you entered was not found!</p>';
    }

  }

  // Validate radius field
  if (isset ($_POST['radius']) && is_numeric ($_POST['radius'])) {
    $r = (int)$_POST['radius'];
  }

  // Proceed if no errors were found
  if ($r && $z) {

    // Retrieve coordinates of the shops
    $shops = array();
    $query = "SELECT name, address, city, state, zip, phone, lat, lon
    FROM dealerships
    INNER JOIN locations
    ON dealerships.zip = locations.zip";
    $result = mysql_query ($query);

    // Go through and check all shops
    while ($row = mysql_fetch_assoc ($result)) {

      // Separate closest shops
      $distance = Dist ($row['lat'], $row['lon'], $zip['lat'], $zip['lon']);

      // Check if shop is in radius
      if ($distance <= $r) {

        $shops[] = array (
          'name'      => $row['name'],
          'address'   => $row['address'],
          'state'     => $row['state'],
          'city'      => $row['city'],
          'zip'       => $row['zip'],
          'phone'     => $row['phone']
        );

      }

    }

  } else {
    $Errors = ($Errors) ? $Errors : '<p>Errors were found please try again!</p>';
  }

}

?><html>
<head>
<title>Pick Your Shop</title>
</head>
<body>
<form action="" method="post">
  <p>Enter your zip code below to find the nearest shop</p>
  <?php echo ($Errors) ? $Errors : ''; ?>
  <div>
    <label>Zip:</label>
    <input name="zip" type="text" size="10" maxlength="5" />
  </div>
  <div>
    <label>Search Area:</label>
    <select name="radius" id="radius">
      <option value="5">5 mi.</option>
      <option value="10">10 mi.</option>
      <option value="15">15 mi.</option>
      <option value="20">20 mi.</option>
    </select>
  </div>
  <div>
    <input type="hidden" name="submitted" value="submitted" />
    <input type="submit" value="Submit" />
  </div>
</form>

<?php

if (isset ($shops)) {

  if (!empty ($shops)) {

    echo '<p><strong>' . count ($shops) . ' results were found.</strong></p>';
    foreach ($shops as $value) {

      echo '<p><strong>' . $value['name'] . '</strong><br />';
      echo $value['address'] . '<br />';
      echo $value['city'] . ', ' . $value['state'] . ' ' . $value['zip'];
      echo ' <a target="_blank" href="http://maps.google.com/maps?q=',
      $value['address'], ' ',
      $value['city'], ', ',
      $value['state'], ' ',
      $value['zip'],
      '">Map this location</a><br />';
      echo 'Phone: ' . $value['phone'];
      echo '</p>';

    }

  } else {
    echo '<p><strong>No results found</strong></p>';
    echo '<p><strong>' . $z . '</strong><br />';
  }

}

?>
</body>
</html>

Edited by Dani: Fixed formatting

2
Contributors
7
Replies
10
Views
5 Years
Discussion Span
Last Post by cereal
0

I think the problem is in the second query, since zip is in both tables you should get an error like Column 'zip' in field list is ambiguous so try to add or die(mysql_error()) to check if this error happens, or just change the query to:

$query = "SELECT name, address, city, state, locations.zip, phone, lat, lon
FROM dealerships
INNER JOIN locations
ON dealerships.zip = locations.zip";

bye!

Edited by cereal

0

I did that and you were right. I needed to tell it which table for zip, city, and state. Now it works! Hooray! Thanks :)

0

Now I have a new task to accomplish. I want to load the results on the page without having to reload the page again using ajax. I'm new to ajax though. Below is what I have, when it submits, the page just reloads without any results.

Also, if I put the form's action to search.php, this works, so it's the ajax not working right or something.

JQuery at the top of the main PHP page with the form

<!-- JQuery Library Load -->  
<script src="../js/jquery-1.7.2.min.js"></script>

<script type="text/javascript">
$(function() {

    $("#zip-search-button").click(function() {
        // getting the form values
        var zipString  = $("#thezip").val();
        var radiusString = $("#radius").val();

        // forming the queryString
        var data = 'search='+ zipString + radius;

        // if searchString is not empty
        if(searchString) {
            // ajax call
            $.ajax({
                type: "POST",
                url: "search.php",
                data: data,
                beforeSend: function(html) { // this happens before actual call
                    $("#results").html(''); 
                    $("#searchresults").show();
                    $(".word").html(searchString);
               },
               success: function(html){ // this happens after we get results
                    $("#results").show();
                    $("#results").append(html);
              }
            });    
        }
        return false;
    });
});
</script>

The form on the main PHP page.

<div id="pick-shop">
    <form action="" method="post">
        <div id="zip-dropdown">
         <select class="sexydropdown" name="radius" id="radius">
            <option value="5">Within 5 Miles From...</option>
            <option value="10">Within 10 Miles From...</option>
            <option value="15" selected>Within 15 Miles From...</option>
            <option value="20">Within 20 Miles From...</option>
            <option value="25">Within 25 Miles From...</option>
            <option value="30">Within 30 Miles From...</option>
        </select>
        </div>
        <div id="zip-container">
        <input type="text" name="zip" id="thezip" required="required" value="" placeholder="Enter Your Zip Code" />
        <input type="hidden" name="submitted" value="submitted" />
        <input type="submit" value="Submit" id="zip-search-button" />
        </div>
    </form>
</div>


<div id="searchresults">Search results for <span class="word"></span></div>
<div id="results" class="update">
</div>

The php processing page (search.php)

<?php

// Create page variables
$r = NULL;
$z = NULL;
$shops = NULL;
$Errors = NULL;

// Establish DB connection
mysql_connect ('localhost', 'admin', 'thepassword') or die(mysql_error());
mysql_select_db ('database') or die(mysql_error());

// Declare page functions
function Dist ($lat_A, $long_A, $lat_B, $long_B) {

  $distance = sin(deg2rad($lat_A))
      * sin(deg2rad($lat_B))
      + cos(deg2rad($lat_A))
      * cos(deg2rad($lat_B))
      * cos(deg2rad($long_A - $long_B));

  $distance = (rad2deg(acos($distance))) * 69.09;
  return $distance;

}

### Handle form if submitted
if (isset ($_POST['submitted'])) {

  // Validate Zip code field
  if (!empty ($_POST['zip']) && is_numeric ($_POST['zip'])) {

    $z = mysql_real_escape_string((int)$_POST['zip']);

    // Verify zip code exists
    $query = "SELECT lat, lon FROM locations WHERE zip = '$z'";
    $result = mysql_query ($query);

    if (mysql_num_rows ($result) == 1) {
      $zip = mysql_fetch_assoc ($result);
    } else {
      $Errors = '<p>The zip code you entered was not found!</p>';
    }

  }

  // Validate radius field
  if (isset ($_POST['radius']) && is_numeric ($_POST['radius'])) {
    $r = (int)$_POST['radius'];
  }

  // Proceed if no errors were found
  if ($r && $z) {

    // Retrieve coordinates of the shops
    $shops = array();
    $query = "SELECT name, address, dealerships.city, dealerships.state, locations.zip, phone, lat, lon
    FROM dealerships
    INNER JOIN locations
    ON dealerships.zip = locations.zip" or die(mysql_error());
    $result = mysql_query ($query) or die(mysql_error());

    // Go through and check all shops
    while ($row = mysql_fetch_assoc ($result)) {

      // Separate closest shops
      $distance = Dist ($row['lat'], $row['lon'], $zip['lat'], $zip['lon']);

      // Check if shop is in radius
      if ($distance <= $r) {

        $shops[] = array (
          'name'      => $row['name'],
          'address'   => $row['address'],
          'state'     => $row['state'],
          'city'      => $row['city'],
          'zip'       => $row['zip'],
          'phone'     => $row['phone']
        );

      }

    }

  } else {
    $Errors = ($Errors) ? $Errors : '<p>Errors were found please try again!</p>';
  }

}


// Any PHP Database Errors -->
echo ($Errors) ? $Errors : '';


// Let's do this! Show Results, Baby! -->
if (isset ($shops)) {

  if (!empty ($shops)) {

    echo '<p><strong>' . count ($shops) . ' results were found.</strong></p>';
    foreach ($shops as $value) {

      echo '<p><strong>' . $value['name'] . '</strong><br />';
      echo $value['address'] . '<br />';
      echo $value['city'] . ', ' . $value['state'] . ' ' . $value['zip'];
      echo '&nbsp;<a target="_blank" href="http://maps.google.com/maps?q=',
      $value['address'], ' ',
      $value['city'], ', ',
      $value['state'], ' ',
      $value['zip'],
      '">Map this location</a><br />';
      echo 'Phone: ' . $value['phone'];
      echo '</p>';

    }

  } else {
    echo '<p><strong>No results found</strong></p>';
  }

}
?>
0

Set an id in your form, in this example is myform:

<form action="" method="post" id="myform">

And change your jquery with this:

<script type="text/javascript">
$(document).ready(function(){

    $('#myform').submit(function(e) {
        // prevent reload of page
        e.preventDefault();

        if($('#thezip').length > 0) {
            // ajax call
            $.ajax({
                type: "POST",
                url: "search.php",
                data: $('#myform').serialize(),
                timeout: 30000, // 30 seconds
                cache: false,
                beforeSend: function(html) { // this happens before actual call
                    $("#results").html(''); 
                    $("#searchresults").show();
                    $(".word").html(searchString);
                },
                error: function(){
                    $("#results").hide().html('server not responding');
                },
                success: function(msg){ // this happens after we get results
                    $("#results").html(msg);
                }
            });

            // alternative method
            // $.post('search.php',$('#myform').serialize(),function(data) {$("#result").html(data)});
        }

        else
        {
            return false;
        }
    }); 
});
</script>

bye.

Edited by cereal

0

Looks like it would work, but when I tried this out, nothing happens when I click the submit button of the form. :(

0

Can you post your updated code or upload it to your previous link?
In my test this is working, so maybe something went wrong.. you can also try to put a log inside the if statement to see if jquery it's executed:

console.log('hello!');

then just open the Javascript Console of your browser and run the script, through that you will also see what kind of error happens. Consider also, that the alternative method I posted searches for wrong id #result instead of #results as in your code.. my mistake.

Edited by cereal

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.