SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Guru
    Join Date
    Oct 2004
    Location
    uk
    Posts
    853
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Where to put ORDER BY

    Hi guys this is my search script

    I would have thought that ORDER BY would go after the WHERE statement but it didnt work

    Any help on where it should go many thanks


    PHP Code:
    $query "SELECT *, date_format(startdate,'%d/%m/%Y') startdate FROM jobs WHERE 1=1 ";

    if (!empty(
    $category)) {
      
    $query .= "AND category='$category' ";
    }

    if (!empty(
    $location)) {
      
    $query .= "AND location='$location' ";


    if (!empty(
    $salarytype)) {
      
    $query .= "AND salarytype='$salarytype' ";
    }

    if (!empty(
    $salary)) {
      
    $query .= "AND salary >= $salary ";
    }

    if (!empty(
    $jobstatus)) {
      
    $query .= "AND jobstatus='$jobstatus' ";


  2. #2
    SitePoint Guru Ize's Avatar
    Join Date
    Nov 2005
    Location
    The Netherlands
    Posts
    809
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It does

    The order is this:

    SELECT [] FROM [] WHERE [] ORDER BY [] LIMIT []

    Remember that the WHERE clause has to be entirely finished before you add ORDER BY (so, in other words, after all your AND pieces are added).

  3. #3
    SitePoint Guru
    Join Date
    Oct 2004
    Location
    uk
    Posts
    853
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    like this?

    $query = "SELECT *, date_format(startdate,'%d/%m/%Y') startdate FROM jobs WHERE 1=1 ORDER BY 'applications' ";

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,018
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    something like that, yes, if you fix the typo in OREDER

    also, presumably you will want to sort by a column, not by a string
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru
    Join Date
    Oct 2004
    Location
    uk
    Posts
    853
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    $query "SELECT *, date_format(startdate,'%d/%m/%Y') startdate FROM jobs WHERE 1=1 ORDER BY applications "
    When I use that it returns every result in the database

    applications is a column name

  6. #6
    SitePoint Guru Ize's Avatar
    Join Date
    Nov 2005
    Location
    The Netherlands
    Posts
    809
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Of course it does
    Your conditional is "WHERE 1=1". Well, 1 is always 1, no?

    You forgot to add "AND ......" after that.

  7. #7
    SitePoint Guru
    Join Date
    Oct 2004
    Location
    uk
    Posts
    853
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    $query = "SELECT *, date_format(startdate,'%d/%m/%Y') startdate FROM jobs WHERE 1=1 AND ORDER BY applications ";
    like that?

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,018
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    no, not like that, like this --
    Code:
    SELECT *
         , date_format(startdate,'%d/%m/%Y') startdate 
      FROM jobs 
     WHERE 1=1 
       AND category = 'foo'
       AND location = 'hollywood'
       AND salarytype = 'exhorbitant'
       AND salary >= 937000
       AND jobstatus = 'fired'
    ORDER BY applications
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    $query "SELECT *, date_format(startdate,'%d/%m/%Y') AS startdate FROM jobs WHERE 1=1 ";

    if (!empty(
    $category)) {
      
    $query .= "AND category='$category' ";
    }

    if (!empty(
    $location)) {
      
    $query .= "AND location='$location' ";


    if (!empty(
    $salarytype)) {
      
    $query .= "AND salarytype='$salarytype' ";
    }

    if (!empty(
    $salary)) {
      
    $query .= "AND salary >= $salary ";
    }

    if (!empty(
    $jobstatus)) {
      
    $query .= "AND jobstatus='$jobstatus' ";


    //Addition Here
    $query .= "ORDER BY applications "


Bookmarks

Posting Permissions

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