SELECT DISTINCT multiple field search?

Question

I'm trying to search multiple fields (zc_city, zc_zip and zc_state), matching against a single value input by the user. The three columns should be included in the results. Here's what I have now:

$q = strtolower($_GET["q"]);
if (!$q) return;

$sql = "SELECT DISTINCT zc_city AS zcity FROM search_zipcodes WHERE zc_city LIKE '$q%'";

$rsd = mysql_query($sql);
while($rs = mysql_fetch_array($rsd)) {
    $zcity = $rs['zcity'];
    echo "$zcity\n";
}

The table has the structure:

CREATE TABLE search_zipcodes (
  zc_zip VARCHAR(5),
  zc_lat FLOAT,
  zc_lon FLOAT,
  zc_city VARCHAR(80),
  zc_state CHAR(2) 
);

Answer 1

It sounds as if you simply asking for an OR:

$sql = "SELECT zc_city, zc_state, zc_zip FROM search_zipcodes WHERE zc_city LIKE '$q%' OR zc_state LIKE '$q%' OR zc_zip LIKE '$q%' ";

I'm still not sure you have stated what the primary key on this table. If it were Zip or Zip+City, then you wouldn't need to use DISTINCT. If you can have multiple entries for the same city, state and zip, then you could use a GROUP BY like so:

$sql = "SELECT zc_city, zc_state, zc_zip FROM search_zipcodes WHERE zc_city LIKE '$q%' OR zc_state LIKE '$q%' OR zc_zip LIKE '$q%' GROUP BY zc_city, zc_state, zc_zip ";

EDIT You can look up more info about getting the results from a query, but you might try something like:

while ( $rs = mysql_fetch_assoc($rsd) ){
    $array[] = $row;
}

Or

This stuffs the results into an array with each entry of the array being an array of the column values and drops the last one which will always be empty.

while ( ($resultArray[] = mysql_fetch_assoc($rsd) ) || array_pop( $resultArray ) );

Answer 2

To return rows that have a match for at least one column, use OR:

SELECT zc_city AS city, zc_state AS state, zc_zip AS zip
  FROM search_zipcodes
  WHERE zc_city LIKE :city OR zc_zip=:zip

As a prepared query, this looks like:

// connect to the DB. Should be in its own function to isolate credentials.
$db = new PDO(...);

// get the cities. Should be in a function/method to access DB (the data access layer).
$findPlace = $db->prepare("SELECT zc_city AS city, zc_state AS state, zc_zip AS zip
      FROM search_zipcodes
      WHERE zc_city LIKE :city OR zc_zip=:zip");
$places = $findPlace->execute(array(':city' => $_REQUEST['q'] . '%', 
                                    ':zip' => $_REQUEST['q']));

// display places. Should be in a method of a view class.
?>
  <ul>
    <?php foreach ($places as $place) {
       echo "<li>$place[city], $place[state] $place[zip]</li>\n";
    } ?>
  </ul>

Note there are three different tasks delineated with comments. Each should belong to a separate class, with the first two in a separate layer (the data access layer) from the third (the architecture layer, such as Model-View or Model-View-Controller).