MySQL + PHP

slcity

Hello. I am trying to build a website that will find locations based off of zip codes. I have gotten to the point where i can find X zipcodes within X miles of the posted zipcode. Those zip codes are stored in an array $zipArray

My problem is displaying them. The way my code is displaying them, it is including even the zip codes that do not have a location associated with them. I have tried several diffrent coding options still to no adval.

Here is the current coding:

$nrows = count($zipArray);

if($nrows != 0)
{
    for($j=0;$j<$nrows;$j++)
    {
    if ($nrows == 1) {
    $place = $zipOne;
    } else
     $place = $zipArray[$j];
    $sql="SELECT * FROM location WHERE zip='$place'";
$result = mysql_query($sql);
        $row = mysql_fetch_array($result);

  ?>

Results Here <br>
<?php
    }
}
?>

bpat1434

hmm... hard to follow your code... try this though (not guaranteed, just as a clean-up measure to make sure you're doing what you want):

<?php

$nrows = count($zipArray);

if($nrows > 0)
{
  for($j=0; $j<$nrows; $j++)
  {
    $place = ($nrows==1)?$zipOne:$zipArray[$j];
    $result = mysql_query("SELECT name FROM location WHERE zip='$place' LIMIT 1");
    $name = mysql_result($result, 0, 'name'); // Grabs the one bit of information
    echo $zipArray[$j].' => '.$name.'<br>';
  }
}

?>

SOmething like that should work....

~Brett

Installer

I agree with Brett--hard to follow.

But assuming your table has a place column and a zipcode column, that an empty place is stored as an empty string, and that $zipArray is an array of zipcodes you want to match with their places, maybe this code will also help:

$zipArray = array('12345', '67890', '54321');
if (!empty($zipArray)) {
    $sql = "SELECT * FROM zip_table WHERE (zipcode = '";
    for ($i = 0; $i < count($zipArray); $i++) { 
        $sql .= $zipArray[$i];
        if ($i < (count($zipArray) - 1)) { 
            $sql .= "' OR zipcode = '";
        }
    }
    $sql .= "') AND place != ''";
    $result = my_mysql_query($sql); 
    while ($row = mysql_fetch_array($result)) { 
        echo $row['place'] . ' => ' . $row['zipcode'] . '<br />';
    } 
}

It will do it all in one query, rather than a separate one for each zipcode in $zipArray.

hth

slcity

Sorry about the confusion. Let me see if i can clear this up.

Search.html
First, a user enters a 5 digit zip code and the radius (in miles) they would like to extend the search. Action=ZipSearch.php

ZipSearch.php

This file calls file phpziplocator.php. In this file $zipArray is automatically populated with all of the zip codes that are X amount of miles away from the original zip code they inputed. This part of the program is working fine.

Now on ZipSearch.php, I would like to display all locations that have a zip code (db colum: zip) that matches any zip code located in the $zipArray.

From that, i would like to display various fields in about each location (time, hours, etc), but i can do that. I am just having trouble with this because for some reason the results are being filled with empty tables from zip codes that have no matches. So say there are 50 zip codes within 15 miles of zipcode 12345, and only 2 locations within that search area - the code will still display 50 tables, only 2 with actual information populated in them.

bpat1434 - Your code still produced the blank fields. But I understand you were just cleaning up my code, and thanks it helps a bit to see it in an orderly fashion.

bpat1434

Hmm... that's very intersting... are you sure that in your database you dont' have multiple entries for the same zip? That would be the only thing I can think of.. Perhaps this as a debug measure:

<?php
var_dump($zipArray);
echo '<hr>';
$code = '';
foreach($zipArray as $zip)
{
  $sql = "SELECT * FROM `table_name` WHERE zip='$zip'";
  $rslt = mysql_query($sql) or die('Error in query: '.mysql_error());
  $code .= mysql_result($rslt, 0, 'zip');
  $code .= ' --|-- '; // A simple seperator
}
echo $code;
?>

See what that gives you. We're checking the values in zipArray with the var_dump function, and we're only looping through each item in the zipArray (rather than count..).

~Brett

Installer

Maybe you should post your database scheme. If you have a table with a zipcode column and a location column, and some of the rows have an empty location field, I don't see how the code I posted (or a precise adaptation of it) would return any rows that have an empty location. In fact I'm sure it won't. How are you storing the locations, empty and otherwise?