SORT BY does not work with JOIN

[Anonymous]

I have 2 tables. One with files and one with persons related to the first through the id.
Table 1: files (idfiles, idcontacts(FK), etc.)
Table: 2 contacts (idcontacts, etc.)

I create a join in the following query:

$query = "SELECT files.subject, files.number, files.date, files.prescription, files.ordered, files.notes, files.filename, files.guarantee, contacts.name AS contact_name FROM contacts INNER JOIN files ON (contacts.idcontacts=files.idcontacts)";

In the table I get I have a link of the top of each column which makes sorting in ASC/DESC order each time I click on it.

The problem is the following:
if the query contain the join then sorting does not work.
On the other hand, if query is like the following, it does work.

$query = "SELECT * FROM files $order_by_str $sort_order_str $limit_str";

The rest of the code is as following

$default_sort_order = 'ASC';
$default_order_by = 'idfiles';

................


   $result = mysql_query($query);
   if(!$result) error_message(sql_error());
?>
<DIV ALIGN="CENTER">
  <table width='100%' border='5' cellspacing='1' cellpadding='7' bordercolor='#A7B3BE' class='results'>
    <TR>
      <TH WIDTH="5%" NOWRAP> <A HREF="<?php echo "$PHP_SELF?action=list_records&sort_order=$sort_order&order_by=idfiles"; ?>"> No </A> </TH>
      <TH WIDTH="5%" NOWRAP> <A HREF="<?php echo "$PHP_SELF?action=list_records&sort_order=$sort_order&order_by=number"; ?>"> SerialNumber </A> </TH>
      <TH WIDTH="30%" NOWRAP> <A HREF="<?php echo "$PHP_SELF?action=list_records&sort_order=$sort_order&order_by=subject"; ?>"> Subject </A> </TH>
      <TH WIDTH="20%" NOWRAP> <A HREF="<?php echo "$PHP_SELF?action=list_records&sort_order=$sort_order&order_by=contact_name"; ?>"> Customer </A> </TH>
      <TH WIDTH="5%" NOWRAP> <A HREF="<?php echo "$PHP_SELF?action=list_records&sort_order=$sort_order&order_by=date"; ?>"> Date </A> </TH>
      <TH WIDTH="15%" NOWRAP> Description </TH>
      <TH WIDTH="15%" NOWRAP> Notes </TH>
    </TR>
    <?php
   while($query_data = mysql_fetch_array($result)) {
      $idfiles = $query_data["idfiles"];
      $number = $query_data["number"];
      $subject = $query_data["subject"];
      $contact_name = $query_data["contact_name"];
      $date = $query_data["date"];
      $prescription = $query_data["prescription"];
      $notes = $query_data["notes"];
      echo "<TR>\n";
      echo "<TD WIDTH=\"5%\" ALIGN=\"CENTER\"><a href='./digi_archives/".$idfiles.".jpg"."' target=\"_new\">"."ΑΡΧΕΙΟ"."</a></TD>\n";
      echo "<TD WIDTH=\"5%\" ALIGN=\"CENTER\">$number</TD>\n";
      echo "<TD WIDTH=\"30%\" ALIGN=\"CENTER\">$subject</TD>\n";
      echo "<TD WIDTH=\"20%\" ALIGN=\"CENTER\">$contact_name</TD>\n";
      echo "<TD WIDTH=\"5%\" ALIGN=\"CENTER\">$date</TD>\n";
      echo "<TD WIDTH=\"15%\" ALIGN=\"CENTER\">$prescription</TD>\n";
      echo "<TD WIDTH=\"15%\" ALIGN=\"CENTER\">$notes</TD>\n";
      echo "<TD WIDTH=\"5%\" ALIGN=\"CENTER\"><a href=\"./list_files_2.htm"."?idfiles=".$idfiles."\">".'επεξεργ.'."</a></TD>\n";
      echo "</TR>\n";
   }
?>
  </TABLE>
</DIV>

Maybe, someone more experienced is able to find out where the problem lies.
Thanks.

 

[Alexej Kubarev]

it should be ORDER BY and yes, it works as i am using it pretty often..
What is the query you are trying to run? the FULL query.. and what error are you getting?

 

[Anonymous]

It's OK, I had forgotten the

$order_by_str $sort_order_str $limit_str

thus the full query would be

$query = "SELECT files.subject, files.number, files.date, files.prescription, files.ordered, files.notes, files.filename, files.guarantee, contacts.name AS contact_name FROM contacts INNER JOIN files ON (contacts.idcontacts=files.idcontacts) [b]$order_by_str $sort_order_str $limit_str[/b]";

and it works so.

 

[Alexej Kubarev]

okej.. good..