Two level sort

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
PaulR
Forum Newbie
Posts: 3
Joined: Wed Nov 16, 2011 11:42 am

Two level sort

Post 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
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Two level sort

Post by Celauran »

Code: Select all

SELECT schools, stops
FROM tablename
WHERE whatever
ORDER BY schools, stops ASC
Not what you're looking for?
PaulR
Forum Newbie
Posts: 3
Joined: Wed Nov 16, 2011 11:42 am

Re: Two level sort

Post 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
?>
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Two level sort

Post by Celauran »

Might help if you told us what the error was...

Also, do you really need every column from three tables?
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: Two level sort

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
PaulR
Forum Newbie
Posts: 3
Joined: Wed Nov 16, 2011 11:42 am

Re: Two level sort

Post by PaulR »

Thanks :) i think i should start with something much much simpler ://
Post Reply