MySql ORDER BY Clause

masonite85 · #1 (**permalink**) 02-19-11, 21:17

PHP Code:


			
$qry = "SELECT DISTINCT Schools1. * FROM

(SELECT *FROM Schools WHERE Western= '$westernDisicipline' OR English= '$englishDisicipline' OR Fences='$fencesDisicipline') Schools1 

INNER JOIN

(SELECT * FROM Schools WHERE NCAA= '$NCAAtype' OR IHSA='$IHSAtype' OR OTHER= '$OTHERtype') Schools2 

INNER JOIN

($stateQ) Schools3

ON

Schools1.Western = Schools2.Western and Schools1.Western = Schools3.Western and Schools2.Western = Schools3.Western

and

Schools1.English = Schools2.English and Schools1.English = Schools3.English and Schools2.English = Schools3.English

and

Schools1.Fences = Schools2.Fences and Schools1.Fences = Schools3.Fences and Schools2.Fences = Schools3.Fences

and

Schools1.IHSA = Schools2.IHSA and Schools1.IHSA = Schools3.IHSA and Schools2.IHSA = Schools3.IHSA

and 

Schools1.State = Schools2.State and Schools1.State = Schools3.State and Schools2.State = Schools3.State

and 

Schools1.NCAA = Schools2.NCAA and Schools1.NCAA = Schools3.NCAA and Schools2.NCAA = Schools3.NCAA

and 

Schools1.OTHER = Schools2.OTHER and Schools1.OTHER = Schools3.OTHER and Schools2.OTHER = Schools3.OTHER";

I have this query which works correctly however I would like it to ORDER BY the School Name. I try to look this up and do what the tutorials do but all i get is 0 results where there should be plenty of results.

Any ideas?

r937 · #2 (**permalink**) 02-19-11, 22:30

your query makes my head hurt...

Code:

SELECT DISTINCT Schools1. * 
  FROM ( SELECT * 
           FROM Schools 
          WHERE Western = '$westernDisicipline' 
             OR English = '$englishDisicipline' 
             OR Fences ='$fencesDisicipline' ) Schools1 
INNER 
  JOIN ( SELECT * 
           FROM Schools 
          WHERE NCAA = '$NCAAtype' 
             OR IHSA ='$IHSAtype' 
             OR OTHER = '$OTHERtype' ) Schools2 
INNER 
  JOIN ($stateQ) Schools3
    ON Schools1.Western = Schools2.Western
   and Schools1.Western = Schools3.Western
   and Schools2.Western = Schools3.Western
   and Schools1.English = Schools2.English
   and Schools1.English = Schools3.English
   and Schools2.English = Schools3.English
   and Schools1.Fences = Schools2.Fences
   and Schools1.Fences = Schools3.Fences
   and Schools2.Fences = Schools3.Fences
   and Schools1.IHSA = Schools2.IHSA
   and Schools1.IHSA = Schools3.IHSA
   and Schools2.IHSA = Schools3.IHSA
   and Schools1.State = Schools2.State
   and Schools1.State = Schools3.State
   and Schools2.State = Schools3.State
   and Schools1.NCAA = Schools2.NCAA
   and Schools1.NCAA = Schools3.NCAA
   and Schools2.NCAA = Schools3.NCAA
   and Schools1.OTHER = Schools2.OTHER
   and Schools1.OTHER = Schools3.OTHER
   and Schools2.OTHER = Schools3.OTHER

could you please explain in words exactly what the join is trying to do?

also, what's $stateQ?

okay, as to your question, does this query return good results as it is but 0 results with an ORDER BY clause? or are you saying it returns 0 results without the ORDER BY clause as well?

masonite85 · #3 (**permalink**) 02-19-11, 23:02

PHP Code:


			
if(!empty($ANYSTATE))
{
    $state = "Please Choose";
    $stateQ = "SELECT * FROM Schools WHERE State <> '$state'";
}

the stateQ handles the last piece of the query they have the choice to choose up to three states or "all states" this handles that. You can see the working form in the following link.

The College Equestrian - Search for a College

Honestly I can't explain it well, as this a very new thing to me and when I first wrote my query it seemed correctly written but didn't return results, through some suggestions on some forums I got the posted query which did work so I left it at that. It does return results without the ORDER BY but fails when I attempt to put it in. Maybe there is a better way of achieving this, It makes my head hurt too...

it-iss.com · #4 (**permalink**) 02-20-11, 04:39

Can you also provide the SQL statement that contains the ORDER BY clause?

r937 · #5 (**permalink**) 02-20-11, 05:51

Quote:

Originally Posted by masonite85

Maybe there is a better way of achieving this, It makes my head hurt too...

i think you're doing it wrong

the "cross joins" that you have are needlessly complex

based on the form that you linked to, there is a much simpler approach

you will have to rewrite your php code, though

what is involved is simply to test each of the form fields that a user can check, and optionally generate WHERE clause conditions based on those choices

for example, if the user does not check anything, your query should look like this --

Code:

SELECT * 
  FROM Schools

if the user checks off English and NCAA then the query should look like this --

Code:

SELECT * 
  FROM Schools 
 WHERE English = '$englishDisicipline' 
   AND NCAA = '$NCAAtype'

if the user checks off both Western and English along with NCAA then the query should look like this --

Code:

SELECT * 
  FROM Schools 
 WHERE ( Western = '$westernDisicipline' 
      OR English = '$englishDisicipline' )
   AND NCAA = '$NCAAtype'

see the pattern?

Internet Services

Web Development

Digital Marketing

Consumer Tech