Where to put ORDER BY

[wazo]

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

$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' ";
} 

[Ize]

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).

[wazo]

like this?

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

[r937]

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

[wazo]

$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

[Ize]

Of course it does
Your conditional is "WHERE 1=1". Well, 1 is always 1, no?

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

[wazo]

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

like that?

[r937]

no, not like that, like this --

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

[Dan Grossman]

$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 ";