Hey all,

I have a database of schools in the United States for my website to utilize. I am incorporating a search into my website that allows a teacher to choose a state, and from that a list of cities will appear.

The way my database is organized is as follows, I have the following columns:

school_name, school_address, school_city, school_state, school_zip.

Now, since there is obviously more than one school per city, I can't simply use

$query="SELECT school_city FROM schools WHERE school_state = '$state' LIMIT 0, 500";
$result=mysql_query($query); 
$numrows=mysql_num_rows($result);


for($x=0; $x<$numrows; $x++)
{
   $school_city = mysql_result($result, $x, "school_city");
}

to list the results...I need to purge all the results of duplicates.

How would I go about doing this?

Thanks in advance!
-influx

    SELECT DISTINCT

    btw: this has nothing to do with sorting

      I also forgot to ask, is there a way to alphabetize the results?

        Originally posted by influx
        I also forgot to ask, is there a way to alphabetize the results?

        Yep. use the "order by" clause, "order by school_city".

        You didn't mention what database you are using. You should, as not all databases work exactly the same way.

        See below for the syntax for MySQL's select statement (from: http://dev.mysql.com/doc/mysql/en/select.html)

        SELECT
        [ALL | DISTINCT | DISTINCTROW ]
        [HIGH_PRIORITY]
        [STRAIGHT_JOIN]
        [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
        [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
        select_expr, ...
        [INTO OUTFILE 'file_name' export_options
        | INTO DUMPFILE 'file_name']
        [FROM table_references
        [WHERE where_definition]
        [GROUP BY {col_name | expr | position}
        [ASC | DESC], ... [WITH ROLLUP]]
        [HAVING where_definition]
        [ORDER BY {col_name | expr | position}
        [ASC | DESC] , ...]
        [LIMIT {[offset,] row_count | row_count OFFSET offset}]
        [PROCEDURE procedure_name(argument_list)]
        [FOR UPDATE | LOCK IN SHARE MODE]]

          that is really, really helpful.

          thanks.

          Now, last question:

          Once a teacher clicks on a city name, they are taken to a page with a list of schools in the city.

          to call the school names, i need to find them in the same city/state they previously chose.

          For that, would my select statement look something like:

          $query="SELECT * FROM schools WHERE school_zip = '$zip' AND school_city='$city' LIMIT 0, 200";

          Thanks

            I'll let you experiment for yourself with Google in order to discover a search string which will point you to the right site.

            Alternatively, you could just remember that the documentation for MySQL is stored at www.mysql.com. Pretty easy to remember, no?

              Originally posted by influx

              For that, would my select statement look something like:

              $query="SELECT * FROM schools WHERE school_zip = '$zip' AND school_city='$city' LIMIT 0, 200";

              That's right. it would look something like that.

              If the user has chosen a zip or a state, then maybe that AND should be an OR.

              You know, this is looking a bit like a homework assignment. You really should do your own homework...

                Write a Reply...