SELECT * FROM table ORDER BY need help here

use values from an array to sort an SQL query

[smileybri]

Like some others here everything I know about PHP is self taught. There are many resources and I have a bunch of books, but I can't seem to find how to do this...

I have a table of locations
(location_id, name, address, city_id, state, zip, special_info, section_id)

and a table of cities in my county
(city_id, city, abbr)

I want to query locations and order the results by city, but as you can see city is in a different table.

How should I approach this?

[mcibor]

Your sql question:

SELECT locations.location_id, locations.name, locations.address, locations.city_id, locations.state, locations.zip, locations.special_info, locations.section_id, cities.city, cities.abbr FROM locations, cities WHERE locations.city_id = cities.city_id ORDER BY cities.city;

locations is the name of the 1st table and cities is the name of the 2ns table.

The rule of this statement is following:
SELECT a.name, b.place FROM a,b WHERE a.id = b.id ORDER BY a.name;

a.name means a-table.b-field

Hope this helps you
Michal Cibor

[smileybri]

That gets me started, thanks, but it is not getting the whole set so the "WHERE locations.city_id = cities.city_id" part must be limiting the results.

I will play around as there is plenty I learned form your code.

Is it possible to sort the this in PHP instead of trying to sort it in SQL?

[mcibor]

Yes, this query is limiting the answer to only known cities. If you have in location a city_id that is not in cities, then it won't be shown.

However to say the truth I myself use the redundant, but easier way - I store the city name and city_id in table locations. It doesn't make my db much bigger, and the query is much simpler.

With php I don't recomend that. it's possible, but hard to achieve. Mysql is much simpler.

Moreover you must have a city in cities before you put it's id into locations, otherwise it would be without sense. So in working condition you get all required results.

Best regards
Michal Cibor

And welcome to WebmasterWorld!

[edited by: mcibor at 9:43 pm (utc) on May 28, 2005]

[smileybri]

Yes.

$sql = 'SELECT locations.location_id, locations.name, locations.address, locations.city_id, locations.state, locations.zip, locations.special_info, locations.section_id, cities.city, cities.abbr FROM locations, cities ORDER BY cities.city;

That seems to be what I want. Thank you very much for your help.

[mcibor]

This last query of yours isn't working, because you retrieve the value first from first table and for that data you retrieve all data from the second table, and so on, and so on.

Eg: table a:(0, London; 1, Berlin; 2, Warsaw) table b:(0, John; 1, Hans)
And you want answer: Hans, Berlin; John, London
To do that use my query, whether your query will give:
Berlin, Hans; Berlin, John; London, Hans; London, John; Warsaw, Hans; Warsaw, John

You see the difference?
Best regards!
Michal Cibor

[smileybri]

I see what you mean.

Thanks so much for your help.