PHP MYSQL Sort Problem

[clankk]

Not sure if this is a PHP or MYsql problem.  My website setup is PHP Version 4.4.1, MYSQL 4.1.11.  When the database results are returned and displayed they seems to ignore the sort/ order by clause.  At times the order seems to be in reverese or exactly as the results are in the database. 

I also noticed that the problem arises after an update to the MYSQL database but that doesn't explain why the order by in PHP is ignored.

 

Appreciate any help and please be specific on what you need me to do, kinda newbie to all this. The site was created by some cowboy and he's refused to fix it!

[tauchai83]

hi..could you please post your code here? the sql statement

[btherl]

Both the sql AND the php please   That sort of strange behaviour makes sense when dealing with cowboy code.  Now we just need to turn it into professional programmer code

[clankk]

I've identified the problem in the code below.  It seems to be a problem with the final SQL being written. The code below establishes what fields will be used to Sort the results..

 

case ($type=="individuals"):
$this->sort_fields = array("'Name'");
break;
case ($type=="xxx" || $type=="ggg"):
$this->sort_fields = array("'Country'", "'Company_Name'");
break;

 

And then we the last part of the SQL Statement

 

if($type==$type=="individuals"):
$str = " WHERE TYPE='".$this->type."' ORDER BY MEMBER_ID";
}
[b]#Added By Me[/b]
[b]elseif($type=="xxx" || $type=="ggg"){
$str = " WHERE TYPE='".$this->type."' ORDER BY COMPANY_NAME";[/b]}
else {
$str = " WHERE TYPE='".$this->type."' [b]ORDER BY ".join(",", $sort_fieldz);}[/b]
[b]//$str = " WHERE TYPE='".$this->type."' ORDER BY Country, Company_Name";}[/b]

 

And finally the SQL part

 

$st = "SELECT * FROM member";
    if ($this->sql_clauses!=null) {
              $st .= " ".$this->sql_clauses;
    }
$result = mysql_query($st, $this->dbh); 

 

The code in Bold is what I've added and that work well.  The problem is there are about 20 different criterias, so building the SQL string is what I need to fix.

Thanks

 

 

[grimmier]

Add either ASC or DESC after your order by statement.