Page 1 of 1

Two level sort

Posted: Wed Nov 16, 2011 12:13 pm
by PaulR
Hello;

Here is my problem: I have 2 Tables for wich there is a many 2 many link that i tought would be best is provided by a 3rd table.

In the 1st table i have SCHOOLS and in the other i have PUBLIC TRANSPORT STOPS.

any School my have more than one Transport stops associated to them and same goes with
Transport stops who may have more than one shool associated to them
thus the 3rd Table _M2M

I would like a query that would sort Schools ASC & sort ASC under each School the Public Transport Stops associated to them sorted like this:

Listing:

School A
Stop A
Stop Y
Stop Z
-----------------

School B
Stop C
Stop D
Stop Z

-----------------
etc

I looked and looked but can't find the solution that i suppose to be straightfoward :/
Thank you for your help !
Regards,
Paul

Re: Two level sort

Posted: Wed Nov 16, 2011 12:41 pm
by Celauran

Code: Select all

SELECT schools, stops
FROM tablename
WHERE whatever
ORDER BY schools, stops ASC
Not what you're looking for?

Re: Two level sort

Posted: Thu Nov 17, 2011 11:46 am
by PaulR
i would hope so but i get an error in query ://

Code: Select all

<?
include("connect.php");

error_reporting(E_ALL);
// ini_set('display_errors', '1');


$query="
SELECT * FROM shp_schools AS schools, shp_transport_school_m2m AS m2ml, shp_transport AS stops
WHERE schools.school_id == m2ml.school_id
ORDER BY schools.school_id, stops.transport_id ASC
"; // end of query



$result = mysql_query($query);
mysql_close();

//query error treatement 
// start IF

IF (!$result) {
ECHO "Oups ! error in Query :/ ";
} // end IF

ELSE { // start ELSE


WHILE( $rows = mysql_numrows($result)) { // start WHILE
// What to put here
echo "<p />$school_id $school_name $transport_station_name";

} // end WHILE
} // end ELSE
?>

Re: Two level sort

Posted: Thu Nov 17, 2011 11:49 am
by Celauran
Might help if you told us what the error was...

Also, do you really need every column from three tables?

Re: Two level sort

Posted: Thu Nov 17, 2011 3:24 pm
by pickle

Code: Select all

SELECT
  school.school_id,
  stops.stop_id
FROM
  sph_schools school,
  shp_transport AS stops,
  shp_transport_school_m2m as match
WHERE
  school.school_id = match.school_id AND
  stops.stop_id = match.stop_id
ORDER BY
  school.school_id ASC
  stops.stop_id ASC
That will get you the data. You'll then have to iterate through it and create an array that holds all the stops for each school.

Re: Two level sort

Posted: Fri Dec 02, 2011 12:36 pm
by PaulR
Thanks :) i think i should start with something much much simpler ://