Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2011
    Posts
    2

    Unanswered: MySql ORDER BY Clause

    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?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2011
    Posts
    2
    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...

  4. #4
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Can you also provide the SQL statement that contains the ORDER BY clause?
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by masonite85 View Post
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •