Using "order by" in my search statement

[Anon]

The following code works perfectly to retrieve data from MySQL, but I can't get "order by" to work in the statement. Ideally the search results would be ordered by state and then city. Any help would be much appreciated. Thanks - Mike Acklin

$searchStmt = "SELECT * from $tableName where inactive <> 'checked' and " ;

if ($state)
$searchStmt .= "state like '%$state%' and " ;
if ($country)
$searchStmt .= "country like '%$country%' and " ;
if ($region)
$searchStmt .= "region like '$region' and " ;
if ($occ)
$searchStmt .= "occ like '$occ' and " ;
if ($occ_fl)
$searchStmt .= "occ_fl like '$occ_fl' and " ;
if ($vis)
$searchStmt .= "vis like '$vis' and " ;
if ($jim)
$searchStmt .= "jim like '$jim' and " ;
if ($alv)
$searchStmt .= "alv like '$alv' and " ;
if ($laz_b)
$searchStmt .= "laz_b like '$laz_b' and " ;
if ($laz_bm)
$searchStmt .= "laz_bm like '$laz_bm' and " ;
if ($inactive)
$searchStmt .= "inactive like '$inactive' and " ;

$stmt = substr($searchStmt, 0, strlen($searchStmt)-4) ;

[Anon]

I didn't see a city field, but it should just need this at the end

$stmt .= "ORDER by state, city";

[Anon]

Did you append: " order by state,city" at the end of your query statement?

(I am assuming that the above is but a piece of the whole statement)

BTW, if you want to search for regular expressions, use them, e.g.: " alv like '%$alv%'"

If you really need an exact match then use that, this will optimize your query performance.

I would also suggest an approach like:

if ($something)
$temp[] = " something like '%something%' ";

if ($foo)
$temp[] = " foo = 'foo'";

and at the end:

$tempquery = implode(" and ", $temp);

that will avoid the substring part of your code.

HTH

[Anon]

You might want to use this:

&nbsp; &nbsp;" ORDER BY state ASC, city ASC"

Greetz,

Vincent Driessen
Venlo, The Netherlands