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
Two level sort
Moderator: General Moderators
Re: Two level sort
Code: Select all
SELECT schools, stops
FROM tablename
WHERE whatever
ORDER BY schools, stops ASCRe: Two level sort
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
Might help if you told us what the error was...
Also, do you really need every column from three tables?
Also, do you really need every column from three tables?
Re: Two level sort
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 ASCReal programmers don't comment their code. If it was hard to write, it should be hard to understand.
Re: Two level sort
Thanks
i think i should start with something much much simpler ://